0

I'm asking this as I'm kinda overwhelmed by the data presented to me (I'm still fairly new in programming and SQL). Basically I need to create a database to store warehouse equipment information, and of course to make it as simple as possible when retrieving these data in .NET

Here are the key attributes of the equipment that perplexed me in creating a SQL table for "Equipment":

  • Many, but not all equipment can have SSN (this is usually a 10-digit number to uniquely identify the type of equip)
  • Many, but not all equipment can have serial (a number to uniquely identify the SAME equip which has multiple quantity)
  • All equipment have description (this is the name of the equip)
  • Some equip may have both SSN and serial, while some equip have none.
  • Location: Tells where the equipment is stored in the warehouse. Equipment with different serial may be stored in separate location. As such, need to keep track of location for every serial.
  • Set: Tells which equipment is part of a set. For example, a machine can be part of a set together with an antenna and a wire, all 3 are different equipment stored in "Equipment" table.

That's about it, but it's too complicating for my current skills. This is so far what I'm able to come up with:

tblEquips:

  • EqpID - Since an equip can have neither SSN or serial, I have to create an incremental EqpID
  • SSN
  • Description
  • currentQty
  • totalQty
  • location
  • assemblage - datatype is bit, tells whether this item is a set
  • Serial - I don't know how to store this, I'm thinking of storing a long list of serials separated by a comma? But it won't work when it comes to identifying which serial is in which location

tblAssemblage:

  • SetID
  • Set - store to identify a set along with its information. Eg. a machine, an antenna and a wire. Maybe XML data type?

As such, anyone can help modify, or even work on new tables to organize this whole chunk of interrelated data? Thanks in advance for the help, and sorry about my English - it's not my native language, I hope it's comprehensible.

  • Storing comma separated values is almost always a really bad design. Btw: which DBMS are you using? Postgres? Oracle? – a_horse_with_no_name Mar 3 '14 at 9:33
1

Can an equipment be part of multiple set's? ( assume: yes)
Can an item(one piece with one serial) be part of multiple equipments? (assume : no)
I would break the equip in parts at the serial level as smallest item.

Equip:
eqid
ssn
description
currentQty
totalQty

Item:
item_id
eqid
serial
location

Set:
setid
description?

Equip_to_set:
setid
equipid

With this model you have the location per unique serial item.
You have equip with multiple set assignments.
SSN and serial are optional.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.