How to Structure a Table where *some* Columns can have Multiple Values? [duplicate]

This question already has answers here: Closed 11 years ago.

开发者_运维问答

Possible Duplicate:

How to Store Multiple Options selected by User in a Table

I am very confused by this.

I want my users to be able to restrict who may contact them. So I need to create a new table of RESTRICTIONS for each user and their selected criteria, which will have a column for each user's restriction criteria. Those columns will include age, income, looking for, drugs, etc. Some of those columns (looking for, drugs, etc.) might contain multiple choices, and therein lies my problem. How do I structure the table in this way, considering some criteria (columns) can have multiple values while others do not?

I've been told about join tables and enum values (both of which I've never worked with before), but I am really confused as to how I would structure a table in which some columns (not all), can contain multiple choices.

How do I store those multiple choices in those specific columns of the table and how do I structure the overall table of RESTRICTIONS?

---

A DB column (at least theorethically) should NOT hold multiple values. Unfortunately, there are some programmers that store multiple values in a single column (values separated by comma for examples) - those programmers (in most cases) destroy the concept of DB and SQL.

I suggest you to read about Database Normalization to get a start in organizing your tables. And, do your best to achieve the Codd's Third Normal Form

---

This is the simplest way. Multiple attributes become rows in a second table.

CREATE TABLE restrictions (
  user_id INTEGER PRIMARY KEY, -- references users (user_id), not shown
  age_restriction VARCHAR(10) NULL,
  income_restriction VARCHAR(20) NULL
);

CREATE TABLE looking_for (
  user_id INTEGER NOT NULL REFERENCES restrictions (user_id),
  looking_for VARCHAR(35) NOT NULL, -- could also be a foreign key.
  PRIMARY KEY (user_id, looking_for)
);

INSERT INTO restrictions (user_id) VALUES (1);
INSERT INTO restrictions (user_id, age_restriction) VALUES (2, '> 25');

INSERT INTO looking_for VALUES (1, 'boat');
INSERT INTO looking_for VALUES (1, 'dunky fazoo');

If you wanted to accept multiple restrictions on age, such as '> 25' and '< 55', you could build another table for that, too.

To retrieve all the restrictions, use an OUTER JOIN.

SELECT r.user_id, r.age_restriction, r.income_restriction, lf.looking_for
FROM restrictions r
LEFT JOIN looking_for lf ON lf.user_id = r.user_id

---

You probably need more than one table.

You have a "users" table already, right? If one of your "restrictions" criteria can have just one value per user, then that column belongs in the "users" table. So you might have columns "min_age" and "max_age" in the users table, because presumably each user has only one, contiguous range of ages they are looking for.

On the other hand, for each restriction criterion that can have multiple values, you need a new table. So you might have a table "users_restrictions_drugs" in which the primary key is (user, drug). But you might also have a table "users_restrictions_lookingfor" in which the primary key is (user, lookingfor). Whatever "looking for" is.

For some of these tables it may make sense either

• to define the second column (the one that isn't "user") as an enum
• or (better) to have an additional table that sets out the possible values of that second column and is referenced by a foreign key.

---

table restrictions
user_id smoker_ok min_height max_height min_age max_age
-------------------------------------------------------
1       Y               150       200        24     34
2       N               100       180        32     57

table drug_restrictions
user_id     drug_id   drug_allowed
----------------------------------
1                H               N
1                M               Y
2                E               Y

Would be an example. In the restrictions table, you can store explicit, singular values - smokers yes or no, or min and max requirements. For each table where there are multiple choices, you can create a join table - I've given an example for drugs. In the drug_restrictions table, user 1 says she doesn't want people using H, but does want people using M.

This solution allows you to use the "drug_id" as a foreign key to whatever table in your database populates the "drugs" field on the user interface. It allows you to use regular, standard SQL conventions for those foreign keys, and to enforce them at the database level by declaring them as foreign keys.

The drawback is, of course, that you have to query lots of tables to find matching records, and that's not much fun.

So, you could also follow Catcall's recommendation - this dramatically reduces the number of tables, but makes it impossible to use "standard" foreign key integrity constraints. This might be okay - it's certainly going to be faster.

I'd be reluctant to use enums - they tend to lead to complex queries, and are not "standard" SQL.

---

There's no problem to have tables where some columns have duplicate values. Consider a tbale with users; there's no problem if two users have the same birthday?

The only problem is a table where a primary key occurs more than once. For instance, a user table may very well have username as its primary key, and you wouldn't want two users with the same username.

So, make one table that lists users, one that lists restrictions, and one that joins the two. The latter will have one entry for every combination of user/permission.