How to store inventory properties?

[tuna]

I have a lot of structures in my database like so:



Consider inventory - I might have stocks of balloons, for which I need to record colour, or I might have stocks of cars for which I need to record their registration plate numbers, and so on. The point is, I will need to record different properties for different types of inventory, and each property is unique to that type of inventory.

So naturally, I figured that since each property field (i.e. balloon colour, car plate number) was going to be inventory specific, I would take a bit of an "object oriented" approach by creating child property tables that were also inventory specific, kind of like having subclasses of a class "Property":

tblProperties (field PropertyID) ---> (LJoin PropertyID) tblPropertiesBalloons (fields PropertyID, Colour)
_________________________ ---> (LJoin PropertyID) tblPropertiesCars (fields PropertyID, RegNo)
________________________ ---> ... and so on

I'm simply wondering if this is the best way to approach this. I know that I could have recorded all properties in on table with loads of different fields, but is that structurally sound?

Ultimately, I was envisaging what eBay can do, when if you list balloons, a field for "colour" will appear on the page, and when you list a car, a field for "registration number" will appear on the page. Is this simply achieved by hiding and showing fields at runtime? How would their tables be structured?

I've been puzzling for a long time over this now so sorry for lots of questions.

Thanks very much.

[tuna]

Also, I have properties like "quantity", which details the quantity of a box of stock. I would then need to perform calculations on this type of property (summing) to give me quantity totals for each type of inventory.

In addition, I need to keep historic data on these properties. For example, I have a "condition" property (which gives information like "new" or "old") and I need to keep records of when an piece of inventory goes from new to old etc. I then work out the current state of the inventory by reading off the most recent record. In fact, I currently use this method for all types of properties, but access is often complaining "system resources exceeded" or "cannot open any more tables".