1

I want to create a database for a specific (ecommerce) product and I'm not quite sure what the best way is to store all my specifications. I know exactly which specifications I want and how to store those (one big table since it won't likely change).

For example lets say I sell movies my table would look something like this:

  • Database ID
  • Product Title
  • Type
  • Region
  • Packaging

etc.

Where Type is DVD, Bluray, 3D Bluray etc.

My question is how do I store this Type? I could save this as plain-text / enum (method 1) or alternatively in a new table with all the different types and store the ID of DVD (method 2).

The new table is I think the better way to do it but it creates a lot of tables and messy code because I would also need to do this for Region and Packaging. Also it's not very likely that my data will change and that I need to add more Types and Regions.

When do I create a new table (method2)? Only when its 10+ data types or when it frequently changes or something?

3
  • Also, it was really hard for me to describe the different "methods". Are there better names for this so I can Google this and read up on the subject? Dec 30 '14 at 15:43
  • You're on the right track! Using separate tables is the way to go based on normalizing and relational models. Dec 30 '14 at 15:52
  • you can create trigger before insert for the type column on table X Dec 30 '14 at 17:22
0

In what's called Normalization, you want to prevent storing duplicate data. For example, it would be best to store the 'DVD' text only once in the database, not for each row that has a type of 'DVD'. To achieve this, you would create a lookup table (method 2).

It will seem like extra code, but it's better in the long run.

Also, if you wanted to get a list of all types, you can query that new table instead of the large table. You could query the larger table, but it will be slower and may not have all the types.

0

You're correct that a new table is the best way to store it. Referencing Type from the ID of the value in a secondary table will allow you to normalize your data. This helps ensure integrity, reduce duplicate data, and allow for effective querying of any type.

You're also correct that this results in the creation of several tables - welcome to the world of relational schemas :) It may seem like a pain now, but long term this method will be the best for storing, updating, deleting, and querying your data.

In any of these scenarios, you should always create a separate table for each entity, whether they have 3 data types or 10 data types that may or may not change. Requirements may change down the road and you should plan for the unexpected! You can tell what fields should be branched out into separate tables by seeing if it follows the rule for third normal form,

Each column must depend on directly on the primary key.

Since Type does not follow this, it is best as a separate table.

Further Reading: Litt's Tips on Normalization

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.