I am designing a database and, while I have used simple databases before, I am by no means an expert on relational dB design and so am seeking advice and help.
The database functionality I need is as follows:
A third normal database in the structure represented by the image in the link below. So, a user may have access to one or more airports, and at each airport he may have access to one, two or all three of the available services. In the process of coming up with a design, here is where I have gotten to:
I have a one-to-many relationship between the tables country, areacode, postacode, city and users as each of these tables can have many users. I have a many-to-many relationship between users and airports as each user can be a member of many airports and each airport can have many members.
However, what is confusing me is how to implement the relationship of the services table. While a user can be a member of more than one airport, for each airport he/she is a member of, he/she can also access up to three services, but not necessarily all three, either 1, 2 or 3. But the choice of which services they have should only be made after the airport relationship is defined. Then, and only then can we choose a service. I originally thought that it should be a one-to-many relationship between the services table and the airports table. But now I am thinking that it might be a many-to-many relationship between service and airport tables as each airport can have up to three services and each service can be offered at more than one airport. But, the confusing thing here is that this relationship needs to depend on the user and what access they have. Or, as which service(s) at which airport(s) need to be tied to the usr_id key as well, perhaps the relationship needs to include the user table as well?
I am also a bit unclear of how the inserts work with this type of database but I guess I should cross that bridge when I get to it.
Owww ... my brain hurts. Hopefully this is not too confusing and someone has some insight for me here.