Question about setting up inventory database

Ok, so I am creating a sql database to keep track of inventory. I have around 170 individual types of items. Some of the types have more than one item in them. For example: I have a cisco switch (Type of Item) and I have 20 of these switches.

I am trying to figure out how to enter these into the database. Each item has a serial number, do i need to enter each switch individaully into the database? Or is there a way I can enter the switch type once and list all the serial numbers for that one row? I want to use the Count function to get a count of how many rows there are for each item also.

I am just trying to figure out the best way to set this up. Not asking for a solution, just suggestions. let me know if more info is needed.

Here are the tables I have and their columns:

Table: Item

Columns: id, ItemDescriptorID, LocationID, AccessoriesID, SerialNumber, AssignedTo

Table: Ite开发者_StackOverflow社区mDescriptor

Columns: id, Brand, DeviceName, Model, Image, Type, Notes

Table: Accessories

Columns: id, ItemDescriptorID, OnHandCount, StorageRoomCount

Table: Location

Columns: id, Location

---

Warning: Serious database design is a non-trivial art. You may spend a long time just figuring out the correct model for your data. Do that. It's time well spent. Every mistake or omission now will cost you manifold later.

---

Store as much information as you have, organized relationally so that there is no redundancy. So one table for all individual items, and each item has a part type; one table of part types, each type has a vendor and a category (switches, cables, monitors, ...); one table for vendors; one table for categories, etc.

If you find that any particular unit of information has to be entered twice, then the relational model is wrong.

Finally, make the database present you whichever information you should care to know about, such as counts, tallies, averages, etc.

---

For an inventory, I would suggest that you have at least two tables for items. One would be for the generic item (Cisco switch model 3750 for example) and another would link to that table and have the specific items (asset #, serial #, etc.) This will really start to branch out from here, depending on how complicated you want to get. For example, I could see the following being potential fields or tables depending on your environment and desired complexity:

• Location (is there more than one item in one location)
• Purchase information (do you buy them in batches)
• etc.

---

I would create a table listing your products with unique ids. Then a table containing the serial number and the id to the product description and what else might be unique to that item. You can still count by specifically selecting only the items with the cisco switch's id.

{products} - id - name (Cisco Switch) - category - etc

{inventory} - id (for uniqueness) - product_id - serial - date added - etc

Try to eliminate anywhere where information is repeated. Hope this helps :)

---

You have to create a general table for items, and a table for Category. You have to create a seperate table of Stock, which will have itemID and categoryID, and also Quantity column. All the information related to item will be in item table, and category table will have all types of items you have in the inventory. Item and category will be you setup tables. You have to enter records once, and then you can reutilize it at different places in you inventory system.

---

This is a surgestion for a solution using temporary tables. It may help you a bit.

DECLARE @itemtypes TABLE (id INT IDENTITY (1,1), name VARCHAR(50))
DECLARE @itemstock TABLE (id INT IDENTITY (1,1), itemtype_id INT, serialno VARCHAR(20))

INSERT INTo @itemtypes (name) VALUES ('cisco switch')

INSERT INTo @itemstock (itemtype_id, serialno)
SELECT id, 'serial no 1' FROM @itemtypes WHERE name = 'cisco switch'
UNION all 
SELECT id, 'serial no 2' FROM @itemtypes WHERE name = 'cisco switch'
UNION all 
SELECT id, 'serial no 3' FROM @itemtypes WHERE name = 'cisco switch'

SELECT it.id, it.name, COUNT(ist.id) count
FROM @itemtypes it left outer join @itemstock ist ON it.id = ist.itemtype_id 
GROUP BY it.id, it.name