Search code examples
sqlschemarelational

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.

Example:

level 1000 has 4 ports and three are filled.
level 4000 has 12 ports and none are available.

level table
-----------
level id
port_id1
port_id2
port_id3
port_id(n)


pod table
---------
pod_id
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

Solution

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