Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Inventory Database table schema design (2002/SP3)

    I want to create a database to track inventory in the IT Department. I have drawn it out somewhat but wanted to get ideas from some of you that may have already created a similar database. I was thinking of having a table called tblEquipment that will have the fields that are similar to all pieces of equipment like workstations, thin clients, pocket pcs, printer, ect.... So the equipment table would include fields like EquipID, EquipmentType (Computer, Printer, ect...), SerialNumber, Date Purchased, Purchase Price, Status (whether it is in use or not), ect... I was then thinking I could create a details table for each piece of equipment that would contain the fields that pertain just to the selected EquipmentType in tblEquipment. Now I'm thinking if I'm going to have a separate table for each equipment type I might as well just have 1 table for each piece of equipment.

    How would any of you go about designing a database like this? I was hoping that I wouldn't have to create a separate table for each piece of equipment but with each piece of equipment requiring their unique fields that only pertain to them. There has got to be a better way.

    Any ideas or examples would be greatly appreciated.
    Thanks,
    Don

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Inventory Database table schema design (2002/SP3)

    I certainly wouldn't create separate tables. Try to find a common denominator of what you need, plus perhaps one or two "free" fields.

    Microsoft has a free template for a Inventory Control database: Microsoft Office Templates: Inventory management database

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inventory Database table schema design (2002/SP3)

    Is it considered bad practice to include fields in a table that will only be populated when specific equipment is added? For example, if I include Computer Name, IP Address and few others as fields that will only be populated when a computer is added to the database and not populated when I add a printer for example.

    Thanks again,
    Don

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Inventory Database table schema design (2002/SP3)

    It may not be really good design, but you have to be practical too.

    An alternative could be a "properties" table, linked to the main table by InventoryID:

    <table border=1><td>InventoryID</td><td>PropertyName</td><td>Value</td><td align=right>37</td><td>ComputerName</td><td>C3058</td><td align=right>37</td><td>IPAddress</td><td align=right>168.21.22.23</td><td align=right>38</td><td>PostScript</td><td>-1</td></table>
    This is very flexible.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inventory Database table schema design (2002/SP3)

    Great idea! Thanks!

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Inventory Database table schema design (2002/S

    Hans' property table suggestion is certainly worth trying... I have it in mind too for cases like yours and it's very flexible. You don't need to create extra fields for extra properties, etc. Further, you can lateron always turn (the whole or a part of) such property table into a 'flat' one (turning the property names into fields) using a crosstab & make table query...

Posting Permissions

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