create schema with optional relations 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-&gt;Tier2-&gt;Tier3-&gt;Tier4 Where Tier1 has many Tier2 and so on. But on some cases there are some tiers that get skipped. Like Tier1-&gt;Tier3-&gt;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.
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 -&gt; 2 -&gt; 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.