Need help to design a Master Inventory List

I'm currently trying to make an inventory system. Since most product than can be sold have a UPC Code, I'm creating a List which has the following Rows : Id, UPCCode, QuantityInStock, Price. I have a ID Number because I might sell stuff that don't have a UPC Code but I still want them to be accessible in the system.

My problem is: I want to show different information which depends a lot on which product it is : If it's a book, I want to be able to get who wrote it, how many pages it has, which edition... But if it's a Video Game, I want to be able to get the ESRB Rating, the platforms...

How can I make a Master list of all the UPC Codes that gives me the flexibility to get information depending on what type of product it is?

There's 2 way I've seen this: In my Master inventory list, I add two rows which are Type (Book, Videogame, Other...) and a ID. This way I can test my records, if the Type column is "Book", I search the Books table to find which has that ID.

Second way, I don't keep a Master list, instead I query the "Books" table for the UPC Code, if it's not found, query the Videogame table, and then the "Other" table.

Is any of those two solution good or am I missing something?

Thank you

Your second method, essentially will NOT work. Or rather using it will make your application sluggish, unwieldy, basically impossible to maintain.

Your first method, using typing will work. The structure will look something like this:

<b>Item</b> - Data table
ItemID
ItemUPC
ItemName
ItemTypeID

<b>ItemType</b> - Lookup table
ItemTypeID
ItemTypeName - (e.g. book, game, dvd, etc)

<b>Book</b> - Data table
BookID
ItemID
BookISBN
BookAuthor
BookNumPages
etc.

<b>Game</b> - Data table
GameID
ItemID
GameESRB
GamePlatform
etc


With this method entering data is fairly easy and retieving data will require two steps. First will be to determine the type of item and then to get the data for that item.

There is a 3rd solution which is a parameter based design. A parameter based design has a lot more setup and code to do the insert, but the retrieval is a simple one step process.

A parameter based design looks something like this

<b>item</b> - Data table
ItemID
ItemTypeID
ItemName

<b>ItemType</b> - Lookup table
ItemTypeID
ItemType

<b>ItemTypeParameter</b> - Lookup table
ParameterID
ItemTypeID
ParameterName (e.g. ISBN, ESRB, Author, Game Platform, etc)
ParameterType (e.g. Text, Date, Number, etc) (NOTE: if all of your values will be Text this is not needed)

<b>ItemParameterData</b> - Data table
DataID
ItemID
ParameterID
ParameterData - this is field that has the actual data or value. (e.g. Playstation 2, Shakespeare, 0010034532, etc)

With this method when you add a new item and select its type, you do a query to get all the parameters for that type and create rows in the ItemParameterData table for each one. When you need to get data for a particular item you go to a single table, the ItemParameter table to get the data.

This is a very flexible design because adding a new type or new parameter does not require much, if any, new coding. However, depending on the RDBMS and application environment you can take a performance hit.

Have fun!

Thank you very much. I've had a similar answer from a thread I made in another forum and I've opted for the 3rd solution since I want to have maximum flexibility.

The only question I have is: I'm not sure to understand the use/need of the "Parameter Type" field in the Parameter table which is suposed to hold the type of the value it will contain since the "ParameterData" from the Data table will have to be a varchar to hold every type of value possible. Any enlightment on that?

Thank you for your answer, I'm glad to see that I can get some help.

Whether or not you need the parameter type column depends on your design and what needs to happen with the data. But there is one reason for having it even if you just use a Varchar and don't need to do any calculations. Data validation.

If you know that a value should be a date or an integer then you check that you are not accepting "zzz" as either a Date, cost/price, or quantity. You can have your validation hardcoded, or again, have it driven by the data in your parameter lookup table.

Also if you need to use a value in a calculation either numerical or date you will need to know what conversion to make.

For instance suppose one of the parameters was MSRP (mfg suggested retail price) you can put the data in a varchar field easy enough, but if you want to return all items with a MSRP between $15 and $25 you will need to do a conversion of the data. The parameter type column can tell you want type of conversion you will need to do.

Remember that a text sort of values like 5, 10, 15, 35, 200 will get you:
10
15
200
35
5

To avoid doing coversions all the time an alternate design for the parameter data table is:

<b>ItemParameterDate</b>
DataID
ItemID
ParameterID
ParameterDataText
ParameterDataDate
ParameterDataSingle (floating point)
ParameterDataBoolean

With this design you always populate the text field and then also populate the other data type field if necessary. This means you have duplicate data and must make sure to update BOTH values if there is a change, but you don't have to do conversions.

Enjoy!

I get it now. Thank you.