If one location can have multiple access points, and one access point can be used by multiple locations, you have a many-to-many relationship; this is usually maintained by a table that links the two objects together.
However, you also have a requirement that each location has one (and only one) primary access point. You state that one location's primary access point can be another location's secondary access point - you do not state whether two locations can share a primary access point.
I envision a structure like this:
CREATE TABLE location
( location_id PRIMARY KEY NOT NULL
,primary_access_point_id NOT NULL -- foreign key to access_point table
<additional location attributes>
[Optional - unique constraint on primary_access_point_id]
CREATE TABLE access_point
( access_point_id PRIMARY KEY NOT NULL
<additional access_point attributes
CREATE TABLE loc_ap_map
( loc_ap_id PRIMARY KEY NOT NULL
,location_id NOT NULL -- foreign key to location
,access_point_id NOT NULL -- foreign key to access_point
<additional attributes for this specific combo of location and access_point, if any>
So, each location has a primary access point associated with it (in the
location table itself), and an indeterminate number of secondary access points (connected via
You cannot have more than one primary access point for each location. The primary access point can be tied to other locations. If you set up the unique constraint on
location.primary_access_point_id, then a given access point can only be the primary access point for one location.
Note: while it's technically redundant, I would be inclined to include the link between the primary access point and the location in the
loc_ap_map table. In this case, the presence of the
primary_access_point_id is basically a flag to identify which access point is the primary. this makes various simple queries (list all access points for each location, count the number of access points per location) simple.
You could use triggers to ensure that such a record exists in
- on update or insert to
location, if there's no
loc_ap_map row with the same
access_point_id as the current row's
primary_access_point_id, add sucha row to
- on delete from
loc_ap_map, if any of the relationships match the
primary_access_point_id of a
location row, refuse the delete.