Composite key turns out to be not unique....trying to build a fix

OK...I am hoping this is a classic problem that everyone knows the answer to already. I have been building a mysql database (my first one) where the main purpose was to load line-item data from an invoice and related data from the matching remittance and reconcile the two. Basically, everything has been going along fine until I discovered a problem.

Details: I have thus far identified individual invoice line items with a client (to be billed) id, service date, and service type and matching that transaction against the remittance transaction with the same client ID, service date and service type. Unfortunately, there are times (I just discovered) when one client (ID) gets multiple instances of a particular service on the same day and thus my invoices are not unique based on the three components I just mentioned.

There is another piece of info on the invoice (service time) that could be used to make invoice items unique, but the remittance does not include service times (thus I cannot match directly against it using service time). Likewise, the remittance has another piece of info (claims ref number) that uniquely identifies remittance items. But of course, the claims ref number is not on the invoice.

Is there some way to use an intermediate table perhaps that can bridge this gap? Any help, answers or helpful links would be most appreciated. 开发者_如何学JAVA Thanks in advance.

This is perhaps more a business problem then a technical one-- it sounds like there is in fact no reliable way to match up remittances and invoices, unless something like matching on the dollar amount works. If you use an artificial key on the invoice you kind of solve the technical problem but not the business one.

If you can't change the business process at all and there is no technical way to match remittances and invoices, you might be forced to treat all invoices for a customer/service date/service type as a unit; make each invoice a part of that unit, and then group all the remittances and all the invoices that match that unit together.

You can make life easy on yourself and create an Invoice ID and remove the composite key all together.

Any type of fix is going to have an impact on the calling code, as increasing the field count on the composite key implies that this new field needs to be supplied, so I suggest just creating an invoice ID.

Many IT professionals that work with RDBMS will suggest to never use natral keys. Always use a surrogate key (like an auto-increment column)

I agree with @antlersoft (+1), this sounds mostly like a business problem: how to “match up” items within two separate sets of data that cannot be clearly and cleanly matched up with the data provided.

If the “powers that be” (aka your manager/supervisor/project owner) cannot or will not make this decision, and if you have to do something, based on the information provided I’d recommend matching same-day items like so:

• lowest invoice-item service time with lowest remittance claims ref number
• next-lowest invoice item service time with next-lowest remittance claims ref number
• etc.

(So when you have such multiple-per-day items, do you always have the same number of invoice items and remittances? Or is that going to be your next hurdle?)

Once you know how to implement “matching up” items, you then have to implement it by storing the data that supports/defines the assocaition within the database. Assuming tables InvoiceItem and Remittance, you could add (and populate) ServiceTime in the Remittance table, or ClaimsRefNumber in the InvoiceItem table (the latter seems more sensible to me). Alternatively, as most people suggest, you could add a surrogate key to either (or both) tables, and store one’s surrogate key in the other’s table. (Again, I’d store, say, RemittanceId in table InvoiceItem, as presumably you couldn’t have a Remittance without an InvoiceItem – but it depends strongly upon your buseinss logic.)