Hi folks,

I am working for a project in the domain of Rural Banking. This is Java project that prefers to deal with objects using entity beans rather than have SQL calls in code.

The domain requires the storage of asset information for farmers. Assets are of types: property, vehicles, produce on hand etc. The object model seems ok as there is a generic asset object subclassed by specific asses subclasses to represent each type of asset. My question is is it okay if the database reflects this hierarchy exactly and there is a genric asset table and multiple specific asset tables. The genenric asset table contains attributes that are common to all the specific assets e.g. name.

Is this good database design because it seems to me that two tables need to be accessed to access all the attributes relating to one asset object. Just having specific asset tables (property, vehicle etc.) with the common columns getting repeated in each table is not a denormalized design I think.
Or is it common practice to partition the table this way. There is also no requirement that only some of the columns need to be accessed mostly, thus making a case for the partitioning. you need to access both the tables each time you want an asset object.

Any thought on this welcome.
Thanks,
Santosh

It is quite common practice, but is not always the best approach. You have to consider the ways in which the data will be used. Presumably there will be some queries that work with all asset types at once? If so, your suggested design (just 1 table per asset type) would require a view based on UNIONs of all the tables.

If the total set of attributes for all the subtypes is not too large, it might be better to have a single table ASSET with all the common and specific attributes in it. Check constraints can be used to ensure that the appropriate attributes are populated according to asset type. You can also create a view for each asset type to facilitate queries and operations that deal with a particular asset type.