An application I am developing has data on various cities.
Access to the data needs to be based on a list of cities defined for each client.
A client can have:

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

---------------

So far, I have come up with this (a simplified view of the tables with only the
essential columns pertaining to this question is presented below):

Country table:

==============
idCountry | Name
==============

State / Region table:

======================
idState | idCountry | Name
======================

City Table:

====================
idCity | idState | Name
====================

Client Table:

=============
idClient | Name
=============

Access Definition Table:

===============================
idAcd | idClient | Country | State | City
===============================

Note: Country, State and City are boolean and indicate HOW access has been
defined. For example, if a client has been defined access by country and state,
the columns would be flagged true and the application code would then check
the access control tables for country and state (see below) to determine the list
of cities to which access has been granted.

Access Country Table:

====================
idAcc | idAcd | idCountry
====================

Access State table:

==================
idAcs | idAcd | idState
==================

Access City table:

==================
idAcy | idAcd | idCity
==================

-------------

I am not sure if this is the right approach. Is their a better solution?

Why bother with the booleans? All they effectively state are "there are related rows in the respective table". You might as well dump the booleans and go straight to the tables in the first place.

pootle flump ur codings are working excelent.

Without the booleans, the app would have to scan all three tables every time. I thought this was more optimal. Is this a flawed approach in db design?

No - the app will scan nothing.

The database will return data as required. If the tables are properly indexed then there would be a nominal overhead in the event there are no relevant rows. If not properly indexed then the database would scan the entire table however you would properly index it eh?

pootle flump ur codings are working excelent.

I was not clear -
The booleans are to guide the application code on which tables to query. For example, if a client has been granted access based on Country and State, the application code can then query only Access Country and Access State tables, and ignore the Access City table.

Without the boolean flags, the application code will have to query all three tables every time. That slight overhead can be avoided.

No, you were perfectly clear.
You've replaced one overhead with another whilst also tightly coupling your application and database and introducing the possibility of update anomalies.

pootle flump ur codings are working excelent.

Pootle Flump is correct, but there seems to be a bit of a communication problem.

Making your application aware of security criteria does two bad things. First, it means that the application has to actively participate in any changes to security so that if you miss a check ANYWHERE in the application then your security system is compromised. More importantly, your application will become much more complex which will make it difficult to maintain and will also mean that you'll have to manage your code very differently.

By putting all of the security/access logic into the database, you have a single point of reference which is easier to manage and maintain than the application. You can build a single view that will show what cities a given user can access, use that view in an INNER JOIN to limit the cities returned by any other SELECT statements you need to build, and be done. A tiny fraction of the amount of work, cleaner application code, and a faster running application... You'll like it much better than trying to "roll your own" repeatedly!

-PatP

Ok. Thanks to Pat Phelan's explanation, I now understand what you were trying to convey. I'd appreciate your inputs on querying too ...

Thanks for taking the time to explain Pootle Flump's comment.
I am a developer, and new to db designing. In the last part of your comments (quoted above) you addressed querying.

Currently, this is how the app works -

1. Users inputs a client id and city.
2. The application checks the db for information on the city and retains the following data: idCountry, idState, idCity, Country Name, State Name, City Name.

3. The application then verifies the client id in the database and retrieves the boolean flags in the Access Definition table (using a SELECT and JOIN query on Client and Access Definition table).

4. Then, based on the flags it checks the Access Country, Access State and Access City table with the previously retained data (see 2) with a SELECT query on each (if required).

5. If the client has access to the city data, the application extracts that from the db and outputs it to the user (along with the Country, State and City name from 1).

Based on the suggestions here, I do plan to alter and drop the boolean flags. What would then be the optimal way of querying the db?

(P.S: The db server used is MySQL 5 with innodb engine and I've read that its performance with Views isn't that great. So it might be better to ignore them altogether.)