spot7.org logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML Categories

Schema Design for Invoices and Transaction - "mystery" relation


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.


Categories : SQL

Related to : Schema Design for Invoices and Transaction - "mystery" relation
Master and slave design pattern work with transaction
You could use like below public function query($query) { if (strpos($query,'select') == 0) { $this->dbh = $this->dbhMaster; } else { $this->dbh = $this->dbhSlave; } $this->stmt = $this->dbh->prepare($query); }

Categories : PHP
Cypher - Return nodes with their relation with a count contraint on the relation
Untested, but you should be able to unwind your rels collection and pull out the start and end nodes. Something like: MATCH (doc:document)-[contain]-(sentence)-[with]-(ng1:word)-[rel:relations]-(ng2:word) WITH doc, count(rel) as nbRels, collect(rel) as rels WHERE nbRels > 10 WITH doc, UNWIND(rels) as rel, RETURN doc, STARTNODE(rel), ENDNODE(rel) But you might just want to collect something d

Categories : Neo4j
Javascript mystery command
This is javascript bitwise left shift with assignment. Example of javascript bitwise operators usage: // helper function for displaing results var output = function(operator, result) { console.log(operator + " " + result); } // variables var a = 5; var b = 13; // a | b - OR // if in any of the given numbers corresponding bit // is '1', then the result is '1' output('or', a|b); // 13 // a

Categories : Javascript
"Dr. sqlite3_bind_text" and "Mr. sqlite3_column_text" Mystery
If you want to store some array of bytes (not null terminated strings) you should use sqlite3_bind_blob instead of sqlite3_bind_text (which wants a text, i.e. a null-terminated string). If you know that you are handling null-terminated strings, use sqlite3_bind_text, perhaps with a cast to (const char*); in C casts don't change any data at runtime (except for numerical conversion to or from float

Categories : C++
Mystery with dynamic type & double dispatch
The issue is located at StructureMap's LambdaInstance<T> class inheritance. Usage of C# dynamic involves creation of polymorphic callsites, which ones does use runtime binders. Consider simplified inheritance tree for LambdaInstance class: class Program { private static LambdaInstance<object> Method(object obj) { throw new NotImplementedException(); } static

Categories : C#
Recently Add
Use REGEXP_REPLACE to replace variable number of characters with same number of constant characters
How to subtract a data with nano-second in a same group
listagg alternative in Oracle
How to join 3 tables to a single table in SQL
Postgresql: Trying to Understand the Information Schema Table Constraints Info for a NOT NULL Contstraint
Data Frame in R use like SQL, possibly using sqldf()
How can I group by a table by the date?
Query slows down in Conditional IF
Getting the daily sales report given the date
Retrieve rows with unique values in a column
case sensitive sql search in vb.net
Oracle multi dimensional query
Update column when datediff is greater then other column in the same table
add value from different tables and insert total value into another table
How to prevent transaction locking in sql server for all the connections?
Check the query efficiency
how can I run this select statement inside a select statement without any problems?
Add amount once on duplicate entries
SQL Server 2012 - Unique Priority Column
Query: employees who do not have the same name
Trigger to not allow a member to rent if unpaid balance exceeds $50
Anyway way to change this Oracle SQL statment to use regs?
If you set a field name as an Alias, can you utilize that name within SQL code?
Increment next month with dayOfTheMonth SQL sybase
Update Table Set From is not working as expected
How do I find one matching strings in two txt files
SQL help needed (oracle application express)
How to Roll Up Weekending Data in SQL
How do you call a variable (that is a list of values) in a conditional statement?
Oracle INSTR backward in Oracle SQL
© Copyright 2017 spot7.org Publishing Limited. All rights reserved.