Hope this isn't a repost -
mysql,query,sql ---------- Forwarded message ---------- 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