Track different kinds of assets

[sabavno]

Hi

I know this isn't the right forum, but it is the best one where I got help so many times!

Just a simple question regarding the database design.

To keep different kinds of assets with different kinds of configuraions, should I create a separate table for each kind of asset or could I keep them all in one?

Appreciate your help

Thanks

 

[jjonesal]

More than likely - in a different table

Need a little more info:

Does an asset have multiple configurations?
What does a "configuration" for an asset consist of?
Can you provide an example with data? J. Jones
jjones@cybrtyme.com

 

[sabavno]

Well,

I have a blackberry asset and windows based terminals

The only common configurations fields for them would be serial number and modelID.

For blackberry I do store Pager Number, Roaming, etc.
For WBT I look up the available configuration such System Name, System Description,etc. in the configuraion table.

 

[jjonesal]

If each asset has one and only one configuration,

then I think you would include these configuration properties, like System name, Description, PageNumber, Roaming, etc. in fields in the Asset table.

For a WBT, you would just leave the fields like PagerNumber, Roaming, etc. blank (because they are not applicable)

If you wanted to track how these configurations changed over time, (for instance you wanted to see that at one point a particular asset was configured one way and later was configured differently), then you'd need to separate the data so you could keep a history table of configurations.

Hope this helps... J. Jones
jjones@cybrtyme.com

 

[sabavno]

I have another confusion.

I have a configuration table for WBT

ModelID SelectCode Options
1 2 3
1 3 4
1 4 5
2 4 3
2 6 3


As you see my ModelID has duplicates. Should I create another table to normalize this one?

 

[jjonesal]

Yes. J. Jones
jjones@cybrtyme.com

 

[sabavno]

How?

 

[jjonesal]

Can a Select code for specifc model have multiple options?

ie. could model 1, select code 2, have another option, say 4?

ModelID SelectCode Options
1 2 3
1 3 4
1 4 5
2 4 3
2 6 3

J. Jones
jjones@cybrtyme.com

 

[sabavno]

Yes, it can

 

[jjonesal]

Based on what I've gleaned so far...

Asset Table - contains all assets
AssetID (PK)
Asset Name
SerialNo
ModelNo
PagerNo
Roaming

Model table - contains all available models
ModelID (PK)
and other data specific to the model

SelectCode table - contains all available select codes
SelectCodeID (PK)
and other data specific to the selectcode

Option table - contains all available options
OptionID (PK)
OptionName
other data specific to the option

AvailableConfigurations - contains the available configs for each model (I'm assuming these vary)
ModelID (PK)
SelectCodeID (PK)
OptionID (PK)

SelectedConfigurations - contains the selected configuration for each asset. You would choose these by picking from valid entries in the available configuration table)
AssetID (PK)
SelectCodeID (PK)
OptionID (PK)



J. Jones
jjones@cybrtyme.com

 

[sabavno]

Can you explain me the purpose of having the last tables?

 

[jjonesal]

For each model, I was guessing that only certain configurations would be allowable. Perhaps a model might not support all options. So, you could have an AvailableConfigurations table that would provide basically a picklist of all of the allowable selectcode/option combinations for each model.

Then, I'm assuming (I know I'm making some big jumps here) that each Asset would have an assigned configuration. It might not utilize all of the selectcode/options available (it could be a subset of the allowed combinations).

I think this design would provide you with the flexibility while not allowing you to assign an option to a model, if that option is not allowed for that model.

Does this make sense...? J. Jones
jjones@cybrtyme.com

 

[sabavno]

Yeah, it does
THank you very much for all your help