multivalued attributes in databases

Question

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 -

1. Trying something like putting comma separated values in the field, which appears a bit clumsy.
2. 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:

1. Are there any more ways of handling this?
2. Which of the above two methods is generally used?

Thanks in advance

Answer 1

Is the relationship one-to-many or many-to-many? With the one-to-many relationship, I recommend a foreign key in the child table (the many) referencing the parent table (the one). With a many-to-many relationship, then your best bet will most probably be a separate table with foreign keys to both parent and child.

Answer 2

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.

Answer 3

Read here http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html about the First (1NF), Second (2NF) and Third (3NF) normal forms of database design. There are more forms above 3NF, but usually 3NF is sufficient.

Answer 4

Has anybody heard about multi-valued fields in relational database theory? I know a few databases that allow such things (access 2007, filemaker) but I am convinced these "multi-valued" fields are just artefacts hiding tables with a one to many relationship.

Answer 5

If you are limited to working with a strictly relational database, then you need to store those values as rows in a table. And that was your question - how to do it with a relational database. However, there are many databases available that provide native storage of multiple values in a field which turns out to be a very good match for much real world data, easy to program with, and simpler to comprehend (without the explosion of tables you get with 3rd normal form). For more info, see http://en.wikipedia.org/wiki/MultiValue http://en.wikipedia.org/wiki/IBM%5FU2 http://en.wikipedia.org/wiki/InterSystems