Timothy Griffith Timothy Griffith - 3 months ago 12
SQL Question

way to design a SQL schema where entities have fixed number of relationships but Number of Relationships depend on attributes

In our schema we have a level entity and a pod entity. The pods are stored within the levels on ports. Ports are an attribute of levels. There are a varying amount of ports per level. We can Identify the number of ports based on a SKU for the level. How do we design a schema to account for a varying amount of ports per level in order to check that there is a port available for a new pod or not and to identify the ports for the largest levels vs the smallest.


level 1000 has 4 ports and three are filled.

level 4000 has 12 ports and none are available.

level table
level id

pod table
other attributes

Designing it in the form above would lead to nullable columns and we are not sure if that is the best practice given our setup.

Relational Schema:
Relational Schema


If I understand your schema and intent correctly, this is how I would approach it. (Sorry I don't have pretty ERD software at work.)

Simply, you are creating a normalized table to store the Level-to-Port relationships so that it can handle zero-to-many. You will likely want to add constraints to ensure uniqueness, etc; but this should be the general idea:

enter image description here

Based on your most recent clarifying comment, you could avoid using a Port table and replace the Port_Id field on Levels_Port with a INT field that stores your 5-12 value.