September 19, 2003 at 4:19 am
I am re-writing a database that will have to handle several different payment types.
The existing database is written in Visual FoxPro and has all payments made in 3 separate tables, one for cash, credit card, Postal orders etc, another table for direct debit payments, and another for Standing Orders.
I decided that as every payment requires different types of information to be stored about it, I would normalise the tables to reduce the amount of Nulls. So i therefore have one table for direct debits, one for credit cards, one for cash, one for standing orders etc..
However each payment type has information that is common to every other type of payment, and can't be NULL (e.g amount, payment date, payment banked by, Financial Codes etc.) I can't decide whether to have one table that holds all these common details, or whether to put all these columns into the relevant tables and to use a view using a UNION query when I need to look at all financial details (which will be often). Would having an extra table just be creating an extra 1:1 relationship on all these tables that isn't necessary?
September 19, 2003 at 4:52 am
I comply with what you have done, keeping three tables.
He who knows others is learned but the wise one is one who knows himself.
He who knows others is learned but the wise one is one who knows himself.
September 20, 2003 at 2:27 pm
I would need to know more about the application to be sure, but I have worked on (too) many financial systems, and IMHO you should have one Payments tables and create other tables to remove any partial dependencies. Designs requiring UNION are usually incorrect.
If you are referring to information like credit card number and expiration date, please realize that these data are attributes of the credit card, not of the payment, and so should be elsewhere in the schema (with the sensitive data encrypted).
It is often possible for a payment to be made with more than one instrument, so having separate payment tables may also prevent you from easily storing that information.
--Jonathan
--Jonathan
September 22, 2003 at 2:31 am
Having one payments table would work fine for one-off payments such as credit card, cash etc, as I could record the amounts and then have a 'transaction key' to link to external tables holding the relevant data that needs to be stored. The problem I have is with rolling payments (i.e monthly), such as direct debit, rolling credit card. I have a table holding the direct debit details (i.e bank account number etc) and this has it's own primary key. To be able to link a payment back to this table I would have to put this primary key into the 'payments' table, which means having a key that will definitely be NULL for all payments that are not direct debits.
Should I have 2 payments tables? One for one-off payments and another for rolling ones??
September 22, 2003 at 8:50 am
quote:
Having one payments table would work fine for one-off payments such as credit card, cash etc, as I could record the amounts and then have a 'transaction key' to link to external tables holding the relevant data that needs to be stored.
I think it should be the opposite: have the payment ID as a foreign key in the child table(s).
quote:
The problem I have is with rolling payments (i.e monthly), such as direct debit, rolling credit card. I have a table holding the direct debit details (i.e bank account number etc) and this has it's own primary key. To be able to link a payment back to this table I would have to put this primary key into the 'payments' table, which means having a key that will definitely be NULL for all payments that are not direct debits.Should I have 2 payments tables? One for one-off payments and another for rolling ones??
That is similar to the credit card table I hypothesized. Why not use NULL values for this?
I avoid making columns nullable, but in a case like this I would because then one could use DRI and ISNULL (or COALESCE) rather than CASE expressions. An application like this typically uses the recurring payment templates table (or credit cards table) in the INSERT process, and not in the reporting process except for verification reports.
--Jonathan
--Jonathan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply