Relationships & Structure

[steve.mullen]

Hi,

I'm trying to put together a database for a small recruitment agency who wish to track customer orders. A typical order from a client would be from a theatre, for 20 Stewards & 4 Security Guards, but each each line item (i.e. 20 stewards) they require the facility to associate candidates from a separate candidate pool table. They need to be able to view order history and a list of people who have worked under each line item, but also to be able to report for each candidate to view a history of each shift they have undertaken, like a work history.

the Tables is have are as follows;

tblClients
ClientID
Client Name
Add1
Add2
City
Postcode
Tel
Contact Person

TblOrder
OrderID
ClientID
Site/Booking NameSite Address

tbl_OrderLineItems
OrderLineID
OrderID


Qty
Product (Lookup - tbl_Products i.e stewards, Guards, Marshalls etc)
Date
Start Time
Finish Time
Hours
Charge Rate

tblProducts
Product Name

tbl_Shifts
ShiftID
OrderLineID
Staff Name (Lookup tbl_candidates)

tbl_Candidates
CandidateID
Forename
Surname
DOB
Gender
Mob No.
Tel. No.
email
Add1
Add2
City
Postcode
Qualifications (Lookup)

tbl_Qualifications
Qualification Name


i'm struggling to work out the relationships between tbl_Shifts & tbl_candidates to enable me to report a history of shifts undertaken by each candidate. i've set the tbl_shifts to lookup the candidates table which will populate the field with a candidate, but then i cannot go view that shift under the candidate records.

Any help with the structure would be of great help

Thanks

[jzwp11]

Before I get to your shift issue, I have the following recommendations:

1. It is best not to use spaces or special characters (i.e periods, commas, #, ?, parentheses etc.) in your table or field names
2. It is best not to use lookup fields in your tables. This site details the issues it can cause. Lookups are best left for forms.
3. It is best not to use reserved words as field or table names. This site has a list of those reserved words.

Looking at your tables, the client table and the candidate table have similar fields, I would think that you can combine the data into 1 table and include a field to distinguish the type.

tblEntity
-pkEntityID primary key, autonumber
-fkEntityTypeID foreign key to tblEntityTypes
-txtPrimaryName (surname for a person, company name for business)
-txtSecondaryName (first name for a person)
-txtAdd1
-txtAdd2
-txtCity
-txtPostcode
-DOB (only applies to person)
-Gender (only applies to a person)


Now since a person can be related to another person or company (i.e. the contact person(s) at a company), we need a table to take care of that

tblEntityRelate
-pkEntityRelateID primary key, autonumber
-fkPEntityID foreign key to tblEntity (represents the primary for example the client)
-fkSEntityID foreign key to tblEntity (represents the secondary for example the contact person at the client)

Now a person or company can have many contact methods: a phone number, a fax number, a mobile number and e-mail address, so another one-to-many relationship

tblEntityContactInfo
-pkEntityContactID primary key, autonumber
-fkEntityID foreign key to tblEntity
-fkContactTypeID foreign key to a table that holds all possible contact types phone, fax, mobile,e-mail etc.)
-txtContactInfo (a field to hold the actual number or e-mail address)

tblContactTypes
-pkContactTypeID primary key, autonumber
-txtContactType


Since a candidate may have many qualifications, the qualifications should not go in the same table as the basic information about the person since it describes a one-to-many relationship. What are the qualifications? Don't they relate to the product you are providing as well i.e. steward, guard etc.? Sounds like you have another relationship there that needs to be handled. So we need to capture the qualifications related to a product. I will assume that a particular qualification may relate to many products

tblProducts
-pkProductID primary key, autonumber
-txtProduct
-currChargeRate

tblQualifications
-pkQualID primary key,autonumber
-txtQualification


Now relate the qualifications to a product

tblProductQualifications
-pkProdQualID primary key, autonumber
-fkProductID foreign key to tblProducts
-fkQualID foreign key to tblQualifications

Now we have to relate the applicable products/qualifications to each of the candidates, so that we can properly match the candidates to the products you are selling

tblEntityProductQualifications
-pkEntityProdQualID primary key, autonumber
-fkEntityID foreign key to tblEntity
-fkProdQualID foreign key to tblProductQualifications

Now in terms of shifts, a candidate might have experience working on many shifts, so that describes a one-to-many relationship. Further, a shift may apply to many candidates, so another one-to-many relationship

First a table to define the shifts

tblShifts
-pkShiftID primary key, autonumber
-txtShift

Now relate the applicable shifts to the candidate

tblEntityShifts
-pkEntityShiftID primary key, autonumber
-fkEntityID foreign key to tblEntity
-fkShiftID foreign key to tblShifts


Finishing up the order

tblOrders
-pkOrderID
-dteOrder (order date)

Since an order is related to the client (a record in tblEntity) as well as a site name and address (also a record in tblEntity), you have a one order to many entities relationship

tblOrderEntity
-pkOrderEntityID primary key, autonumber
-fkOrderID foreign key to tblOrders
-fkEntityID foreign key to tblEntity


tblOrderLineItems
-pkOrderLineID
-fkOrderID
-Qty
-fkProductID foreign key to tblProducts
-dteStart (include both date and time)
-dteEnd (include both date and time)
-currChargeRate (will need to copy from tblProducts at time of order)

The hours field is unnecessary since Access can calculate the elapsed time from the given start date/time and the end date/time.

Technically speaking if an order line item has 2 date/time fields it would suggest a one-to-many relationship, but I think this might be one time where we might not fully normalize the structure. However, if a product relative to an order can have multiple start and end date/times then I would suggest to follow through with the normalization.

[steve.mullen]

Thanks for this,

The importance of separating the clients and candidates is that there are further requirements for the candidate area which hasn't been identified in detail as yet, such things as tracking punctuality, attendance and performance, to vetting and employment pipeline and attached files relative to the recruitment process.

I think based on what you've said, i've underestimated the complexity of the design and might need to seek help.

thanks for your advice.

[jzwp11]

You're welcome. Feel free to post back with any questions.