Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    logical approach to building equip list? (2007)

    I am struggling with the logic of how to approach something. I am working on an eventmanagemtn application and have a form for entering an event. In this form, I will need to be able to identify which pieces of equipment the client needs - stand up table, sit down table, chairs, stools, grills, etc. I will also need to specify how many of each.

    Further (and this is where it gets a little tricky) each piece of equipment has certain accessories that come with the primary piece. For example, a sit down table (depending on size) will need two or three table mounts; a stand up table, will need table mounts and four extensions for each table mount. Certain type tables will need 'skirts' to cover the legs. And so on... each piece of equipment has accessories of different types that must be delivered.

    I don't need to be able to address the accessories on the event form but when I ultimately print the delivery list, I need to have the accessories totaled and listed. For example, total # of table mounts, total # of extensions, Total # of skirts, etc.

    I'm just asking for the best approach. I have an events table and an equipment table and think I may need an accessory table but not sure which fields I need in these tables for the equipment issue.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: logical approach to building equip list? (2007)

    So each piece of equipment has a set of Accessories that come with it automatically, whenever it is used. So there is no need to choose the Accessories.. they will just appear automatically on the report.

    This is the design I came up with quickly.

    tblAccessories lists the accessories and quantities that go with each piece of equipment.
    tblEquipment lists all the equipment available.
    tblEventEquipment lists the quantity of each piece of equipment needed for an event.

    The quantity of each accessory would be found by multiplying the two quantity fields together. (It would be simpler if you changed on these field names.)
    You may need to add extra fields to these tables.
    Attached Images Attached Images
    Regards
    John



  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: logical approach to building equip list? (2007)

    Here is a variation.

    If the same accessories apply to different pieces of equipment, it would be useful to separate the Accessories into a separate table, then have tblEquipmentAccessories to say how many of which Accessory go with a particular piece of equipment.
    Attached Images Attached Images
    Regards
    John



  4. #4
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: logical approach to building equip list? (2007)

    Thanks, that's helpful. I'm not sure if an accessory will apply to more than one piece of equipment. However, I was thinking about this and what would be wrong with putting the accessories and associated quantity in the equipment table? Each record would have an equip item AND the accessories that go along with that item. I don't anticipate more than 50 pieces of equipment.

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: logical approach to building equip list? (2007)

    To put the Accessories directly into the Equipment table you would need to create a number of field pairs (Accessory and quantity) to cope with the max number of Accessories you could foresee. (to add extra Accessories you would need to change the design of the table.)

    The problem with this is that to query Accessories and Quantities for the report, you need to query across multiple fields.

    One of the principles of relational design is (in plain English):
    "If for each of these, there are multiple of them, then the them go into a separate related table." In this "these" are pieces of Equipment, and "them" are Accessories.
    Regards
    John



  6. #6
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: logical approach to building equip list? (2007)

    okay, I suspected it would be a normalization issue. Thanks.

Posting Permissions

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