One-to-Many relationship in MySQL – how to build model?

I have got two tables:

1) Area
2) Map

Each Area shall have at least 1 Map, but can also have more than one Map.

One Map can only belong to one Area.

How to build this in MySQL?

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

create table Area(id int primary key auto_increment, name varchar(100));

create table Map(id int primary key auto_increment, 
                 area_id int not null,
                 name varchar(100),
                 foreign key (area_id) references area(id));

SqlFiddle

Each Map MUST have an Area, as area_id is not null (and is a Foreign key on Area)

But you won’t be able (and it’s not desired) to have “at least one map” for each area.

One day, you’ll have to create an Area. And it won’t have any Map at this time.
Or make “regular” checks to see the Areas without any Map.

You may want to delete an Area, if it has no more related Map, when you delete a Map.

Method 2

Add a Foreign key in Map that references the Area’s Primary Key. That will enforce a one-to-many relationship between Maps and Areas.

As for enforcing a minimum of one map per area (if that is necessary) there are some ideas in this post here. One of the simpler solutions would be to create a view that only displays areas which have maps:

CREATE VIEW viewAreas AS
SELECT * 
FROM Areas, Maps
WHERE Areas.ID = Maps.AreaID;

This way, you can create an area, and then add maps to it. You can also enforce the Foreign Key in maps to be NOT NULL, so a map must always have an area.

Method 3

A table each for Map and Area, with a foreign key on Map linking to Area.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating