How would I build this database schema?

acidbox

I am building an app that tracks assets. An asset can be anything like a car, house, piece of jewlery, etc...

I have to store very specific information about each type of asset, so for example, if it was a car, I'd need to store a tag number, vin number, make, model year, etc... If it was a house or piece of property, I'd have to store the physcial address, sqfeet, acreage, phone number, etc..

Also, for all assets, regardless of the type, there can be contacts (people) and their address/contact information assocated with it in the DB for our records.

I have an "asset" table that stores the name of the asset. However I am having trouble figuring out how I would store the custom data for each type of asset. All of the data needs to be searchable somehow, so I want to make it flexible, yet still fast and managable.

Any ideas about how I could do this so that I don't box myself in for the future?

laserlight

You can simulate an object oriented asset class hierarchy.

So you have an Asset table that stores the asset id and any other data common to all assets. Then you have Car table for cars, Property table for property, etc. These tables would share the same asset id with the corresponding entry in the Asset table.

You would also have a Contact table (or Owner or Person table, use the name that fits best) to store the contacts of people who own assets. If your model says that each asset belongs to exactly one person, then you can store the contact id in the Asset table. If your model says that each asset can belong to multiple people, then you should have another table to map assets and contacts, since there would be a many to many relationship (assuming people can own multiple assets, which is a reasonable assumption, methinks).

Roger_Ramjet

laserlight wrote:
You can simulate an object oriented asset class hierarchy.

So you have an Asset table that stores the asset id and any other data common to all assets. Then you have Car table for cars, Property table for property, etc. These tables would share the same asset id with the corresponding entry in the Asset table.

You would also have a Contact table (or Owner or Person table, use the name that fits best) to store the contacts of people who own assets. If your model says that each asset belongs to exactly one person, then you can store the contact id in the Asset table. If your model says that each asset can belong to multiple people, then you should have another table to map assets and contacts, since there would be a many to many relationship (assuming people can own multiple assets, which is a reasonable assumption, methinks).

Which leeds us nicely into Object Relational Mapping and POG