Results 1 to 2 of 2
  1. #1
    access2day is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    May 2012
    Posts
    1

    Database design Question


    I am in the process of reworking an equipment inventory database. The inventory has 1 table only and many queries created from it. The database is for tracking where equipment is located and for annual reconcilation to a master list for accountability. Equipment is added, deleted as well as other paremeters updated, etc... What is the best appraoch for developing a database where employees can easily perform these tasks?

    Thank You,

    Access2day

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Welcome to the forum!

    First, one table is not the answer. Without more details, I would guess that you will need 3 tables at a minimum. Setting up the table structure correctly is fundamental to the success of the database application, so you will want to spend some time analyzing your data and finding out what relationships exist in your data.

    A table to hold the basic information about the equipment

    tblEquipment
    -pkEquipID primary key, autonumber
    -txtEquipNo
    etc.

    A table to hold the locations

    tblLocations
    -pkLocID primary key, autonumber
    -txtLocationName


    Now, a particular location may have many pieces of equipment (one-to-many relationship). Additionally, a piece of equipment many reside in many locations over the course of its life (another one-to-many relationship). Since you have 2 one-to-many relationships between the same two entities you have a many-to-many relationship for which we need a junction table

    tblEquipmentLocations
    -pkEquipLocID primary key, autonumber
    -fkEquipID foreign key to tblEquipment (long integer number datatype)
    -fkLocID foreign key to tblLocations (long integer number datatype)
    -dteEffective (effective date of when the equipment was at the specific location)

    Anytime a piece of equipment is moved, you would have a record in tblEquipmentLocations specifying the new location and the date.

    In terms of using the database, I would not give your users the ability to delete records. Allowing them to delete records will get rid of the history of a piece of equipment which you may need for tax or other purposes.

    When it comes time to dispose of a piece of equipment, you would just add a record to tblEquipmentLocations with the location set to Disposal (this record would have to exist in tblLocations).

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Database design question
    By udigold1 in forum Database Design
    Replies: 3
    Last Post: 03-23-2012, 02:20 PM
  2. Design question
    By Daryl2106 in forum Access
    Replies: 2
    Last Post: 11-24-2011, 08:43 AM
  3. Database Design Question
    By AccessNewBiegr in forum Access
    Replies: 2
    Last Post: 02-08-2011, 08:22 AM
  4. A basic question about database design
    By guitarbinge in forum Access
    Replies: 2
    Last Post: 11-05-2010, 03:29 PM
  5. DB design question
    By dlburkins in forum Database Design
    Replies: 2
    Last Post: 08-28-2009, 07:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums