If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
?is my table design poor?
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 -- Thanks a million for your time and expert advice :-) Jaylin *****Jaylin Message ended******* |
#2
|
|||
|
|||
?is my table design poor?
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 "Jaylin" wrote: 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 -- Thanks a million for your time and expert advice :-) Jaylin *****Jaylin Message ended******* |
#3
|
|||
|
|||
?is my table design poor?
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 "Jaylin" wrote: 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 -- Thanks a million for your time and expert advice :-) Jaylin *****Jaylin Message ended******* |
#4
|
|||
|
|||
?is my table design poor?
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 |
#5
|
|||
|
|||
?is my table design poor?
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 |
#6
|
|||
|
|||
?is my table design poor?
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Help with relationship plase | Rock | General Discussion | 5 | July 4th, 2005 03:54 AM |
Seeking some expert advice. | HD87glide | Using Forms | 14 | March 23rd, 2005 10:11 PM |
unable to repair inobox | Sudheer Mumbai | General Discussion | 1 | February 20th, 2005 11:55 AM |
Update - If statement | Dan @BCBS | Running & Setting Up Queries | 13 | December 14th, 2004 06:02 PM |
Autonumber | Ally H. | General Discussion | 7 | August 27th, 2004 04:51 PM |