I have 4 tables that has the same columns. Tier1, Tier2, Tier3, Tier4. Their columns are id, code, and name.
The complete relationship is like this: Tier1->Tier2->Tier3->Tier4 Where Tier1 has many Tier2 and so on. But on some cases there are some tiers that get skipped. Like Tier1->Tier3->Tier4 so there is no Tier2, instead from Tier1 it got directly at Tier3.
My question is, how can I make a schema for this condition? I don't know how to start searching because I don't know the keyword for this problem.
Best Answer
Rule of thumb: fewer tables and more columns.
In your case you should probably have one
tier
table, and make thelevel
another column. Then have a constraint thatlevel
can only be between 1 and 4.If the tiers are always in order and have those values you don’t need any more data — the absence of a record with level 3 would imply tiers 1 -> 2 -> 4.
You might need two columns for keys: one ID for each individual record and a second column which has the same value across each group of tiers that belong together. It’s that column that you would refer to in a foreign key from (an)other table(s).
But without knowing more about your exact use-case I can only keep this advice generic.