Imagine I have several tables in SQL Server 2016:
- Cat
- Cat carrier
- Cat transit box
- Cat car
Every object can be stored in higher level, but not necessarily directly one above with N-to-1. So a cat can be put into a carrier, the carrier into a transit box and the box put into a car. But a cat can simply ride in a car. Objects can store numerous types, so for example: Cat transit box can hold simultaneously cats and cat carriers.
There is no need for Cat or Cat Car in all this.
Now, the idea is to create a table joining everything at once:
| Cat | Cat Carrier | Cat Box | Cat Car |
|---|---|---|---|
| null | 3 | 5 | null |
| null | 4 | 5 | null |
| 1 | 5 | 5 | null |
| 2 | null | 5 | null |
So this is a situation where three carriers are put into a single box. one of those carriers contain a single cat. Also, there is a cat somewhere in the box.
But I have to make sure that there are no illogical data:
| Cat | Cat Carrier | Cat Box | Cat Car |
|---|---|---|---|
| null | 3 | 5 | null |
| null | 3 | 6 | null |
| 1 | 3 | null | null |
| 1 | null | 8 | null |
Cat carrier cannot be in two boxes at once. Cat cannot be in a carrier and cat box separately.
Two important questions:
- Is this flat structure adequate for what I want to do? Is there any better structure for this task?
- How to create unique constraints to make sure an entity does not travel in multiple different containers.
