Some Table/Relationship Advice please? (1 Viewer)

[Franky G]

Hi,

I'm starting from scratch with an events database but need some advice on tables/relationships.
The main purpose of the database is to calculate/record the hours spent and fees generated by our staff members who attend the events.

Each EVENT may be spread over a few SITES (eg New years eve events, Large Pop festivals) and a few days.

Each SITE may have more than one EMPLOYEE in attendance, with each EMPLOYEE having different REASON for Attendance (Safety Cert., Public Entertainment License, Safety at Sports Ground Cert) Each EMPLOYEE will also have more than one REASON for attendance.

Additionaly, each employee may attend more than one site and/or event on the same day!

Hours worked information;
main consideration here are Hours worked; Start Time and Finish Time recorded for each EVENT, grouped by EMPLOYEE, possibly grouped by attendance REASON.

Need to Calculate fees based on Hours * Rate, but including taking into account Sunday working and other non-standard rates.

TblEvent
EveID*
EveName
EveDescript
EveStart
EveEnd

TblSites
SiteID*
SiteName

TblEmployee
EmpID*
EmpName

TblReason
Reason*

TblHours
EveID*
HrsDate
HrsStart
HrsEnd
HrsRate

I would appreciate any guidance on table structure and relationships, as I haven't done this before. I've usually had existing tables to work with.

Thanks!
Frank

 

[RichMorrison]

Looks mostly OK.

"tblHours" should probably contain all the primary keys of the 4 other tables; Employee, Event, Site, and Reason.

tblHours should probably NOT include any "Rate" data. Every employee with the same Event/Site/Reason would have the same rate, so don't store that same thing multiple times.

Don't know where Rate belongs; does it vary by Event, Site, and/or Reason ? You have to decide that one.

HTH,
RichM