What is the proper way to store "metadata" in a relational database?
I have a table called assets
where an asset can be owned by a user, team, or department, and possibly several of them. My problem is that assets are highly variable and may have properties associated with them that are different for each one.
ex. These can 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 can go on from hundreds to thousands of different types of assets.
How can I store this data type in a normalized way that is easy to query without changing my tables every time a new asset type is added? Some of the fields are also present in all assets, for example, in value.
So far, I believe 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 dont know where to put the data that changes
source to share
When I've run into this in the past, the "best" answer always ends up depending on how much processing I want to do in the database, and how much in the client code.
For what it's worth, often the approach that has worked best for me in the past has been to end up with one table per optional attribute (in particular, not one table per entity type). So in your examples above
assets (as per your example)
asset_types (as per you example)
division_assets (as per your example)
user_assets (as per your example)
colours
asset_id, colour
weights
asset_id, weight
serial_numbers
asset_id, serial_number
Of course, depending on the trade-offs you need to make, this might be a poor choice for you. Personally, I like to keep the schema as clear as possible, including data types and constraints, so I have no drama about changing tables the next time a new attribute appears.
source to share
You can add another table for asset_metadata p>
asset_metadata
asset_metadata_id,asset_id,metadata_name,metadata_value
if you want to normalize and classify metadata, normalize it like this:
asset_metadata
asset_metadata_id,asset_id,metadata_name_id,metadata_value
metadata_name
metadata_name_id,metadata_name_text
source to share
I would recommend putting general attributes such as cost in a regular column. Then add another column where you put the serialized collection of all the other attributes of the variable attribute.
CREATE TABLE assets (
asset_id INT AUTO_INCREMENT PRIMARY KEY,
cost NUMERIC(9,2),
purchase_date DATE,
variables TEXT
);
You can serialize the collection as JSON or XML or whatever. Use what your application code can handle most easily.
INSERT INTO assets VALUES (123, 49.95, CURDATE(), 'color: black; vendor: Acme Co.');
The advantage is that you can add new attributes to the text block at any time. The downside is that you cannot read or write a single attribute, you have to treat the entire collection as a chunk.
But you can index individual attributes to make them searchable. You need to create a new table for each attribute you want to search for (but it might be a small subset of all attributes):
CREATE TABLE asset_color (
asset_id INT NOT NULL,
color VARCHAR(10),
PRIMARY KEY (asset_id, color),
KEY(color)
);
Not all assets are recorded in this table, only those assets that have a color.
Then you can do an indexed search for all assets that have a color attribute:
SELECT assets.*
FROM assets INNER JOIN asset_color USING (asset_id);
You can also do an indexed search limited to assets with a color attribute and the color is black:
SELECT assets.*
FROM assets INNER JOIN asset_color USING (asset_id)
WHERE color = 'black';
It is not possible to create a normalized database that allows variable attributes. All normal forms require the table to be a relation first. And a relationship, by definition, must have a fixed set of attributes.
Other people recommend the EAV table, but the "value" column in EAV does not match the definition of a relational column with a type (other consequences of this are that constraints do not work in the EAV table). Therefore, the EAV table is not a relation and cannot satisfy any normal form.
source to share
You can create two new tables:
1) Define multiple attributes of assets in the following table (as many as an asset can have)
ASSET_ID
asset_attribute
asset_value
2) asset attribute table
attribute_id
asset_attribute
The logic would be that the asset_attributes would need to be defined in the asset_attribute table first, and then it can be used (linked / tagged) with any active (like foreign key, from the dropdown in the UI) and the corresponding entered value.
Hope it helps.
source to share