Return to Question

Imagine I have several tables in SQL Server 2016:

1. Cat
2. Cat carrier
3. Cat transit box
4. 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:

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 cannot be in different carriers. Cat cannot be in different cat boxes. Cat cannot be in different cat cars. Cat carrier cannot be in different cat boxes. Cat carrier cannot be in different cat cars. Cat box cannot be in different cat cars. (null is considered different to any specific value, so cat cannot be in cat carrier 3 and not be in any cat carrier at the same time. Two null values are ok).

Two important questions:

1. Is this flat structure adequate for what I want to do? Is there any better structure for this task?
2. How to create unique constraints to make sure an entity does not travel in multiple different containers.

Imagine I have several tables in SQL Server 2016:

1. Cat
2. Cat carrier
3. Cat transit box
4. 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:

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 cannot be in different carriers.
• Cat cannot be in different cat boxes.
• Cat cannot be in different cat cars.
• Cat carrier cannot be in different cat boxes.
• Cat carrier cannot be in different cat cars.
• Cat box cannot be in different cat cars.
• (null is considered different to any specific value, so cat cannot be in cat carrier 3 and not be in any cat carrier at the same time. Two null values are ok).

Two important questions:

1. Is this flat structure adequate for what I want to do? Is there any better structure for this task?
2. How to create unique constraints to make sure an entity does not travel in multiple different containers.

Imagine I have several tables in SQL Server 2016:

1. Cat
2. Cat carrier
3. Cat transit box
4. 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:

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 carrier cannot be in two boxes at once. Cat cannot be in a carrier and cat box separately.

Two important questions:

1. Is this flat structure adequate for what I want to do? Is there any better structure for this task?
2. How to create unique constraints to make sure an entity does not travel in multiple different containers.

Imagine I have several tables in SQL Server 2016:

1. Cat
2. Cat carrier
3. Cat transit box
4. 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:

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 cannot be in different carriers. Cat cannot be in different cat boxes. Cat cannot be in different cat cars. Cat carrier cannot be in different cat boxes. Cat carrier cannot be in different cat cars. Cat box cannot be in different cat cars. (null is considered different to any specific value, so cat cannot be in cat carrier 3 and not be in any cat carrier at the same time. Two null values are ok).

Two important questions:

1. Is this flat structure adequate for what I want to do? Is there any better structure for this task?
2. How to create unique constraints to make sure an entity does not travel in multiple different containers.

Imagine I have several tables in SQL Server 2016:

1. Cat
2. Cat carrier
3. Cat transit box
4. 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:

1. Is this flat structure adequate for what I want to do? Is there any better structure for this task?
2. How to create unique constraints to make sure an entity does not travel in multiple different containers.

Imagine I have several tables in SQL Server 2016:

1. Cat
2. Cat carrier
3. Cat transit box
4. 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:

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 carrier cannot be in two boxes at once. Cat cannot be in a carrier and cat box separately.

Two important questions:

1. Is this flat structure adequate for what I want to do? Is there any better structure for this task?
2. How to create unique constraints to make sure an entity does not travel in multiple different containers.