relationship and autonumber prob (1 Viewer)

[Lojaine]

Hi guys...

I started a new job and i hv been asked to develop a small system for a travel agency..

the CASE is .. i hv such theory
Supplier has 1/alot of reservations and each reservation would have 1/alot of hotels

when i relate the reservation to supplier it works perfectly .. but when i relate the hotels to the reservations things get missed up .. when i tried to put another autonumber in the reservation it was rejected coz it was already in the there .. how can i set a counter or function like a= a+1 for each cel that is entered... I dunno what is needed . i just need to link those three together

and something else i need to generate a number(refrence number for the supplier) and share it with all the tables
i wiould appritiate any help offered

 

[The_Doc_Man]

The issue is relationships and autonumbers, for sure.

An autonumber in table A can be a PRIME KEY (PK) for table A but can only be a FOREIGN KEY (FK) in any other table. Because an autonumber in table B has NO RELATIONSHIP WHATSOEVER with table A. The autonumber in your reservation table has no relationship to the numbering in your hotels. If you make the relationship go in the wrong direction you will try to relate things that are not related (at least, not that way).

You need to study the concept of relationships and normalization. You have a classic many-to-one relationship between reservations and hotels. The number of the hotel (PK of the hotel table) can be a FK for the reservation table. The PK of the reservation probably has nothing to do with anything except to keep records unique.

 

[Lojaine]

thanks alot for ur reply
that is true , no clear relationship is there between them, but whats needed to be done is , being able to enter as many hotels in one reservation . for ex. a person would go on a tour trip around Spain so he would reserve Sheraton, in Barchelona/ and Radisson Sas in Madrid/ , the simplist way is to list them alllll in the same reservation table , I MEAN hotel1,hotel2,hotel3...hotel10. but the problem is each hotel has alot of detials like city, rate, no.of rooms, no of nights .. etcc... SO it needs a seperated table .. it is soo simple but i couldnt solve it and i really need it, I didnt get how u saw the relationship as MANY -to- One ? please explaine because i can c it from the reservation side it is one-to-many please correct me if im wrong
u can c the three tables as , [ a house that has a room(s) and each room has a bed (s)] Funny example but i think it would make it clear