Yes, my first idea was just as you have described and proposed. However, I
got strong critics from "more experienced database programmer" about my
design not being usable in pratice for heavy join usage. (I do not rely on
his opinion to the fullest as you can see).
Here is the database I've designed according to requirements:
- Products with two types but collectible as one type
- Discounted and Retail products that both join up as Products to be able to
add them to Order collection.
- Orders that have multiple Products
- BonusItems that are added to each of the Products as bonus
- ProductTypes that define a product
- Localizable ProductTypes and BonusItems to several Cultures
This database is actually a small demo of a much larger and more complex
real database. It's made so it assembles all kind of relations needed.
Here is what I came up with:
What design they proposed as an alternative is a kind of hierarchy design
which would look like:
so those entries with NULL would be original entries usable for referencing
and others for translations...
I didn't like the idea to be honest...