have multiple tables with the same key that are separated by type of data or keep things together in the single table [closed]

Closed. This question needs details or clarity. It is not currently accepting answers. 开发者_开发知识库

---

Want to improve this question? Add details and clarify the problem by editing this post.

Closed 2 months ago.

Improve this question

I have broken up a very large database table into smaller, manageable, normalized tables.

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

These tables hold the 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 holds data about the packaging.

Maybe 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 maybe I am right to have separated them and maybe should have taken it a step further and separated the construction into its own table as well.

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 using the same key?

---

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.

---

The answer is it depends.

It depends on what you typically query, how you typically query, how often you query, how large the table is to hold all the data.... etc. It depends.
An example of when you might not want to normalize would be if you need to query aggregated or derived data regularly and the process of compiling it takes a "long time." Usually though I think data should be normalized.

That being said, I'm not sure what you described is "Normalization" as much as it's separation. Normalization would involve removing duplicated data in different columns.

Let's take your example of packaging... It seems to me like you've made a record in some ProductBasePackaging which is related by PartNumber to the ProductBase or something.

In reality, if you were normalizing the data... you'd have a ProductBasePackaging row only for each type of packaging... like maybe you ship 1000 different products but only use 10 different types of boxes. ProductBasePackaging would have 10 rows, each having info on a unique box... then ProductBase would reference its required box by PackagingID