I am attempting to create a database for an SQL class. My database will be for a photographic equipment rental company. I am having a little bit of trouble devising a system to track the inventory.

CAMERAS
---------

ID_EQUIPMENT pk
MANUFACTURER
MODEL
SENSOR
MOUNT
TYPE
DESC

SERIALS
---------

ID_EQUIPMENT fk
SERIAL
COST

The cameras table describes each camera (i.e., Nikon D80 dslr) while the serials table contains each Nikon D80 with the appropriate serial number and amount paid for that exact camera. There will be seperate tables for LENSES, LIGHTS, etc... each with an ID_EQUIPMENT primary key, but with obviously different attributes.

My problem is that I can't figure out how to track the inventory. I could have a boolean STOCK field in the SERIALS table, but some of the items from other tables (i.e., Westcott 42" folding umbrella) do not have serial numbers.

I have never asked a question in a forum like this, so if I haven't given enough information, please let me know.

Thanks all for your time.

Nathan Nontell
Email is first initial, full last name, at ivytech.edu

After reading your post, I just want to confirm something...

One camera can have many serials (1:M relationship)?
i.e. you store Nikon D80 dslr with a single ID_Equipment in the cameras table, and store the serials of each individual Nikon d80 dslr in the serials table

Yes that is absolutely correct. I may have 3 Nikon D80 dslrs, each having an ID_EQUIPMENT of CA007, but each having a unique serial number. Some pieces of equipment will have unique ID_EQUIPMENT, but will not have serial numbers.

Thank you for taking a look at my post, and I eagerly await any suggestions you have. BTW, congrats on the engagement!

Looking at your current table strutcue, my advice would be to put every product into the serials table - whether they have a serial number or not.

Unless you're already storing the items in a seperate table... There's no way of knowing which items you have in your inventory!

Yes, but if they have a serial, then the row will be unique, and the best I could do would be to flag it boolean T/F as to whether that unique item is in inventory.

I have considered putting a quantity field in the tables that contain all non-serial numbered items, the small accessories, and tracking them that way. Leaving the bigger ticket items to be tracked individually through the serials table. I'm pretty sure that that would work, it just doesn't feel very eloquant to me. Was hoping for a slicker solution.

If you are storing the type of item in one table, then you need to record every instance of that item in another - it doesn't sound like you are doing this!

Example:
I have 3 Nikon D80 dslrs, but only 2 have serial numbers.
So I input two records in the serial table..

What happens to the third?!

Unless you need to store all the detail specification for each type of camera device then I dont think you need to have a separate element for each attribute. Nether do i think that a separate table for each product type is sensible. one you dont necessarily know right here right now every type of photographic equipment the company may hire out.

it could be 'just' cameras, lenses, lights, flash guns, umbrellas, screens, tripods, stands etc...

it could be that they suddenly decide to take on video equipment, it could be that they start do to TV/film hore so mebbe the next step is generators

equally you cannot neccesarily be certain that you will capture every possible feature that may be required for each device type at design time. after all you never know when a new manufacturer may intorduce a new product with revolutionary features.. so do you as db designer have to do an application rewrite for each new product type the company takes on, for each new feature a manufacturer introduces.

Personally I think the features can be handled in a different manner. you could go down the route of say an XML list in a large text / memo / blob field and leave it up to your front end to handle the encoding/decoding of the individual XML attributes. I would keep the manufacturer and product type as part of the main record and not part of the XML specification. Conceivably the XML data could be searched using a full text index or whatever your db uses to allow indexing / searchingn for words within a string.

Another approach could be to use an entity table with an entity type

itemtype could be somthing like
itemID
itemDesc eg: lens /*this could be sub typed to then incude wideangle, standard, zoom, telephoto*/

if you extended you could get something like
itemID ItemDesc ItemGroup
1 | Still Photography |
2 | Camera | 1
3 | Lens | 2
4 | standard | 3
5 | wide angle | 3
6 | lens tissue | 3

then link the itemtype with a product

itemtypeID 'say 2: camera
ProductID 'say 104: nikon FD150
ItemDesc 'say sensor array n x o pixels
personally i'm not certain thats right.

the XML route may be a sensible option, espceially if you constrain the access to that data to be throigh an XML template or other code int he front end.

Thank you both for your replies!

This is only a class project and I have approached my professor about doing it this way: Using a table to store all equipment in, having a GROUP_TYPE field in that table, having triggers that will not allow certain attribute values to be null if certain GROUP_TYPEs are chosen. So, a record of GROUP_TYPE lens would not be allowed to have a null MIN_FOCAL or MAX_FOCAL, but would be allowed to have a null SENSOR_SIZE... I would still have my serials table with its STOCK field to track big ticket items, and a quantity field in the equipment table to track small items. This arrangement is fine for the project, per my professor.

Having said that, I don't like it all that well. (although writing those triggers was fun!)

georgev: you are exactly right. HOW to store each instance of an item individually, when some will be stored by serial, and some do not have serials, is my entire conundrum. Yes, I could autogenerate a serial, but then there will be confusion as to what is an ACTUAL serial and what isn't. The business rules of my pretend company require that serial numbers are recorded at time of rental so that the company can ensure they get the SAME camera/lens back.

EDIT: georgev, 3 Nikon D80s would all have serial numbers, but very small accessory items would not.

healdem: I don't think your solution addresses the problem that I'm having at all. However, it addresses my OTHER problems quite well! I had already thought about including a spec sheet, but I had never even considered an XML sheet that could be searched! That's brilliant! Probably far beyond the scope of my knowledge and this class, but I think that I may work on such a thing in my spare time this summer. I've used VB to read/write from XML before, so I'm sure I could eventually figure out how to do what your describing. Thanks for the great idea, and if you have any further input I'd love to hear it!

Nathan

SERIALS
---------
ID_PRODUCT <-- Primary Key (Autonumber)
ID_EQUIPMENT <-- Foreign Key
SERIAL <-- Allow Null values
COST

Would this would get round the problem?