[Resolved] Database design thought

Megha

I want to design the database where the primary entity is an asset. Each asset owns a particular set of characteristics. Sometimes two assets can share the same set of attributes, sometimes they have mutually exclusive attributes and sometimes some of the attributes overlap.

Attributes need to be defined dynamically which means with every asset we should be able to define its attributes if not existing in the system.

If I put all assets in a single table, I have to keep adding the columns as and when a new attribute for a new asset comes into the system. For existing assets this can be waste of space as well as time consuming while querying the database.

Another approach which I think of is: If I keep one asset table with a basic set of attributes and another table where I can define two columns: attribute name and attribute value for each asset. This will be something like a table with (asset_id, attrib_name, attrib_value) where we can add incoming new attributes and their values as per the asset. But the problem here is that if I add a new attribute here and later another asset gets defined with the same attribute then we have to add altogether a new row into the table. Also the column value checks need to be enforced explicitly.

Do you have anyother design strategy which can better suit my problem?

I query or update the asset information frequently and I am using MySQL as my database solution.

Thank you very much
Sincerely
-megha

bastien

the second approach is the best one...each asset should have its own unique id and that is what is referenced in the second table's asset_if column

house_1 (id 1)
house_2 (id 2)

attributes_table
(asset_id, attribute_id, attribute_value)
1,2,3
2,2,3

while they reference the same attributes the asset_id will be distinct

hth

drawmack

three tables:

1) assets
        asset_id
        asset_name
        ....
2) attributes
        attrib_id
        attrib_name
        ....
3) assets to attributes
        ata_id
        ata_value
        ......

Megha

Data Requirements:

Asset is a basic entity in the system.
Each asset has a set of attributes associated with it.
Two or more assets can share same set of attributes OR an asset can have altogether a unique set of attributes.
Attributes are not predefined for all assets. For most of the assets, we can think of a basic set of attributes that depends on the asset type. After that each individual asset can add a different attribute for itself.

Database Design:

I want to put asset data into a single table. But then every asset can have different attribute names with different data types - I cannot put this information in the same table.

I am thinking of making one asset table with basic set of attributes along with the asset type information.

Asset (asset_id, asset_type)

Next I can make several tables depending on the number of asset types (in the system) each with the basic set of attributes of related asset type.

Asset_Type1 (asset_id, attribute11, attribute12, ...)
Asset_Type2 (asset_id, attribute21, attribute22, ...)
.....

Now to accommodate the attributes those are defined dynamically as and when a new asset gets defined: I think of making tables for each possible data type like:

Asset_Dynamic_Integer (asset_id, attribute_name, attribute_val)
Asset_Dynamic_Varchar (asset_id, attribute_name, attribute_val)
.....

This will help me to enforce column constraint (if any) for attribute values.

I am not sure of this desin strategy. I will appreciate if you can go through it once and give me some insight into the design I have in mind or any other better idea.

Thank You Very Much!!!
Megha

drawmack

my design meets every one of your requirements and it's in fifth normal form so I don't understand why you would want to do it differently.

Megha

Originally posted by drawmack
three tables:

1) assets
        asset_id
        asset_name
        ....
2) attributes
        attrib_id
        attrib_name
        ....
3) assets to attributes
        ata_id
        ata_value
        ......

[/B]

Can you please elaborate more on this design. I do not understand how we are going to map assets to attributes in the third table?

Second table has attribute names, what about attribute data types - are you thinking to put attribute type information in this table?

All attributes may have different data types, how can we put different data type values under the single column, ata_value of third table?

Please explain further - I might be missing on some point

Thanks
Megha

drawmack

my bad:

the third table should look like this

3) assets to attributes
    ata_id
    asset_id
    attrib_id
    ata_value
    .....

that should answer the first question. You use asset_id and attrib_id in conjunction to link the attributes to the fields.

The dots were to leave some room for flexibility. You could create a field in the attributes table called type. I'd make it an enum. Then you can create various columns in the assets to attributes table one for each data type, though this breaks normalization so instead I would create seperate tables like so:

x) attrib_val_<type>
    av_id
    ata_id
    ata_value

you'll have to do a bit of planning to limit the number of attrib_Val_<type> fields.

things like you can store tinyint, int and bigint all in the same table if you enforce size requirements with software. same with char, varchar and text, etc.

basically create one attrib_val table for the largest of each type and you'll be covered.

Megha

Gotcha, Thanks a lot for the elaboration!