Question: 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

Answer: 
I'd recommend putting the common attributes like cost in conventional column. Then add one more column in which you put a serialized collection of all the other variable asset attributes.

```
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 you want. Use whatever is most easily processed by your application code.

```
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 blob at any time. The disadvantage is that you can't read or write an individual attribute, you have to treat the whole collection as a lump.

But you can index individual attributes to make them searchable. You need to create a new table for each attribute you want to be searchable (but this could 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 every asset is 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 that have a color attribute, and the color is black:

```
SELECT assets.*
FROM assets INNER JOIN asset_color USING (asset_id)
WHERE color = 'black';

```

There is really no way to design a *normalized* database that permits variable attributes. All normal forms require first that the table be a relation. And a relation by definition must have a fixed set of attributes.

Other people are recommending an EAV table, but the "value" column in an EAV doesn't meet the definition of a relational column with a type (other consequences of this are that constraints don't work in an EAV table). Therefore an EAV table isn't a relation, and cannot satisfy any normal form either.
You can create two new tables:

1) Defining multiple asset attributes in the following table (as many as the asset may have)

asset\_id

asset\_attribute

asset\_value

2) asset\_attribute table

attribute\_id

asset\_attribute

The logic would be that asset\_attributes will need to be first defined in the asset\_attribute table and then it can be used (linked/tagged) with any asset (as a foreign key, from a drop down list on UI) and a proper value entered.

Hope this helps.