CODEWITHSUNDEEP
sqlsql-serverdatabase-designrelational-databasedatabase-schema
PastorPL
PastorPL

Reputation: 1024

Schema Design for Invoices and Transaction - "mystery" relation

I've got question regarding problem I've faced in my project. Problem is about model of DB and to be more precise: how I can model this kind of relation (which at first I thought it will be simple M:M,but after some thoughts it is not so obvious ;) ). I have some ideas but maybe someone will have something better:)

My question will be regarding following problem:

I've got 2 tables. One has got e.g. invoices, second one - transactions. One invoice can match to many transactions, and one transaction can match many invoices. I want to allow user to do some matching so he can pick invoices (one or more), pick transactions (one or more) and click "match". But after clicking "Match", all used record (inovoices and transactions) cannot be used any more. My ideas are:

  1. Used standard association table but with 3 columns: idIncoive, IdTransaction and unique id for matching info. And put unique constraint to... and here I've stuck because I don't know how to force that one invoice can have multiple transactinos but within one matching (and here are I'm missing information about matching I guess)

  2. I thought that I will add one extra columnt to invoices and transactions table. And when user do matching, I will put unique numer (but unique for matching, not unique for tables sens of) to these columns. And this number, will be matching number, which I can easliy chcek witch transaction/invoice was checked and with what.

And this is idea which is my best for now. But as I said - I'm not sure that this is the best in all so here is my ask to get your opinions about this too.

One more thing: there is a similar (at first glance) question here but problem stated there is:

  1. More complicated because user need to do some specific calculations and store information regarding projects
  2. Not answered yet :).

Upvotes: 0

Views: 631

Answers (1)

JotaBe
JotaBe

Reputation: 39004

If a transaction can match many invoices, and an invoice can match many transactions, there is no direct way to "lock" the rows so that they cannot be used any more once already used.

For example if you match 2 invoices to 3 transactions, each transaction will refer to 2 invoices, and each invoice will refer to 3 transactions.

There is no direct way to tell SQL Server: "Man, we're done, don't allow to match these rows any more!". And that's becasue you cannot define a rule for it.

Once you know that there is no possibility to create this rule the easiest way to solve your problem is to implemente it using your application logic, and with this DB design:

  • add a Matched bit column on each table
  • and a classical M-N intermediate table that has a primary key composed of the primary keys of both invoices and transactions

And then, the rest of the job must be done from your application logic:

  • create the entries on the intermediate table
  • set the Matchedfield to 1
  • don't offered the users to match invoices or transactions that are already matched (by using this flag column)

This is a simple, easy to implemente and clear solution. Follow the KISS principle.

Creating a number for each matching group, i.e. a third identity column on the intermediate table, can help you when reverting the change or querying the matching group, but it's not necessary.

Upvotes: 1

Related Questions