MS Visio Category relationships to SQL Server

Question

I'm using MS Visio to model a database and part of the model contains Transaction categories - the parent table has a transactionId, timestamp, amount, and transactionType. There are three child tables - cheque, bank transfer, and credit card, all related to the parent by transactionId.

Is there a specific way this kind of relationship is implemented in SQL Server, or is it just a conceptual model leaving the implementation up to me? If the latter, why have a transactionType column in the parent table if the tables are all related with transactionId - is it just to narrow my queries? That is, if a row in the parent table specifies "cheque" as the transactionType I know that I only have to query/join the cheque child table?

It just occurred to me - is this just an ISA hierarchy, in which case I'd create three distinct tables each containing the columns identified in the ISA parent entity?

Answer 1

The transactionType in the parent table is useful if you'd like to query over all transactions, for example to sum the amounts per transaction type:

select transactionType, sum(amount)
from transactions 
group by transactionType

Without the column, you could still do that by querying on the child tables:

select 
    case when c.transactionId is not null then 'CHEQUE'
         when cc.transactionId is not null then 'CREDIT CARD'
         ...
    end
,   sum(amount)
from transactions t
left join cheque c on t.transactionId = c.transactionId
left join creditcard cc on t.transactionId = cc.transactionId
...
group by 
    case when c.transactionId is not null then 'CHEQUE'
         when cc.transactionId is not null then 'CREDIT CARD'
         ...
    end

As you can see, that's much harder, and requires extending the query for each type of transaction you add.

Answer 2

This is essentially multiple-table inheritance, although you can model it in the domain as a simple reference relationship if you want.

There are many good reasons to have the selector field/property. The obvious one is so an application or service gets a hint as to how to load the details, so it doesn't have to load every conceivable row from every conceivable table (try this when you have 20 different types of transactions).

Another reason is that much of the time the end user doesn't necessarily need to know the details of a transaction, but does need to know the type. If you're looking at an A/R report from some financial or billing system, most of the time all you need to know for a basic report is the previous balance, amount, subsequent balance, and the transaction type. Without that information, it's very hard to read. The ledger doesn't necessarily show the details for every transaction, and some systems may not even track the details at all.

The most common alternative to this type of model is a single table with a whole bunch of nullable columns for each different transaction type. Although I personally despise this model, it's a requirement for many Object-Relational Mappers that only support single-table inheritance. That's the only other way you'd want (or not want) to model this in a database.