0

In this example I have a table called "Zones"

create table stk_zones (
    ZoneId int not null auto_increment,
    Primary Key (ZoneId),
    ZoneName varchar(255),
    Height int,
    Width int,
    Description varchar(255)
);

Inside this zones table I add a few records:

insert into stk_zones (ZoneName, Height, Width, Description)
values ('Zone A', 100, 200, 'First Zone!'), ('Zone B', 50, 50, 'Second Zone!'), ('Zone C', 50, 100, 'Third Zone!');

What would be the best way to show that Zone B is nested inside of Zone A? They share the same properties so I want to keep them in the same table.

Would the best option be a secondary table, something like:

create table stk_zone_relations (
    ZoneRelationId int not null auto_increment,
    Primary Key (ZoneRelationId),
    ParentZoneId int,
    Foreign Key (ParentZoneId) references stk_zones (ZoneId),
    ChildZoneId int,
    Foreign Key (ChildZoneId) references stk_zones (ZoneId)
);

Not sure proper principals in this sort of scenario.

1 Answer 1

1

If a zone can have only one parent, then you have a many:one relationship. That is implemented with just ParentId linking to ZibeId.

If you have zones that belong to multiple other zones (overlapping?), then it sounds more like "many:many". That requires an extra table with 2 columns - each being a ZoneId. See Many-to-many for index advice.

6
  • In this case the Zone can have a parent, or no parent. So I think the Many-To-Many would be the case. I think the part I am thrown off is with the Many-To-Many table having the relationship just be coming from the single table.
    – Travis
    Commented Jul 31, 2023 at 18:45
  • @Travis "the Zone can have a parent, or no parent. So I think the Many-To-Many would be the case" - No this would be Zero-Or-One-To-Many which can be implicitly simplified by saying One-To-Many. You can accomplish your goal with a nullable foreign key ParentZoneId field in your Zones table, like Rick mentions.
    – J.D.
    Commented Jul 31, 2023 at 18:54
  • Then in case the zone can be in multiple parents, would require a second table for a Many-To-Many? @J.D.
    – Travis
    Commented Jul 31, 2023 at 18:58
  • 1
    Yup, just trying to ask questions about other scenarios. Thank you.
    – Travis
    Commented Jul 31, 2023 at 19:03
  • 1
    1:many with lack of a parent can be represented by NULL (and still involve only 1 table). That is, I agree with J.D.
    – Rick James
    Commented Jul 31, 2023 at 19:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.