database design

[Guest]

sorry I have posted this before but help would be greatly appreciated if you
could comment to see if I am on the right track

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.

 

[Guest]

It is apparent that you have not built the tables and put data in them.
A table can not have two primary keys as you show in tblTeamDetails.

Why not use the EmpNumber as the primary key in the tblEmployee instead of a
separate field?

Separate the name into LastName, FirstName, Middle, and Suffix (JR, SR, II,
IV, etc.).

Does the employee never change classification? Use a separate table to show
classification and DateChange.

Your tblLabourcosting does not collect any cost information. Why would you
need Employee name and Number in the table as that information is already in
the tblEmployee?

 

[TC]

You posted this question before & I answered it!

Why should anyone answer again?

Try responding to the help that you already got.

TC

 

[Guest]

TC
sorry about the repost I got it working now after reading your comments in
my previous post

 

[TC]

Well, you've changed it a bit since the previous time, but it still has
some of the previous problems. For example:

tblTeam
*TeamName as PK
TeamLeadername

Team /name/ is not good as a primary key. Better to have a seperate
field, TeamID (perhaps an AutoNumber), as the primary key.

Team Leader /Name/ is just plain wrong, as I said before. What if you
have two John Smith's?

I sugget you go back & re-read my previous comments & check that you've
considered them all

Cheers,
TC

 

[John Vinson]

It is apparent that you have not built the tables and put data in them.
A table can not have two primary keys as you show in tblTeamDetails.

Actually a Primary Key can consist of up to *ten* fields. There are
problems (as you point out) but this isn't one of them.

John W. Vinson[MVP]

 

[TC]

It is apparent that you have not built the tables and put data in them.
A table can not have two primary keys as you show in tblTeamDetails.

He just showed it wrongly. I believe he meant them as a composite
primary key.

Cheers,
TC