0

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.

2
  • 1
    Could you please give an example of what each of those Tiers actually are?...i.e. a little more context please? It'll actually affect the answer that's most suitable to what you're trying to accomplish.
    – J.D.
    Mar 1, 2021 at 3:12
  • It's a government hiearchy, like district->village->neighborhood Mar 1, 2021 at 5:09

1 Answer 1

0

Rule of thumb: fewer tables and more columns.

In your case you should probably have one tier table, and make the level another column. Then have a constraint that level 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.

1
  • Thank you. This is not an exact solution, but I've found a way to solve this problem. Mar 1, 2021 at 5:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.