Should I separate redundant data in my database?
I have a database app that stores prices for things in different places. Each price has the following data associated with it:
- price
- date
- product ID
- country
- price type (factory/wholesale/retail)
The last three items (pID, country, pricetype) can be thought of as one composite item describing the purpose of the price; there is a lot of redundancy in this data. So I'm thinking: separate those out into their own table to save space and simplify queries.
Normal:
Prices (price_id, price, date, product_id, country_id, pricetype_id)
vs:
Prices (price_id, price, date, descriptor_id)
Descriptors (desc开发者_开发知识库riptor_id, product_id, country_id, pricetype_id)
Is this worth the added programming effort required? Will it be more or less extensible/maintainable in the long run?
Is this worth the added programming effort required?
Yes
Will it be more or less extensible/maintainable in the long run?
More extensible and easier to maintain.
In general
You should always normalize to at least 3NF.
See this article: http://databases.about.com/od/specificproducts/a/normalization.htm
It depends on the amount of data you are expecting in that table. If you have no performance/storage problems, you don't need separate tables (for performance reasons).
On the other hand, you will get all disadvantages that come with redundancy. You have to check your data for inconsistencies etc.
But: Regardless of the design you choose, there's still time to change the road you're on.
精彩评论