What is the proper way to store 'metadata' in relational database?

mysql, normalization, rdbms, sql

Solution

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

)

Problem

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

Original source