Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2005

    Unanswered: Formatting Access Database: Question...

    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:

    ... 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:

    Thanks so much again I look forward to hearing from you!


  2. #2
    Join Date
    Dec 2004
    Madison, WI


    I would recommend having an autonumber field in all of the tables. Also, there are no relationships set up. You may want to set up some relationships.

    Just my thoughts at a very quick glance.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Mar 2005

    thanks... but...

    do I even need to have seperate tables the way I do? The only autonumber I plan on having in all of the tables is the employeeID anyway, since all of the fields correspond to the particular employee (i.e. employee's computer, employee's projects - which are uniquely named in each employee's case). Any ideas? Thanks so much.

  4. #4
    Join Date
    Mar 2005
    After a look at the tables in your database I would recommend to do some normalization. This means you do not need less, but more tables to avoid redundant data and possible inconsistencies.

    I would recommend the creation of at least the following additional tables:

    - addresses
    - phonenumbers
    - phonetypes
    - emailaddresses
    - jobs
    - degrees
    - skills
    - divisions

    and remove the fields therein from your original tables.

    The employees tables would the probably look like this:


    and the table addresses like this


    and the phonenumbers table like this


    and phonetypes like this

    phonetype (office, home, fax, mobile, pager)

    Then you would need some more tables to store relations between tables:

    - addressestoemployees (addressid, employeeid)
    - phonenumberstoemployees (phoneid, employeeid)
    - phonetypestophones (phonetypeid, phoneid)
    - emailstoemployees (emailid, employeeid)
    - employmentstoemployees (employmentid, employeeid)
    - computerstoemployees (computerid, employeeid)
    - attributestoemployees (attributeid, employeeid)

    This is called normalization and since it causes some more work when creating the data model and the database design it reduces the amount of work later on by avoiding redundancies and incostencies since every piece of information is only stored once in your database.

    *) One could even go so far to create a table for the zipcodes itself:


    and a table

    zipcodestoaddresses (zipid, addressid)

    just in case the zip code for a city would eventuelly change and not to have to update all address records from this city...

    Good luck!

  5. #5
    Join Date
    Dec 2003
    I just want to expand on the eloquent description from the previous poster.

    After you have gotten your table to 3NF (third normal form) you are going to want to create some indexes on primary fields within each table. Any field that may be used in a primary search and always the unique identifier field for each table.

    AFter that is done, you are going to want to create relationships between the tables. 1-m and choose whether or not you want to enforce referential integrity, i.e. cascading of deletion and update actions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts