CODEWITHSUNDEEP
mysqldatabasedatabase-design
mllamazares
mllamazares

Reputation: 8206

Should I create another table?

I have a table with this structure:

enter image description here

col1 would be "product_name" and col2 "product_name_abbreviated".

Ignoring the id colum I've this data:

1 1 43
1 1 5
1 1 6
1 1 7
1 1 8
2 2 9
2 2 10
2 2 34
2 2 37
2 2 38
2 2 39
2 2 50

I can do another table and put there col1 and col2 columns becouse they are repeated. Something like this:

enter image description here

But I'm sure that it'll not be repeated more than 15 times, so... Is it worth?

Thanks in advanced.

Upvotes: 0

Views: 357

Answers (5)

naomi
naomi

Reputation: 1984

This is a very basic question in database design and the answer is a resounding "Two Tables"! Here are just some of the reasons:

  1. If you have one table, then by mistake someone could enter a new row with product name "1" and abbreviated product name "2" The only way to stop this would be to add rules and constraints - far more complicated than just splitting the tables in the first place.

  2. Looking at the database schema should tell you meaningfully about what it represents. If it's a FACT that you can't have a product with product name "1" and abbreviated product name "2" then this should be clear from looking at the table structure. A single table tells you the opposite, which is UNTRUE. A database should tell the truth - otherwise it is misleading.

  3. If anyone other than yourself looks at or develops against this database, they may be confused and misled by this deviation from such basic rules of design. Or worse, it could lead to broken window syndrome, if they assume it was not carefully designed and therefore don't take care with their own work.

The principle is called "Normalisation" and is at the heart of what it means for something to be a relational database rather than just some data in a pile :)

Upvotes: 0

Fergara
Fergara

Reputation: 947

Normalization is good for avoiding repeating data. Your model is tiny, the data is small, you should not worry and leave one entity (table).

In real projects sometimes we normalize and then realize we got a mess. It's always good to balance between repeating data and easy of understanding the model and querying. Not to mention when working with data warehouse databases...

Upvotes: 0

Paranoid Android
Paranoid Android

Reputation: 5037

You are sure NOW, but what about when you will extend your application in one year time? Split the tables

Upvotes: 2

user359040
user359040

Reputation:

Yes, you should split them out into separate tables - this is an example of normalisation to Second Normal Form.

Upvotes: 4

Lupuss
Lupuss

Reputation: 649

Use only one table with the ID, two VARCHAR columns for the name and abbreviation and a NUMBER for the price.

Upvotes: 0

Related Questions