Hello all,
Looking for a best approach really, I'm in the process of designing an asset tracking database where assets may have any number of child assets which in turn may have any number of child assets and so on.
So I think my Asset Table will look something like:
CREATE TABLE dbo.tb_Assets
(
AssetID int PRIMARY KEY,
SKU_Ref int FOREIGN KEY REFERENCES tb_SKU(skuID),
Description varchar(255),
AssetParent int FOREIGN KEY REFERENCES tb_Assets(AssetID)
)
With a couple more fields for value etc. There will then need to be some properties that can be attached to certain assets but not all, I want these properties to be user definable and for users to add any number of them and attach them to assets. I was therefore thinking of a Properties table with Property and Description fields with a many-to-many join to the Assets table (some properties would be shared between assets).
However this opens up a few issues, ideally the properties should have different types for example some would be numbers, others would be text.
Any input into the design would be very useful :)
Db design with Dynamic Properties for Objects
Posted - 08/31/2012 : 06:10:45
