?is my table design poor?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to create a database to track employee movement (new & terminate). I m
afraid I have poor table design, which cause me to supplement many queries to
prepare monthly report. Would appreciate comment & suggestion on my table
design

Dbs will prepare 3 rpts
(i) Total Co carry fw last year, monthly new, monthly
terminate
(ii) By divsision, carry fw last year, quarter new,
quarterly terminate
(iii)Be division, carry fw last year, total year new,
total year end, total year end balance.

My table is structure with the following field
(1) Emloyee ID
(2) Emloyee name
(3) Division
(4) StartDate
(5) EndDate
(6) StartQtr
(7) EndQtr
(8) StartMon
(9) EndMon
 
The table looks ok to me. What is the problem?
I would expect you to need a different query for each different report.
If you had a 'start year' and 'end year' as well it might make some queries
quicker.
You might want name split into first and last so you could sort by last name.

-Dorian
 
A couple of suggestions --
(1) Emloyee ID - (PK)
(2) Emloyee name -- use separate field for LName, FName, Middle, Suffix (JR,
SR, II, IV, etc), Sex, Birthdate, etc.
(3) Division - people move a lot - use a separate table for the next three
data items
(4) StartDate - above
(5) EndDate - above
(6) StartQtr - do not store - can be computed
(7) EndQtr - do not store - can be computed
(8) StartMon - do not store - can be computed
(9) EndMon - do not store - can be computed

Assigned
(1) Emloyee ID - (FK)
(2) Division -
(3) StartDate -
(4) EndDate -
(5) Supervisor (FK) related to Emloyee ID in employee table
(6) Grade-Level
(7) Title
(8) Remarks
 
Just a tip: when discusing table structures, you should always
explicitly state the primary key of each table. This is particularly
important with child tables (of a 1:many relationship), where the
primary key could have more than one field (a so-called "composite"
primary key). In my opinion, it is always a bad idea for readers to
guess the primary keys. For example, I can /guess/ that you have
defined Employee ID as the primary key field of that table - but I
don't /know/, and for that reason I decline to guess :-)

HTH,
TC
 
Just a thought, but it seems to me that the Start/EndQtrs and
Start/EndMons are not needed. I may not understand how they are used,
but my guess is that this information could be derived from the
StartDate and EndDate.

Betsy
 
Yes, looking at it now, some of those fields do not look likely, in
that table. I just noted the lack of PK indication, & thought I would
comment on that :-)

Cheers,
TC
 
Back
Top