multivalued attributes in databases

database-design

Solution

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 (1,'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.

Problem

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

Original source

Related problems