1

I'm looking for suggestions/best practices for implementation of a location management DB.

Locations have a primary access point and potentially infinite secondary access points. They must have one and only one primary access point. These access points may be referenced differently by other locations. (ie a secondary access point for one location may be the primary of another). In the interests of DRY, I would like to have one table of "Locations" and another table of "AccessPoints". I'm struggling with intermediate tables/rules to enforce this behaviour.

closed as too broad by McNets, LowlyDBA, mustaccio, SqlWorldWide, Erik Darling Jul 26 '17 at 16:11

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

0

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 loc_ap_map).

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 loc_ap_map:

  • on update or insert to location, if there's no loc_ap_map row with the same location_id and access_point_id as the current row's location_id and primary_access_point_id, add sucha row to loc_ap_map
  • on delete from loc_ap_map, if any of the relationships match the location_id and primary_access_point_id of a location row, refuse the delete.
  • Yep, thats what I came up with. Thanks. I was trying to be smarter and remove the primary id from the location table but I was just overthinking the solution. Thanks for your help. – Chris Schulz Jul 26 '17 at 23:06
0

Sounds pretty straightforward, so perhaps there is something I don't understand?

CREATE TABLE LOCATIONS
( location_key ... primary key not null
, location attribute_1 ... not null
, ...
, location attribute_n ... not null );

CREATE TABLE ACCESS_POINTS
( access_point_key ... not null
, location_key ... not null
, access_point attribute_1 ... not null
, ...
, access_point attribute_m ... not null
,    constraint ... primary key (access_point_key, location_key)
,    constraint ... foreign key (location_key)
                    references LOCATIONS (location_key)
);

A assume here that several locations can share the same access_point.

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