0

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 :).
Community
  • 1
  • 1
PastorPL
  • 968
  • 3
  • 17
  • 36
  • *"One invoice can match to many transactions, and one transaction can match many invoices."* Are you sure about that? When I order something from Amazon, I'm pretty sure that nobody else pays for it. (That is, my transaction appears only on my invoice, not on anybody else's.) – Mike Sherrill 'Cat Recall' Nov 24 '14 at 10:41
  • Yup, because all transactions/invoices are for the same user - us. So all invoices are invoices that we have made, and all transactions are transactions that come to our account. – PastorPL Nov 24 '14 at 10:45

1 Answers1

1

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.

JotaBe
  • 38,030
  • 8
  • 98
  • 117
  • Hmm I think that your answer is complete. But I will stay with my last idea with creating additional column and place there number that will be matching no. This way I will avoid additional table. Of course your solution is better normalized, but on the other hand mine is more obvious for me :). Nevertheless thanks! – PastorPL Nov 25 '14 at 16:30
  • 1
    As you're not using the "normalized version" you should create indexes on those number columns. On the contrary whenever you want to query those columns or join the tables on that columns, or filter by that columns, the system will have to do a full table scan. If you used the "normalized solution" the index would already exist on the intermediate table. It's not that your solution is bad at all, specially if it makes more sense for you, but take this *tiny detail* into account. – JotaBe Nov 25 '14 at 16:46
  • Good point, thanks. I will keep mind on this trip, because it's definitely make sense. – PastorPL Nov 26 '14 at 08:06