Help defining relationship

[filch]

Hello all,

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:

link to image of dB layout

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.

Dave

[dickersonka]

create two tables, airportservices and userairportservices

airportservices
AIRPORT_SERVICE_ID
AIPORT_ID
SERVICE_ID

userairportservices
USER_AIRPORT_SERVICE_ID
USER_ID
AIRPORT_SERVICE_ID

this will allow airport services to hold the services at the airport and the userairportservices to hold the relationship between user and airport_service

[filch]

I will give that a try. Thanks for the response. I appreciate your time.

Dave

[filch]

create two tables, airportservices and userairportservices

airportservices AIRPORT_SERVICE_ID AIPORT_ID SERVICE_ID

userairportservices USER_AIRPORT_SERVICE_ID USER_ID AIRPORT_SERVICE_ID

this will allow airport services to hold the services at the airport and the userairportservices to hold the relationship between user and airport_service

updated dB diagram

Does this look correct? I seems to make sense to me. If it is correct, can you explain how inserts work with this type of dB. If I create a form on a web page with all the respective info in it to be selected or entered by the user, how does this info get distributed amongst the tables? Or am I missing something here?

Regards,

Dave

[dickersonka]

yep that looks good, the other way to do it would be to replace user_id in user_airport_services with user_airports_link, but there could be a constraint violation if the user was ever removed from an airport

what do you mean as far as the user inserting into the tables?

[filch]

Sorry .. one other question: The tables airport and service are concerning me a bit.

First, the user can be a member of up to 9 different airports and then, for each airport, that user can have access to up to three different services. So, how should the data be entered into each of these tables? On the old form I had, which used a single table, I had a separate checkbox for each airport and service and so, the users data included all the airports and services they were members of. However, the problem was that the user may not have had access to the same services at each airport but the way the dB was, it would say that they had the same access at all airports. This is why this re-design is happening. What I would like is that on the form, I could present a grid in which the user picks the airport and then also picks the services for that airport. Then they would select a second airport if applicable and then the services for THAT airport and so on. And hopefully, the info from the form gets distributed to the proper tables on submit.

I know this is a lot of question but any help is very much appreciated.

Dave

[dickersonka]

the airport services will have something like this

as_id a_id s_id
1 1 1
2 1 2

services
1 movies on board
2 international flights

userairportservices
uas_id user_id as_id
1 1 2

this means the airport offers two services, but the user only has access to 1 service, userid of 1 can only have a service of international flights at airportid 1, but airportid 1 offers 2 services

[filch]

Humm ... correct me if I am wrong but I am not sure if this is quite right and it is probably due to my lack of clarity in this.

All airports offer all three of the services in question. What is different is that each user may or may not have access to all the services at each airport. So, at airport one, he may have access to two of the services, airport two, access to one service, airport three, access to all three and so on.

Is that clearer?

Dave

[dickersonka]

But the choice of which services they have should only be made after the airport relationship is defined.

i thought this meant airports have different services

this table structure can be used, or you can use this one

userairportservices
user_airport_service_id
user_id
airport_id
service_id

and drop the airportservices table

[filch]

So, this is what I have.

dB diagram

Would this not make the table users_airports_link redundant?

If you have the time, with this latest change, if you could respond with the same type of illustration you provided in your reply of 3:37 PM, I would appreciate it. That helped me see this a bit clearer.

Again, thanks for all your help.

Dave

[dickersonka]

yes, you can drop users_airports_link

i don't know which 3:37 post is, but i assume its the one with the data

airports
aid airport code
1 hartsfield ATL

services
1 movies on board
2 international flights

users
uid usr_fname
1 john
2 jane

userairportservices
uas_id user_id service_id airport_id
1 1 2 1
2 2 1 1
2 2 2 1

this will show user_id 1 (john) has international flights at atl

this will show user_id 2 (jane) has international flights and movies at atl

[dickersonka]

also your city, country, and area code tables are out of sync

the way you have it, a city could have a different postal code, different country and different area code

the relationship should be
country --> areacode --> city -->postalcode

a country has area codes, which has cities, which have postal codes

[filch]

yes, you can drop users_airports_link

i don't know which 3:37 post is, but i assume its the one with the data

airports aid airport code 1 hartsfield ATL

services 1 movies on board 2 international flights

users uid usr_fname 1 john 2 jane

userairportservices uas_id user_id service_id airport_id 1 1 2 1 2 2 1 1 2 2 2 1

this will show user_id 1 (john) has international flights at atl

this will show user_id 2 (jane) has international flights and movies at atl

OK ... I understand how this works now as far as retrieving data from the database (at least conceptually if not actually), but I am a bit confused in terms of the insert. I believe I would have to insert first to the users table and then retrieve the insert id and then do sequential inserts into the rest of the tables. Is this correct? I am confused because I guess I thought that with InnoDB tables, there was some sort of ripple insert or update. But the more I read about this, I see that this is not the case. But looking at your example, it appears as though the id for the usersairportservices table is not unique, in that there are two id number 2 entries. Is that a typo or is it really not unique and if so, how the heck did that record get inserted?

Dave

[filch]

also your city, country, and area code tables are out of sync

the way you have it, a city could have a different postal code, different country and different area code

the relationship should be country --> areacode --> city -->postalcode

a country has area codes, which has cities, which have postal codes

Sorry ... didn't see this before I replied. Well .. here in Canada, a city can have different postal codes as well as areacodes. And, I am pretty sure there is a Toronto in the US as well as here in Ontario. My point is that my thinking for the current structure was that a user would only have one city but a city could belong to many users. The same would apply to postal codes, areacodes, countries. So I think I am not understanding what you are saying here. It looks like you are saying I need to create a country table which is related to the areacode table, which is then related to the city table and finally to postalcode. Or, are you saying I should combine these tables somehow. You can tell I am confused ;-)

Dave

[dickersonka]

sorry i mistyped my id from before, you were correct, it should be 1 2 3

[dickersonka]

yes sequential inserts into the rest of the tables after user, the ripple effect you are wanting, is only on updates and deletes, you can't necessarily insert and populate tables the way you are wanting without triggers, but thats a whole different story

now the location thing
what you were allowing with the way you had it before was a user to have a city that wouldn't belong to the correct country, postal, or area code and vice versa

think of it just as you stated it, a country has area codes, area codes have cities, cities have postal codes

(the area code doesn't necessarily match because its phone based, but anyways)

take for example there is a toronto us and canada

we will take area codes out of this example

country
1 us
2 canada

cities
cityid cityname countryid
1 toronto 1
2 toronto 2

postal codes
postalcodeid postalcode cityid
1 11115 1
2 11116 1
3 45428 2

that is the concept, also you i don't know about how canada is, but countries have states, states have cities, cities have postal codes, which maybe states is what you are meaning area codes

[filch]

Wow .. until you mentioned it, I did not realize that I had neglected to add a province/state table to the mix. Duh!!

I will update the diagram to see if I understand what you are saying.

Dave

[dickersonka]

Lol always helps to have a second set of eyes.

Sure just give the link for your new diagram and i'll check it out.

[filch]

OK .. I have updated the diagram . I am referring to telephone area codes actually. So again my thinking was ... an area code can belong to many users. In the diagram, I have left it related to the users table. However, I guess it could also be related to the city as a city can have many areacodes. But, I think there are also cases where small satellite towns could also be part of an area code. Maybe it is best just to put the area code data back in with the users data?

Dave

[dickersonka]

area codes are a little tricky and sort of hard to get a direct correlation with a physical location

i think it would be best to leave them separate

for example atlanta has 3 area codes, but those area codes are also used in other cities, suburbs also share those 3 area codes, multiple cities, sharing duplicate multiple area codes

plus a user can have an area code from another place if they moved

very good, aren't you happy with this diagram compared to how you started?

so what is your next step with this?