Need help evaluating my database design

[TimIT]

I am trying to create an Access 2003 database that contains several types of
demographic information about the employees in my organization (40+
employees). I have taken several classes on Access and read several
tutorials, so I have finally created a database framework, but I feel as if I
am not doing things as efficiently as I could be. I was wondering if anybody
could look at my database and let me know if they notice anything in my
tables or fields that could be better/fixed. I would appreciate it so much.

You can download my .mdb file at the following location:
http://som.ucdavis.edu/Members/majahops/employees.mdb

.... I zipped it and it decrease the size by a TON, so if you aren't sketchy
about downloading a .zip file, you can download it from this location:

http://som.ucdavis.edu/Members/majahops/employees.zip

Thanks so much. Feel free to reply via this thread or email me with any
comments/help you can give me. Thanks so much again!

TiM

 

[Tim Ferguson]

but I feel as if I
am not doing things as efficiently as I could be

I agree that you have quite a bit of work to do yet! Here are some
immediate thoughts:

Employees: this is the one where all the stuff about the people should be
kept. NameFirst and NameLast, Birthday, Address etc are good choices.
Phone, Phone2, Fax, Pager, Mobile are not: as sure as eggs is eggs,
someone will have two fax numbers or three home numbers. You would be
better off with a new table for PhoneNumbers with fields (EmployeeID,
TypeOf, NumberToBeDialled), which would have one row _per number_ and
lots of rows per employee. The NumberToBeDialled would be a convenient PK
for this, unless you have employees that share numbers.

Attributes: this one is in severe trouble. Fields like Skill1, Skill2,
.... Skill3 are a dead giveaway. Replace this with two tables, one for
Skills and one for Degrees, with one-to-many relationships as above.

Employment: I am not clear what you are trying to model with this table.
If employees have several contracts with the employer, then you'd need a
table of Contracts -- I guess this is what you are trying to get at with
JobTitle1 and JobTitle2, JobSummary1 and JobSummary2 and so on. On the
other hand, you only have one DateHired, so it's not clear which JobTitle
that would apply to. You need to be much more specific about how this
table should work. What do you want to do about historic information? The
SupervisorImmediate field is a text field: should this not be linked to
another Employee record, or rather another Contract?

Computers: this needs to be more specific too. Are you modelling a lump
of plastic-and-wires sitting on a desk, or a particular employee's access
level? For my money, I would probably want LoginID and Network in the
Employee record, and the hardware stuff in the Computers table. Once
again, EMailClient1 and EmailClient2 fields suggest that really you
should be looking at a many-to-many relationship. On the other hand, if
you want to monitor licenses, a dedicated package might meet your needs
better.


In general: nearly all your fields seem to be Text(255) -- including, for
example, HDDSize. This is legal but it's not a very good idea for several
reasons. If I were you, I would be thinking hard about the nature of
stuff that is going into each of the fields. Several will actually be
pointers (i.e. Foreign Keys) into further related tables: MonitorBrand,
GraphicsCard, Projects, SkillDescription and so on all come to mind.

Even more general: I don't get much feel from the tables you suggested
what this database is actually _for_. Automating payscales? Training and
skills analysis? LAN management? Disciplinary? I imagine many of your
choices would be a lot easier if you have a really precise business case
to refer to, because that is the document that will tell you where you
can make compromises and where you need to be rock-solid.

Oh dear: I hope that does not sound too negative! There are some basics
of relational design that you need to be clearer on, in order to get
anything worthwhile out of Access. Stick around the NG here and get a
feel for the messages you see time and time again: about normalisation,
about picking out the entities that need to be modelled and about how to
relate them to each other.

All the best


Tim F