mysql多值属性表_数据库中的多值属性

How should a relational database be designed to handle multi-valued attributes ?

edit: To elaborate:

There are two ways I could think of for doing this -

Trying something like putting comma separated values in the field, which appears a bit clumsy.

Create another table for the field and let the multiple values go to the field. This might lead to very large number of tables, if I have too many fields of this kind.

The question is:

Are there any more ways of handling this?

Which of the above two methods is generally used?

Thanks in advance

解决方案

In conventional relational database design, each row & column must store only one value.

Don't store comma-separated lists or anything wacky like that.

For example, say a sports team has seven members. You could do this:

CREATE TABLE team (

team_id INT PRIMARY KEY,

team_name VARCHAR(50),

team_members VARCHAR(200)

);

INSERT INTO team VALUES ('Dwarfs', 'Sleepy,Dopey,Sneezy,Happy,Grumpy,Doc,Bashful')

But it's better to do this:

CREATE TABLE team (

team_id INT PRIMARY KEY,

team_name VARCHAR(50),

);

INSERT INTO team (team_name) VALUES ('Dwarfs');

CREATE TABLE team_members (

team_id INT,

member_name VARCHAR(20),

FOREIGN KEY (team_id) REFERENCES team(team_id)

);

INSERT INTO team_members VALUES

(LAST_INSERT_ID(), 'Sleepy'),

(LAST_INSERT_ID(), 'Dopey'),

(LAST_INSERT_ID(), 'Sneezy'),

(LAST_INSERT_ID(), 'Happy'),

(LAST_INSERT_ID(), 'Grumpy'),

(LAST_INSERT_ID(), 'Doc'),

(LAST_INSERT_ID(), 'Bashful');

nb: LAST_INSERT_ID() is a MySQL function. Similar solutions are available in other brands of database.