18

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?

tellob
  • 1,220
  • 3
  • 16
  • 32

3 Answers3

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

Raphaël Althaus
  • 59,727
  • 6
  • 96
  • 122
  • So: I am not able to build a thing like: One area has to have a map? I can only ensure, that a map has an area so far? – tellob Jun 11 '13 at 13:14
  • @tellob. Yes. At least at the db level. You could manage this kind of check at the application level, rather. – Raphaël Althaus Jun 11 '13 at 13:16
  • 1
    I know where you are coming from but not sure I agree. This argument, if followed for all items tends to lead to no foreign relationship being required. I think it's better to require it and deal with the work, e.g. create the child first, then the parent and assign the child at that point. Avoiding validations because 'one day' you'll need a blank record hasn't worked that well for me in the past. – Michael Durrant Jun 11 '13 at 13:16
  • @MichaelDurrant so area_id must be null(able) in map. And you can have a Map without Area. This is just an inversion of the problem... No ? – Raphaël Althaus Jun 11 '13 at 13:18
  • The real thing i would like to have is, that when i want to create an area, i have to tell a map id. so the map has to be created before. on the other hand i want multiple maps to be assigned to one area. is there really no solution for this? i see the problem of the cross reference, so: you cannot create a map without having an area and areas are dependent on maps. so it might be the only way as described in your answer i guess?! – tellob Jun 11 '13 at 13:21
  • Nice answer. +1. Can you add a snippet on how an insert would look with this example? – Amir Afghani Nov 26 '14 at 22:25
9

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.

Community
  • 1
  • 1
Jenius
  • 174
  • 15
  • To my mind the trigger solution postet afterwards is the most suitable. Thanks for your answer! – tellob Jun 11 '13 at 13:49
2

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