CODEWITHSUNDEEP
mysqldatabasedatabase-design
Ibrahim Azhar Armar
Ibrahim Azhar Armar

Reputation: 25745

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?

Upvotes: 1

Views: 2383

Answers (2)

dustinl4m3
dustinl4m3

Reputation: 159

You can do that, but it's not really the preferred solution. This is the classic tradeoff in database normalization.

The "more correct" approach is to have tables "cities" and "areas", and tables "queries_cities" and "queries_areas" that are many-to-many to relate them.

Else- what happens if a city or area id changes? Rather than change a single record in one place, you'll get to write a script to go through and update all the query records manually.

Upvotes: 4

James
James

Reputation: 8586

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.

Upvotes: 1

Related Questions