enforce referential entegrity and relationship links

[Harpz]

I am designing a database for a hotel booking system for my A level project...

The following are my entities and attributes...

customers table - Customer ID, title, firstname, surname, address, town, postcode, county, dob, sex, telephone, mobile

bookings table - Booking ID, Customer ID, Room ID, arrival date, departure date, Time, rooms per night, adults per room, children per room, smoking preference, easy access room, accommodation, room type, room quality, bed type, budget, price, payment method, VAT, total cost, credit card type, card number, expiry date. .

staff table - staff id, title, firstname, surname, address, town, county, postcode, dob, telephone number, mobile number, sex, position and salary per year

rooms table - Room ID, hotel facility id, smoking preference, easy access room,accommodation, room type, room quality, bed type, price, VAT, total cost

hotel facilities table - hotel facility ID, room id, parking, fitness facilities, swimming, pool, restaurant, disabled facilities, pets welcome, non smoking rooms, air conditioning, short periods, guided tours, banquets, spa, groups allowed, congress/meetings, child friendly, tennis court, concierge service, heated pool.

cleaning table - cleaner id, staff id, room id, cleaning time, rooms to clean, date

I am having problems linking the fields together and making relationship and enforcing referential entegrity, the following errors appears each time I want to link one field to another...

for example if I giv try 2 link room id 4rm da bookings table 2 room id in the rooms table it says...microsoft access cant create this relationship and enforce referntial integrity...data in the table 'bookings' vilates intergrity rules. For example, there may be records relating to an employee in the related table, but no record for the employee in the primary tables. Edit the data so that record in the primary table for all related records. If u want to create the relationships without following the rules of referntial intergreity, clear the enforce refernential entergrity check box...

can someone urgently help me plz

 

[maxmangion]

it seems that you already have some records inputted in your database, and the problem is that you have some orphan records i.e. you have records for a particular person, but you do not have a record for that person. Search for your missing record, and either create a record for the employer, or else delete the orphan records (the ones that they have not a related record).

Thereafter, you can enforce referential integrity.

 

[Harpz]

That has helped, i deleted the orphan records and im able to enforce referential entegrity and now have one to many links between my tables etc
Thanks

 

[Harpz]

Im tryin 2 create a relationship between to of my tables and da folowing error message keeps appearing:

no unique index for the referenced field of the primary table

wha does this mean?

 

[maxmangion]

that means that one of the fields in your table which you're creating the relationship on, does not have an index. Go to the table design find the field you're creating the realationship on and set the Indexed property to Yes (No Duplicates).

 

[Harpz]

thanks

 

[Harpz]

I am not sure if my relationships are linked correctly...

customer id [customer table] - customer id [booking table]
room id [booking table] - room id [rooms tables]
room id [rooms tables] - room id [cleaners table]
room id [rooms tables] - hotel facility id [hotel facility table]
room id [hotel facility table] - room id [cleaners table]
staff id [cleaners table] - staff id [staff table]

does this seem okay?

 

[Milothicus]

i'm not sure, but it seems to me you have too many roomid relationships....

what is the purpose of the roomid field in the facility table? assuming facilityid is the primary key for the table, why would you want to associate each facility with a roomid? aren't there many rooms for one facility? once a record in the room table has a facility associted with it, you don't need a list of roomids in the facility table, which you can't have directly anyway.