Skip to main content
Added ERD
Source Link
Robbie Dee
  • 9.8k
  • 2
  • 25
  • 54

I would suggest you retain the master table as you've described and have a table for each adjustment type. In between, have a link table with a FK to the master table and a column for the adjustment type and a FK to the primary key on each adjustment table. Naturally, you'd also want a metadata table to store adjustment types and their descriptions.

ERD

enter image description here

You might ask why you couldn't just have a FK to Master from the child tables. A valid point, but this design affords you the opportunity to apply the same change to multiple Master records without introducing redundancy.

I've seen some implementations similar to the scenario you've described where the type is omitted on the link table for the sake of brevity and a series of adjustment table PKs are earmarked for the type. I'd strongly advise you not to do this as some adjustment types may grow far beyond what you'd anticipate.

I would suggest you retain the master table as you've described and have a table for each adjustment type. In between, have a link table with a FK to the master table and a column for the adjustment type and a FK to the primary key on each adjustment table. Naturally, you'd also want a metadata table to store adjustment types and their descriptions.

I've seen some implementations similar to the scenario you've described where the type is omitted on the link table for the sake of brevity and a series of adjustment table PKs are earmarked for the type. I'd strongly advise you not to do this as some adjustment types may grow far beyond what you'd anticipate.

I would suggest you retain the master table as you've described and have a table for each adjustment type. In between, have a link table with a FK to the master table and a column for the adjustment type and a FK to the primary key on each adjustment table. Naturally, you'd also want a metadata table to store adjustment types and their descriptions.

ERD

enter image description here

You might ask why you couldn't just have a FK to Master from the child tables. A valid point, but this design affords you the opportunity to apply the same change to multiple Master records without introducing redundancy.

I've seen some implementations similar to the scenario you've described where the type is omitted on the link table for the sake of brevity and a series of adjustment table PKs are earmarked for the type. I'd strongly advise you not to do this as some adjustment types may grow far beyond what you'd anticipate.

Source Link
Robbie Dee
  • 9.8k
  • 2
  • 25
  • 54

I would suggest you retain the master table as you've described and have a table for each adjustment type. In between, have a link table with a FK to the master table and a column for the adjustment type and a FK to the primary key on each adjustment table. Naturally, you'd also want a metadata table to store adjustment types and their descriptions.

I've seen some implementations similar to the scenario you've described where the type is omitted on the link table for the sake of brevity and a series of adjustment table PKs are earmarked for the type. I'd strongly advise you not to do this as some adjustment types may grow far beyond what you'd anticipate.