Multivalued Attributes in Databases

Question

How to create a relational database to handle multi-valued attributes?

to change . To clarify:

There are two ways I could come up with for this:

• Trying to do something like putting values ​​separated by commas in a field that seems a bit awkward.
• Create another table for the field and let a few values ​​go into the field. This can lead to a very large number of tables if I have too many fields of this type.

The question arises:

• Are there any other ways to handle this?
• Which of these two methods is commonly used?

Thanks in advance

Answer 1

In the traditional relational database structure, each row and column should store only one value.

Do not keep lists separated by commas or something ridiculous.

For example, they say that there are seven people in a sports team. You can do it:

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 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 the database.

Answer 2

Is the one-to-many or many-to-many relationship? In the one-to-many relationship, I recommend the foreign key in the child table (many), referring to the parent table (one). If there are many-to-many relationships for many, the best option would be, most likely, a separate table with foreign keys for both the parent and the child.

Answer 3

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

Answer 4

If you are limited to working with a strictly relational database, you need to save these values ​​as rows in a table. And that was your question - how to do this with a relational database. However, there are many databases that provide their own storage of several values ​​in a field, which turns out to be very good for many real data, easy to program and easier to understand (without exploding the tables you get with the 3rd normal form). For more information see http://en.wikipedia.org/wiki/MultiValue http://en.wikipedia.org/wiki/IBM_U2 http://en.wikipedia.org/wiki/InterSystems