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 -

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

You have to be much more explicit than this. I suppose English may not be your first language, but give it a shot. People will try to help and some will fix your question but you have to give us something to go on. — , Nov 13 '08 at 19:24
Does this answer your question? [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — philipxy, Jun 14 '20 at 06:40

score 21 · Answer 1 · edited Nov 26 '17 at 20:38

21

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.

edited Nov 26 '17 at 20:38

Kekis2014

145
2
16

answered Nov 13 '08 at 18:47

Bill Karwin

538,548
86
673
828

Is LAST_INSERT_ID tied to a table? or is that a unique number across the database? – Nov 13 '08 at 19:26
In MySQL, auto-increment values are tied to a table, there's no sequence object like there is in Oracle or PostgreSQL. But LAST_INSERT_ID() returns the most recent value generated by an INSERT, regardless of the table you used, which makes it useful for populating child tables. – Bill Karwin Nov 13 '08 at 19:53
2

Should also be noted that the LAST_INSERT_ID() and mysql_insert_id() in the API are per connection. i.e. the value is from the last insert on that connection, not on the entire database. – Fire Lancer Nov 28 '10 at 14:56
@Fire Lancer: Thanks, that's a good point and correct. LAST_INSERT_ID() wouldn't be much value if it returned the last value generated for any concurrent connection. You'd have race conditions, and be no better off than if you used the "SELECT MAX(id) FROM ..." technique. – Bill Karwin Nov 28 '10 at 16:14

score 1 · Answer 2 · answered Sep 09 '09 at 19:21

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

score 1 · Answer 3 · answered Nov 13 '08 at 18:43

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.

score 1 · Answer 4 · answered Nov 13 '08 at 18:48

1

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.

answered Nov 13 '08 at 18:48

Robert Walker

368
1
2
8

Tariq Imtinan · Answer 5 · 2020-04-23T22:45:16.577

There are two possible approaches for this -

Relationship : Storing multiple values for one attribute of an entity is done through referenced key-foreign key relationships. For more info, check this.
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.

multivalued attributes in databases

5 Answers5

Linked