Table structuring

tuna

Registered User.
Local time
Today, 10:09
Joined
Mar 31, 2010
Messages
27
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.
 
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.


They way I would normalized/design this is to use these tables:

Lookup Tables:

1) InventoryType

- InventoryTypeID - Autonumber/Primary Key
- InventoryTypeDescription
- additional fields as needed

2) InventoryTypeProperties

- InventoryTypePropertiersID - Autonumber/Primary Key
- InventoryTypePropertiesInventoryTypeID - Long - Foreign key to InventoryType
- additional fields as needed

Master List Tables

3) Inventory
- InventoryID - Autonumber/Primary Key
- Inventory_InventoryTypeID - Long - Foreign key to InventoryType
- additional fields as needed

4) InventoryProperties
- InventoryID - Autonumber/Primary Key
- InventoryProperties_InventoryTypePropertiersID - Long - Foreign key to InventoryTypeProperties
- additional fields as needed


I would use a Main form with sub form for the Inventoriry properties. When selecting the InventoryProperties_InventoryTypePropertiersID you can use a combo box the limits the records using the Inventory_InventoryTypeID from the parent form.
 
Having tables for different properties is not the way to go because adding more items will require more tables.

Instead use just one table for properties of all objects. It will have ItemID, PropertyName, PropertyValue. (BTW, don't use Property or Properties for any names since they are reserved words.)

On your forms and reports, instead of a label and a textbox to display the property, use two bound textboxes. One displays the property name and the other the value. If you like the "label" can be set up to look like a genuine label control by removing the border.

Use Continuous Forms or DataSheet view in a subform linked by ItemID to show the properties. I prefer the Continuous Forms in this situation. You don't need headers showing "Property Name" and "Value" or something similar which you will get with the datasheet view.

If the same property names are used a lot for different items you could consider using a numeric PropertyNameID instead of recording the name directly, with a lookup to a PropertyNames table. It is a little trickier to set up for adding new names compared to just being able to write the property name straight into the "label" textbox control.
 
Thanks for your responses - I'll have a good think about your suggestions and get back to you. But the first question that pops up is what about different data types? You both suggest methods that tries to avoid the creation of new fields (or tables) for new types of properties, but what if I have a property 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.

Also, 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".

Thanks very much
 
Some specific fields you would keep, particularly those which apply to many inventory items such as quantity. Dates are also best kept as date fields.

Any datatype can be held as a string and converted to numbers or dates on the fly where appropriate. Indeed a string placed into a formula will automatically convert to a number. Dates are a bit more work.

How many tables do you have?

Are you rewting whole records with the updated information of just maintaining a history table for the item. Normally one would hold the history as a series of related records.
 

Users who are viewing this thread

Back
Top Bottom