Relationships between table levels and foreign keys

Question

I need help with the following structure in my database.

These are the tables

PriceList
OptionalGroup
ProductGroup
Product

Now I need to structure this things up, lets begin from the last table article.

The article belongs to a product, then the product belongs to a productgroup, then the productgroup belongs to an OptionalGroup, and the OptionalGroup belongs to a PriceList.

And today the relationship looks like this.

Table: PriceList
Table: OptionalGroup has ForeignKey to PriceListId
Table: ProductGroup has ForeignKey to OptionalGroup
Table: Product has ForeignKey to ProductGroup

Sample data:

>  Pricelist: 81
   >  OptionalGroup: Shoes
       > ProductGroup: Shoelace
       >   Product: Shoelace 1
       >   Product: Shoelace 2

How should I design this?

In this way, to get a product I need to now pricelist, optionalgroup, productgroup then I can get the product. Because pricelist has their own OptionalGroup and it could also have the same optionalgroup name.

@wp91 ..how should i design this? is the question ;) just read my text. — Nils Anders, Dec 20 '12 at 15:40

score 1 · Accepted Answer · edited May 23 '17 at 11:48

How should I design this?

You already did ;) Your data model seems appropriate to what you described as your requirements.

There are some possible variations without radically changing the basic idea though. For example...

enter image description here

...uses identifying relationships to "migrate" the parent PKs to children, so you can (for example) get the PriceListId directly from the Product table, without JOINing with ProductGroup and OptionalGroup tables, but at the price of "fatter" child FKs.

Whether the design with non-identifying relationships is better than the design shown here is a matter of the engineering tradeoff. Both have pros and cons and it is your task to pick one with more pros and less cons for your particular case.

Also, in case there are no (other fields) in the PriceList table, and you can never have an empty price list (or simply don't make a distinction between empty and non-existent price list), you can omit that table entirely.

score 0 · Answer 2 · answered Dec 20 '12 at 12:10

0

This design will do,

table name PriceList
ProductID--> PK
Price
table name OptionalGroup
GroupID--> PK
ProductID--> FK
GroupName
table name ProductGroup
ProductGroupID--> PK
ProductGroupName
GroupID--> FK
table name Product
ProductName
ProductGroupID-->FK

answered Dec 20 '12 at 12:10

Mariappan Subramanian

9,527
8
32
33

Relationships between table levels and foreign keys

2 Answers2