Database Normalization

Question

I have recently broken up a very large database table into smaller, manageable tables and for the most part I am satisfied with my work and I feel that the data is properly normalized.

But there is one exception to this. The tables in question are from a products database that stores information about (you guessed it) products that the company sells. I have separated much of the information into two tables: ProductBase and ProductBasePackaging.

These tables hold the umbrella of information that is relevant to a base part number rather than an individual product (there are multiple products to each base number).

ProductBase contains rather general information such as MarketingCopy, Keywords etc. and also information on construction i.e. material, components etc.

And ProductBasePackaging of course holds data about the packaging.

Now that I am writing the application for data manipulation, I am beginning to second guess myself. It seems like I have just made it harder on myself now that I have to keep track of multiple tables that use the same key (the base part number). Or am I right to have separated them as such and maybe taken it a step further and separated the construction into it's own table as well?

I am pretty well versed in using sql but this is the first time I have ever had to actually design a database structure, let alone restructure a large existing database. So basically what I am asking is should I have multiple tables with the same key that are separated by type of data or keep things together in the single table where I can reference everything I need from one table using the same key?

Sorry I know that was a lot to read, I hope it made sense, and thanks to all those who make it through!

Answer 1

Normalization might look like a pain in the a** right now - but trust me, in the long run, you'll be glad you did it! Non-normalized "flat" tables with everything but the kitchen sink in them will become very unmanageable over time, data inconsistencies will creep in, and before you know it, you have a huge steaming pile of crap - errrg - data that doesn't make any sense anymore!

Yes, joining tables can be a bit of work - but especially for displaying data, you should definitely check out views which can help you write those JOINs once and then just use them as "virtual tables" that hold everything again.

Database normalization - up to roughly 3NF - is a good thing (TM) for sure! I would always recommend doing it, and then maybe at that point introduce back some limited de-normalization where performance needs might require it - but only in a very controlled way, and with your full understanding and knowledge that you are in fact denormalizing something again.