Database Design: Defining Access Control

Question

An application I am developing needs to provide access to data based on a list of cities defined for each client. A client can have:

• access to all cities in a country OR
• access to all cities in a state / region OR
• access to select cities in any state or country.

What would be the best way to define this in the database (if the db has a Country table, State / Region table, City table and a Client table)?

Clarification: (A simplified view of the tables with only the essential columns pertaining to this question).

Country table -

idCountry | Name

State table -

idState | idCountry | Name

City table -

idCity | idState | Name

Client table -

idClient | Name

Answer 1

You could to create a Location self related table (Id, Name, ParentLocation) and a AccessControl table (ClientId, LocationId). When a client is related to a location, you could grant access to all location below it. Some examples:

ID Name      Parent
-------------------
 1 World     NULL   -- Need to represent all countries
 2 Brazil    1      -- A country
 3 São Paulo 2      -- A state
 4 São Paulo 3      -- A city

If you want to stick your current model, maybe a table like (ClientId, CountryId nullable, StateId nullable, CityId nullable). This way you could define your security access as your definition, but would need to deal with nullable fields.