Table help

[ictcrystal]

Hello, I really need help and im so stressed out i cant understand. Ok so im doing a project and i made a requirement spec of which has this: must store appointment ID, customer ID, forename, surname, contact number, dentist ID, forename, surname, room, appointment date and time... what this means is when a new appointment is made which asks for the details above then these details will be stored... in the appointments table.. BUT my issue is my appointments table does not have forename, surname ( for both the customer and dentist) or the contact number.... So i dont know how i will justify that those listed will be stored from the new appontment because all that is stored is appointment ID, customer ID, dentist ID, room, appointment date and time... because if i cant then my issue is why did we ask for those details in the new appointment (i needed them to identify the customer if the ID was forgotten) IM SO STUCK please if anyone understand could you help me out THank you PS, i have posted this on another forum but i am desperate for help so i have posted this here

[amrut]

You need separate tables namely
tblCustomers(CustomerID PK, forename, lastname and other fields)
tblDentist(DentistID PK, forename, lastname and other fields)
tblAppointments (AppointmentID,AppDate - appointment date,CustomerIDFK -related to CustomerID,DentistIDFK -related to DentistID)
See this site form examples of database relationsips - http://www.databaseanswers.org/data_models/

[ictcrystal]

I do have seperate tables.. my issue is that if ive said new appointments are made an stored on appointment table then how is this the case when only the customer ID is saved and not the contact number ( in the apt table)

[amrut]

No need to save customer contact number in apts table as it is already there in tblCustomers. This is how relational database works. You can use a query to pull all the related customer data based on the CustomerIDFK foreign key stored in apts table.

[ictcrystal]

so does this mean my requirement of new appointments must be stored in appointments table is invalid? because it only stores part of what was collected?

[orange]

Thanks for advising readers!

PS, i have posted this on another forum but i am desperate for help so i have posted this here.

This has been cross posted at http://www.access-programmers.co.uk/...d.php?t=276158

[amrut]

No, new appointments must be stored in appointment table only. One field pointing towards the customer is enough for an appointment as other details about that customer are already there in your customers table. In case of new customer, you have to always add him to the customer's table before you proceed for booking an appointment. Can you post a screenshot of your table relationship ?

[ictcrystal]

Oh i understand now!

Thank you for your help