Postgresql – create schema with optional relations

postgresqlschema

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 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.