CODEWITHSUNDEEP
database-design
Abhishek Yadav
Abhishek Yadav

Reputation: 5241

multivalued attributes in databases

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

Upvotes: 9

Views: 51251

Answers (5)

Tariq Imtinan
Tariq Imtinan

Reputation: 104

There are two possible approaches for this -

  1. Relationship : Storing multiple values for one attribute of an entity is done through referenced key-foreign key relationships. For more info, check this.
  2. Set : A scalar datatype in mysql that can have zero or more values, each of which must be chosen from a list of permitted values specified when the table is created. For more info, please check this and this link.

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562230

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.

Upvotes: 21

Toby
Toby

Reputation:

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_U2 http://en.wikipedia.org/wiki/InterSystems

Upvotes: 1

Robert Walker
Robert Walker

Reputation: 368

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.

Upvotes: 1

Glenn
Glenn

Reputation: 8032

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.

Upvotes: 1

Related Questions