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:
- EqpID - Since an equip can have neither SSN or serial, I have to create an incremental EqpID
- 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
- 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.