Ok, and so i am developing a sql database to keep an eye on inventory. I've around 170 individual kinds of products. A few of the types have several item inside them. For instance: I've got a 'cisco' switch (Kind of Item) and that i have 20 of those switches.

I'm trying to puzzle out how you can enter these in to the database. The items includes a serial number, should i enter each switch individaully in to the database? Or it is possible to way I'm able to go into the switch type once and list all of the ghd serial numbers for your one row? I wish to make use of the Count function to obtain a count of the number of rows you will find for every item also.

I'm just trying to puzzle out the easiest method to set this up. Not requesting an answer, just suggestions. tell me if more information is required.

Listed here are the tables I've as well as their posts:

Table: Item

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

Table: ItemDescriptor

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

Table: Add-ons

Posts: id, ItemDescriptorID, OnHandCount, StorageRoomCount

Table: Location

Posts: id, Location

Warning: Serious database design is really a non-trivial art. You might spend a very long time just determining the right model for the data. Do this. It's the perfect time wisely spent. Every mistake or omission now can cost you manifold later.


Store just as much information as you've, organized relationally to ensure that there's no redundancy. So one table for those individual products, and every item includes a part type one table of part types, each kind includes a vendor along with a category (switches, cables, monitors, ...) one table for suppliers one table for groups, etc.

In the event that any particular unit of knowledge needs to be joined two times, then your relational model is wrong.

Finally, result in the database present you whichever information you need to care to understand about, for example counts, tallies, earnings, etc.

To have an inventory, I recommend you have a minimum of two tables for products. You might be for that generic item ('cisco' switch model 3750 for instance) and the other would connect to that table and also have the specific products (resource #, serial #, etc.) This can really begin to branch from here, for the way complicated you need to get. For instance, I saw the next being potential fields or tables based on your atmosphere and preferred complexity:

  • Location (can there be several item in a)
  • Purchase information (would you purchase them in batches)
  • etc.

I'd produce a table listing your items with unique ids. A table that contains the serial number and also the id towards the description of product and what else may be unique to that particular item. You are able to still count by particularly choosing just the products using the 'cisco' switch's id.

items - id - title ('cisco' Switch) - category - etc

- id (for originality) - product_id - serial - date added - etc

Attempt to eliminate anywhere where details are repeated. Hope this can help :)

You need to produce a general table for products, along with a table for Category. You need to produce a seperate table of Stock, that will have itemID and categoryID, as well as Quantity column. All the details associated with item come in item table, and category table may have all kinds of products you've within the inventory. Item and category is going to be you setup tables. You need to enter records once, and you can reutilize it at different places in your soul inventory system.

This can be a surgestion for any solution using temporary tables. Assistance you a little.

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