MySQL Database column having multiple values

Question

I had a question about whether or not my implementation idea is easy to work with/write queries for.

I currently have a database with multiple columns. Most of the columns are the same thing (items, but split into item 1, item 2, item 3 etc).

So I have currently in my database ID, Name, Item 1, Item 2 ..... Item 10.

I want to condense this into ID, Name, Item.

But what I want item to have is to store multiple values as different rows. I.e.

ID = One  Name = Hello   Item = This
                              That
                              There

Kind of like the format it looks like. Is this a good idea and how exactly would I go about doing this? I will be using no numbers in the database and all of the information will be static and will never change.

Can I do this using 1 database table (and would it be easy to match items of one ID to another ID), or would I need to create 2 tables and link them?

If so how exactly would I create 2 tables and make them relational?

Any ideas on how to implement this? Thanks!

Solution 1

I don't think one table really makes sense in this case. Instead you can do:

Main Table:
ID
Name

Item Table:
ID
Item #
Item Value
Main_ID = Main Table.ID

Then when you do queries you can do a simple join

Solution 2

This is a classical type of denormalized data base. Denormalization sometimes makes certain operations more efficient, but more often leads to inefficiencies. (For example, if one of your write queries was to change the name associated with an id, you would have to change many rows instead of a single one.) Denormalization should only be done for specific reasons after a fully normalized data base has been designed. In your example, a normalized data base design would be:

table_1: ID (key), Name
table_2: ID (foreign key mapped to table_1.ID), Item

Solution 3

You're talking about a denormalized table, which SQL databases have a difficult time dealing with. Your Item field is said to have a many-to-one relationship to the other fields. The correct things to do is to make two tables. The typical example is an album and songs. Songs have a many-to-one relationship to albums, so you could structure your ables like this:

Table Album
album_id [Primary Key]
Title
Artist

Table Song
song_id [Primary Key]
album_id [Foreign Key album.album_id]
Title

Often this example is given with a third table Artist, and you could substitute the Artist field for an artist_id field which is a Foreign Key to an Artist table's artist_id.

Of course, in reality songs, albums, and artists are more complex. One song can be on multiple albums, multiple artists can be on one album, there are multiple versions of the same song, and there are even some songs which have no album release at all.

Example:

Album
album_id Title       Artist
1        White       Beatles
2        Black       Metallica

Song
song_id  album_id  Title
1        2         Enter Sandman
2        1         Back in the USSR
3        2         Sad but True
4        2         Nothing Else Matters
5        1         Helter Skelter

To query this you just do a JOIN: SELECT * FROM Album INNER JOIN Song ON Album.album_id = Song.album_id

Comments

[boisvert]

The answers below are very clear - what you have is the classic case about why databases store data in multiple tables, cross referencing rows by number (aka "keys").