I have a table called assets, where an asset can belong to a user,team, or division, and possibly multiple of each. My issue is that the assets are highly variable, and can have properties associated with them that are different for each one.
ex. These could be assets:
1.)
type:workbench
cost:200
vendor:Acme Co.
color:black
2.)
type:microscope
serial_no:BH-00102
purchase_date:1337800923
cost:2040
and this could go on for hundreds to thousands of different types of assets.
How would I store this type of data in a normalized way that would be easy to query, without altering my tables every time a new asset type is added? Some of the fields are the present across all assets too, such as cost.
So far I figure that I should have:
assets
id,cost,purchase_date,asset_type_id
asset_types
id,name
division_assets
division_id,asset_id
user_assets
user_id,asset_id
but i do not know where to put the data that varies
I would suggest this:
assets (
id
asset_type_id
vendor_id
cost
purchase_date
)
asset_poperties (
id
asset_id
asset_property_type_id
value
)
asset_property_types (
id
property_type
)
asset_types (
id
asset_type
)
vendors (
id
vendor
)