Ibrahim Azhar Armar Ibrahim Azhar Armar - 2 months ago 4
MySQL Question

What is the ideal solution for storing multiple ID's in a database table?

My real-estate application has a database table that it holds the queries(inquiries) by the user. This table holds all the information about a particular real-estate query.

The database table looks something like this:

CREATE TABLE IF NOT EXISTS `queries` (
`id` bigint(20) NOT NULL auto_increment,
`category_id` int(10) NOT NULL,
`serial` varchar(30) NOT NULL,
`minBudget` int(11) NOT NULL,
`maxBudget` int(11) NOT NULL,
`city_id` int(10) NOT NULL,
`area_id` int(10) NOT NULL,
`locality` varchar(100) NOT NULL,
`status` tinyint(1) NOT NULL,
PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


In
city_id
and
area_id
chances are there are situations where I would want to store multiple IDs; for example, 10, 12, 20, 50 and so on.

How should I be storing this in the databsae?

Should I use the
varchar
datatype and hold it as a string with a delimiter defined, then fetch the value in an array?

Answer

Do NOT use a varchar if those are IDs to another table. Use a many-to-many table mapping them together.

CREATE TABLE IF NOT EXIST `query_cities` (
 `id` bigint(20) NOT NULL auto_increment,
 `query_id` bigint(20),
 `city_id` bigint(20)
)

CREATE TABLE IF NOT EXIST `query_areas` (
 `id` bigint(20) NOT NULL auto_increment,
 `area_id` bigint(20)
)

This will be much cleaner than stuffing things into a varchar - for instance, it allows you to say:

SELECT c.city_name, c.state, c.whatever FROM queries q 
JOIN cities c ON (q.city_id = c.id) WHERE q.id = ?

Edit: meh, I'm lame and didn't include foreign keys, there, but you get the point.

Comments