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!

Answer 1

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