The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Database Design: One field relates to one of 3 tables?

Hi All:

Wanted to hear what your reccomendations are for databse fields that may need to refer to (be a foreign key to) more than one table.


Table: Accounting Transaction Master

where PhysicalDocumentId refers to the Primary Key of one of the following tables:
1. SalesInvoice (SalesInvoice.SalesInvoiceID)
2. GoodsInward (GoodsInward.GoodsInwardID)
3. CreditNote
4. etc...

I don't like the fact that the database can not enfore relational integrity in such cases.

What are your reccomendations?
vitriol Send private email
Friday, January 05, 2007
Why not have 3 foreign keys, nullable, one for each
possibility, and use triggers to ensure that
only one is set?
DEBEDb Send private email
Friday, January 05, 2007
One way is to have a PhysicalDocument table and sub-type tables for Invoices, etc.
Mike S Send private email
Friday, January 05, 2007
There's an easier solution. Reverse the primary-foreign key relationship, and reverse the direction of the corresponding joins in your select statements.

Specifically, the SalesInvoice table contains a foreign key linking directly to a TransactionMaster record. If you wanted to find all "documents" generated by a transaction, you simply join all of the tables together and look for a common TransactionMasterID in each table.  That way, one transaction, per se, can have both a Sales Invoice and a Credit Note.
Friday, January 05, 2007
As a follow up, you could potentially have two Sales Invoices in one transaction, but simply requiring that the TransactionMasterID column be unique (within the SalesInvoice table), will solve that problem.
Friday, January 05, 2007
+1 to vitriol's solution.

Instead of a trigger to ensure only one is set, I'd use a check constraint.  I find they are easier to administer than triggers, and probably a bit more performant.
OneMist8k Send private email
Friday, January 05, 2007
I'd go DEBEDb's route with OneMist8k's check constraint if I needed to finish quickly (Method 1), but TheDavid's method (Method 2) would be preferred.

Method 1 lets you get rid of the PhysicalDocumentType column. It will make your joins cleaner since you won't need to lookup a table name with the PhysicalDocumentType value to prepare the join.

Think about maintenance in 6 months. You'll ask yourself:
"Does PhysicalDocumentId point to the SalesInvoice table? Well, just a minute. I'll need to check the PhysicalDocumentType data value"
or (preferred)
"SalesInvoiceID relates to SalesInvoice.SalesInvoiceID"

Another downside is you will have as many foreign key columns as you have tables.

Method 2 is a cleaner solution in that you can have as many ancillary tables as you like without affecting the TransactionMaster table.
Mike Saeger Send private email
Friday, January 05, 2007
The relationship between the ATM table and the others looks to be 1:1 in each case. If it's 1:n the ATM should be the master, if it's n:1 then a complex solution or three separate table (my prefered method) is the solution.

However it it's 1:1 then the accounting details should be held in each of the three tables, not in one or three separate ones, I think. You can place a UNION ALL view over the relevant columns of the three tables if that would help.
David Aldridge Send private email
Friday, January 05, 2007
Thanks for your tips.

Your input has definitley made me think of things I might have ignored.

The relationships should be *..1 between AccountingTransactions and PhysicalDocuments.
i.e. One PhysicalDocument (i.e. an Invoice) can result in multiple AccountingTransactions (example: One for sales another for tax)

Further, I really hate my solution -- Its a maintainence nightmare (as correctly suggested by Mike Saeger). I was recently asked to maintain another system where I had used a similar pattern 4 yrs ago -- and it was very difficult to grok the DB all over again.

For 1..* relationships your suggestion holds, however this should clearly be *..1. Though I like the approach.

@David Aldridge:
The relationship is strictly *..1, however in practice its usually going to be 1..1 hence your suggestion may be a good approach too, but that would mean sacrificing some flexibility.

@Mike S:
Having a single PhysicalDocument Table does not solve the problem as it just relocates the PhysicalDocumentType to the PhysicalDocument table, as you yet need to have relationships to the individual PhysicalDocumentTable

The types of PhysicalDocuments are likely to increase as the project gets implemented further, so cant have different columns for each type as that would mean significant change to the Data Structure over time.
vitriol Send private email
Saturday, January 06, 2007
One solution that I saw in an Access-based accounting system looked interesting.

The APInvoice table had 2 fields: TransactionType and TransactionID. TransactionType was a simple int with a default value of 1. TransactionID was a simple autonumber primary key. ARInvoice was similar, except that the TransactionType had a default value of 2. Other 'transaction table' followed this general principle

The GeneralLedger table had two fields: TransactionType and TransactionID. When 'posting' to the GL, each transaction would include both Type and ID.

I don't remember what relationships were created, but I do remember that there was another table that listed all the transaction type IDs, as well as the physical and descriptive names of all the transaction tables.

I don't know what that is like to design, build, and maintain, but it was sure easy to report against.
Ron Porter Send private email
Saturday, January 06, 2007

What including the TransactionType in the AP as well as AR and the GL does is it allows the DB to enfore refrential integrity by using a compound Foreign Key on the fields (TransactionType + TransactionID) between GL and AP and AR.

This is a extension of my original post and definitely helps remove the absence of database enforced relational integrity.

However, I'm yet trying to reach (what seems like the holy grail) of not having the FROM part of my SQL query depend on the value of TransactionType

vitriol Send private email
Saturday, January 06, 2007

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz