Menu
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!
484
Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency.
Following are the various types of Normal forms: A relation is in 1NF if it contains an atomic value. 2NF. A relation will be in 2NF if it is in 1NF and all non-key attributes are fully functional dependent on the primary key. 3NF. A relation will be in 3NF if it is in 2NF and no transition dependency exists.
The database normalization process is further categorized into the following types: First Normal Form (1 NF) Second Normal Form (2 NF) Third Normal Form (3 NF)
Normalization is the process to eliminate data redundancy and enhance data integrity in the table. Normalization also helps to organize the data in the database. It is a multi-step process that sets the data into tabular form and removes the duplicated data from the relational tables.
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.
192
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
