0

I'm using MySQL and my tables are stored using innoDB for the purpose of referential integrity.

QUESTION

I'm building a sales order system. My invoice table has tracking_code column and COD_place column

Customer deals with me using either one of these methods: shipping or COD (cash on delivery/meeting him up personally). Each parcel that is shipped has a tracking code, while for COD, there has to be a place to do that. Thus the column tracking_code and COD_place.

The problem is, if shipping is preferred, then the COD_place column should be NULL or left empty; otherwise, tracking_code must be NULL.

It's not elegant. What is the best way to represent this? Can you create a generalized entity, method in MySQL?

I really can't think of any elegant solution so I turned to your help here. Thank you in advance!

Chin-yang Lee
  • 13
  • 4
  • An option is to store the details in separate tables. For example invoice table can have Enum(TRACKING, COD), reference_id. 'Tracking' table will have id, tracking_code. 'COD' table will have id, cod_place. both Tracking table and COD table will be related to Invoice table via the reference_id. Having said that, if you are going to heavily query the Invoice table frequently, perhaps your original design is better (in spite of null fields) because it is going to avoid joins which may become heavy after the tables have millions of records. – TJ- Jan 06 '13 at 09:38
  • yeah, thanks man. I don't like too many NULLs. But I don't like reference_id in the invoice in that tracking and COD can have the same id (auto increment preferred over alphanum) value, although i can determine which table to join from the Enum column. But that will be done using PHP in fact I want it to be internal. So i guess I'll stick back to original design. Thaaanks! – Chin-yang Lee Jan 06 '13 at 10:17
  • What you need is a CHECK constraint. Unfortunately, these are not supported in MySQL. See here http://stackoverflow.com/questions/2115497/check-constraint-in-mysql-is-not-working and here http://stackoverflow.com/questions/5807231/mysql-check-constraint-alternative for approach using triggers. – Lord Peter Jan 06 '13 at 10:54

1 Answers1

0

You should introduce an additional column to specify which type of transaction is requested. Do not try to implicitly assume this based on the other information.

It is acceptable to have fields for redundant information within a table (using NULL).

Alternatively you could have an additional table for each type of transaction, with only the relevant fields necessary for that type of transaction. This has the added advantage of being able to represent those processes distinctly without interference from the others.

For example, you might have business logic which is supposed to handle COD transactions, which includes a state of "Driver collected payment" whereas your shipping transactions might have a column to handle "Payment pre-authorised transaction ID". There's no need for COD logic to care about shipping transactions, and vice-versa.

With an additional transaction_type field on the Invoice, you know which table to join to, in order to collect the additional information. If you add a 3rd type in future, you're not going to end up with an ever-expanding Invoice table.

Steve Mayne
  • 22,285
  • 4
  • 49
  • 49
  • With the additional field on the invoice I know which table to join to. However determining which table to join to is done explicitly using PHP right? I mean, use PHP to get the value of transaction_type and then JOIN with the corresponding table? – Chin-yang Lee Jan 06 '13 at 10:16
  • Yes, I'd do that @Chin-yangLee. You could feasibly do a UNION of both tables in MySQL, but that almost defeats the purpose of splitting out the different fields into different tables. You can tell which is which by just looking at the transaction_type field (or whatever you call it). – Steve Mayne Jan 06 '13 at 10:23