Options table

[sabavno]

Hi,

I have been strugling to desing the following.
I have a table that keeps the asset information.
Example:

RecID
AssetTypeID
DateCreated


Each asset type can have different options. So I need my Options table. If my RecID wasn't the autonumeric field, I could create the Options table and have one-to-many relationship joined on RecID field. But, I am not building the database from the scratch, I am just modifying it, so I don't want change the logic behind RecID field.

Is there any other ways to have my options stored in another table and relate them to each asset record.

Please help

Thanks.

 

[KenReay]

Hi

You say that options are dependant on Asset Type, but then go on to say you want to relate options to AssetId. Suerly you should be relating them to AssetTypeID ?

I do not feel that I have enough information to offer a definitive suggestion, my assuming you can have 'n' options associated with an asset type, I would be thinking in terms of having a table

AssetTypeId )Primary
OptionTypeID )Key
OptionValue

probably with another table of OptionTypes so:

OptionTypeId
OptionTypeDescription

Hope this helps



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[sabavno]

Thanks a lot for your suggestions,

However, let me go on on this topic.

You suggest implementing the following:
AssetTypeId )Primary
OptionTypeID )Key
OptionValue

However, the asset type may have several options. So the asset record would have to have several OptionTypeID?

Please suggest further.

 

[KenReay]

Hi

Yes, that is why I suggested that approach, I would see the Asset Table being split into two tables, one would hold the 'fixed' information relating to the asset, so:

AssetId
AssetTypeId
AssetDescription
DatePurchased
...etc

the Options would be held in a seperate table so

AssetId
OptionTypeId
..etc

I am not clear on just what you mean by 'option' in this context so I cannot quote examples

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK