Hi,
I'm not sure that this is the best forum, as it is more of a database
design question, but I hope that someone can help me.
I am creating a billing system. Each customer can pay via several methods,
and over time methods of payment might be deleted or added. So, I use the
one-to-one relationship to create tables like this:
_payments_ _creditCard_ _check_
paymentID paymentID paymentID
invoiceID cardNum checkNum
amount expiryDate ident
cardType
The payment types that I have are not exactly like this, as we have some
additional internal methods of payment. I'm somewhat new to relational
database design, so forgive me for asking a stupid question.
To select a payment and it's details, i would do something like this:
SELECT payments.invoiceID,payments.amount, creditCard.*,check.* FROM
payments,creditCard,check WHERE payments.paymentID = creditCard.paymentID
OR payments.paymentID = check.paymentID
My first question is if there's a better way to write this query. And the
second is how I should best store what KIND of payment it is for display
in my reports and data entry. Also, I don't want to change the SELECT
statement when I add a new payment type. Should I add a field to payments
named "paymentType" that's a foreign key to a table of paymentTypes, that
then has a CHAR that gives the table name? I feel like this might violate
normalization rules, but on the other hand I wonder if this could optimize
the SELECT query to avoid the costly union. I'm not really sure what the
best way to do this is, but I think it must be a common problem.
In summary: your advice would be much appreciated. If I should have found
this myself, sorry, my searches so far haven't turned up anything. If you
can tell me where to look I'd appreciate that too.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
