Relationship design

[spnz]

Hi there

I have found this forum so helpful in the past I thought I would try my luck with getting a few more ideas for my next project.

I am designing a database used to track a persons working history and timesheet information for each of the jobs the person works in.

I work for a company that employees a heap of temp staff that often change jobs.
I would like to have a db that I can enter a person in once and be able to track all their details using a form with subforms for each of the relevant tables.

The basic design so far is I have 3 tables.
tblPersonalInformation tblBookingInformation tblTimesheets

tblPersonalInformation is as the name of table suggests contains all the persons personal details.

tblBookingInformation contains all the booking information for current past and future jobs.

tblTimesheets will contain the weekly timesheets.


I way I look at the design is that a person can have many bookings.
Simple create a join between tblPersonalInformation & tblBookingInformation.
A booking can have many timesheets again create a join between tblBookingInformation & tblTimesheets.

What I suspect to be a problem is that with using this design

1. I will be unable to add a booking without a person been associated with that record. This will cause a problem as often I will have booking that have yet been filled with a person.

2. A person will have many different timesheets from different bookings.

My questions are how can I design the tables so I can have bookings that are not associated with a person.
How do I create a join so I am able to pull all of a persons timesheet information or will my current idea of a join work to be able to show all the required information


Thank you for taking the time to help out.

 

[spnz]

Thanks Pat

Ok just so I understand you.

I create an extra tbl I will call it tblSuppliers

my 4 tables will look similar to this

tblPersonalInformation
PersonalInformationID
Forename
Surname
etc


tblBookingInformation
BookingInformationID
PersonalInformationID
SuppliersID
etc

tblSuppliers
SuppliersID
TimesheetID
SupplierName
etc

tblTimesheet
TimesheetID
TSDate
etc

Now I create the following joins
1.tblPersonalInformation & tblBookingInformation.
2.tblBookingInformation & tblSuppliers
3.tblSuppliers & tblTimesheet

Is this correct?

 

[spnz]

Thanks Pat

I am pretty dense so I still need a little help.

1.with tblSuppliers I add BookingID as a foreign key
2.I with tblTimesheet I add BookingID as a foreign key

So now do I make they following joins

1.tblPersonalInformation & tblBookingInformation
2.tblBookingINformation & tblSuppliers
3.tblBookingInformation & tblTimesheets.

Thank you for taking the time to help out.

Shane

 

[spnz]

Sorry...

I edit tblBookingInformation and add in SupplierIDputting the timesheetID in tblTimesheet would restrict the supplier to only a single timesheet. I removed it.

Ok so I use your 2 tables

tblSuppliers
SuppliersID
SupplierName
etc

tblTimesheet
TimesheetID
BookingInformationID
TSDate
etc

I keep tblPersonalInformation the same.

With tblBookingInformation I add a foreign key for Suppliers (SuppliersID)

Then create the joins?

 

[spnz]

Sorry Pat. Been looking at it for to long!!!!

Thanks again for been so tolerant.

Shane