Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
b74smith
Starting Member
3 Posts |
 Posted - 2005-06-02 : 11:57:18
|
| This is a datamodeling question - hope this is the right forum:Is there a standard way of implementing subclassing in a datamodel?E.g if I have a table BankAccount which holds general account info like ClientID, CurrencyID, Balance etc but also have several more specialized accounts like SavingAccount, CurrentAccount, TermDepositAccount etc.The first approach I tried was to have a one to one mapping between base table (account) and derived table, linking these via a FK in the derived table. However the problem with this is that in order to discover the sub-class of a given BankAccount you'd have to iterate (or join) all the possible derived tables, so if there are a lot then you get complex and low performace code.So from this it is tempting to consider breaking relational Db principals and have an ID field in the parent/base table that points to 1 of many rows across several different tables. Of course this is not desirable for various reasons (e.g you can't enforce any relational integritity)I have a few other ideas but am getting the sense that there might be some easy way to do this that I've overlooked...it seems like it should be a fairly common scenario?Any suggestions or comments will be much appreciated.Regards,Brendon. |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-06-02 : 13:41:22
|
quote:
The first approach I tried was to have a one to one mapping between base table (account) and derived table, linking these via a FK in the derived table. However the problem with this is that in order to discover the sub-class of a given BankAccount you'd have to iterate (or join) all the possible derived tables, so if there are a lot then you get complex and low performace code.
That's the way I would go and the standard way to handle this IMHO. You would LEFT OUTER JOIN to all subtables if needed; but the point is, you should never need to do this. The two scenerios you typically encounter are:a) You need to return a recordset of all common attributes amongst the accounts -- therefore, you ignore the subtables and just select from the master "Account" table.orb) You need specific information from one the subtables. If so, you just select from your Account table and join to the appopriate subtable. A view per type is perfect for this scenerio as well to keep things simple. It's also OK to have a "type" column in the Accounts tabel to indicate which type of Account it is.- Jeff |
 |
|
|
|
|
|
|
|
Data Model inheritance and subclassing