Database Design: Defining Data Access from a List of Cities

Question

I am trying to build an API that gives data on various cities.

Currently, there is a table called APIPriv, which has the following columns:

===================================================
  idApiPriv | idClient | Daily | Hourly | DevKey
===================================================
      1     |    23    |  'Y'  |  'N'   |[MD5 Hex]

idClient is a foreign key to the client table.

Daily and Hourly are types of data - 'Y' means the client / developer has access to the data and 'N' means he / she doesn't.

DevKey is an autogenerated MD5 string, provided to the developer, which is used as the developer key.

Now, for each developer having access to the API, the database also needs to store a list of cities associated with each developer. The developer will only recive data on these cities.

How do I define this in the database? The number of cities in a list is not fixed. A developer may have access to 5 cities, another to 10, another to 7.

(There is already a master City table, which holds a list of 1500 cities and, naturally, the list of cities for each developer needs to be a subset of this data.)

Answer 1

Add another table that contains 2 columns - idClient and the id from the Cities table.

Then for each city that the developer has access to, add an entry into this table.

Answer 2

You'll need an associative table for the cities, with something to the effect of:

 idDevCityPriv | idApiPriv | idCity
====================================
      1              1         34
      2              1         42

Then, to get your list of cities, it would go something like this (assuming you have one's DevKey):

select
    c.*
from
    APIPriv a
    inner join DevCityPriv d on a.idAPIPriv = d.idAPIPriv
    inner join City c on d.idCity = c.idCity
where
    a.DevKey = [DevKeyVariable]

Cheers,
Eric

Answer 3

You will need another table with two columns one for the DevKey and one for the CityID. This will allow you to do a one to many join from the Developer to the Cities.

I would also suggest you change the Daily and Hourly to a boolean [true|false] rather than a char or maybe a bit [0|1]. These will lend themselves to logical conditions better and have a stricter set of possible values i.e. just two rather than 'y','Y','n','N','x' etc.

Answer 4

I'd suggest what's called a join table, or link table:

DevKey       CityId
101          1 
101          7
101          4

This table defines which developers have access to which cities, and thus provides the basis of the many to many relationship between developer and city, which can't be modelled directly.

You may want to hold some extra information in this table, for example role start and end date, whereby you can end date a developers access to a certain city.