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

Question

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?

It's called multi-value fields in the book Database Design for Mere Mortal. You need to eliminate it by extracting tables out from them. You might want to read it if you haven't. — zawhtut
– zawhtut, Commented Feb 3, 2011 at 6:15

dustinl4m3 · Accepted Answer · 2011-02-03 06:04:12Z

4

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.

answered Feb 3, 2011 at 6:04

dustinl4m3

1592 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ibrahim Azhar Armar Over a year ago

i agree, and thanks for your suggestion i am surely going on with what you suggested.

rxmnnxfpvg · Accepted Answer · 2011-02-03 06:03:28Z

1

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.

edited Feb 3, 2011 at 6:03

rxmnnxfpvg

31.2k50 gold badges129 silver badges190 bronze badges

answered Feb 3, 2011 at 6:01

James

8,6361 gold badge28 silver badges27 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related