relational Database design

[Jai]

I am currently working on designing a database to capture data such as the
labour costing, activites carried out and the machinery used for the
particular shift.

I have the following table and not sure where to go from here.

* denotes primary key

tbl employee
*EmpID as PK
EmpName
EmpNumber
Classification

tblTeamDetails
*EmpId as PK
*TeamName as PK

tblTeam
*TeamName as PK
TeamLeadername

tbl Labourcosting
*EmpID
*ShiftID
and Employee name and Number should be shown in this table

tblShift
*ShiftID
ShiftDate
Start From and To
Locations From and To

tblPlant
*ShiftID
*ShiftDate
Machineused
Hoursbooked

tblActivity
*shiftID
*Record number
timefrom and To
Locations
Delays etc..

In the form page I want to enter a date how can I tell it to update all the
other related field in a different table (say shift,acticvities ect).

In the activity table more than often there are many activites and needs to
be logged is the record number is sufficient to identify the activities
record. I at a later I want to query the activity by date can this be done
using the record number.

 

[TC]

Just to comment on a few tables (cos I have to go riht now):

* denotes primary key

tbl employee
*EmpID as PK
EmpName
EmpNumber
Classification

Looks fine. I guess you have some reason for not using EmpNumber as the
PK?

tblTeam
*TeamName as PK
TeamLeadername

tblTeamDetails
*EmpId as PK
*TeamName as PK

I wouldn't do that. First, the team name might be something like
"Manufacturing 'B' team (temp.)". That is not good as a PK. I would add
a TeamID, then the team's name is just a simple attribute. Second, you
definitely do not want to identify the team leader by their name. What
if someone edited their name in the employee table but not in the Team
table? You need to use their EmpID - TeamLeaderEmpID, or somesuch.

So I would replace those two tables, with these:

tblTeam
*TeamID (PK)
TeamName
(other characteristics of the team as a whole)
TeamLeaderEmpID

tblTeamMembers
*TeamID } composite
*EmpID } primary key
(other charactersitics of THAT employe on THAT team).

The other way to deal with the team leader would be to delete
TeamLeaderEmpID, ad add a Role flag to tblTeamMembers. Then you can
define the role for each person on each team: perhaps TL = team leader,
PM = project manager, & so on. You could even have joint team leaders
if you wnted that

IMO, using he Roile approach would be more flexible. It would be easy
to do it like that to begin with - harder to change it to that, down
the track.

HTH,
TC