Examples of SQL transaction procedures for sales tracking or a financial database

Question

I am making a database for an accounting/sales type system similar to a car sales database and would like to make some transactions for the following real world actions:

1. salesman creates new product shipped onto floor (itempk, car make, year, price).
2. salesman changes price.  
3. salesman creates sale entry for product sold (salespk, itemforeignkey, price sold, salesman).  
4. salesman cancels item for removed product.  

5. salesman cancels sale for cancelled sale.

      The examples I have found online are too generic, like "this is a transaction", I would like something resembling what I am trying to do to understand it.

Anybody have some good similar or related sql examples I can look at to design these? Do people use transactions for sales databases? Or if you have done this kind of sql transaction before could you make an outline for how these could be made?

My (closed as not a real question) thread so far on stack overflow: Need example SQL transaction procedures for sales tracking or financial database

Latest update, user will send new inputs/ changes /and cancellations from a c# application. Application data:

• Products On Display (this is the parent node which has 3 child nodes)

• Sales(child node of Products On Display)

• Product Custom Features(child node of Products On Display)

• Product Price / current status (child node of Products On Display)

C# App will package that data into XML format and then execute some SQL stored procedures with transactions holding together the xml to table conversions into the SQL Tables designed with the same parent/child node structure using something like what is described by the answers to this related question on Stack Overflow: https://stackoverflow.com/q/2756773/613799

I wish there was a book on designing multi-user sales databases, and the stored procedure transactions that will be used by the related user apps from scratch app->xml->database. Please let me know if you know of a good one, or a chapter of a book.

Answer 1

Depending on the complexity of the database design you would probably need to use transacations to maintain data consistency between tables as you change data within the system.

Any examples would be very basic as every database schema would be different. This is where having a database or data architect comes in handy for a project.

Answer 2

You may want to take at this Car Dealership data model. This model will give you an idea what transactions are needed for your business requirements.

Answer 3

A classic financial example is bank transfer of funds:

BEGIN TRANSACTION;
  UPDATE Account SET amount=amount-200 WHERE account_number=1234;
  UPDATE Account SET amount=amount+200 WHERE account_number=2345;
IF ERRORS=0 COMMIT;
IF ERRORS<>0 ROLLBACK;

Unless you can post a detailed description of some process in your application and the database schema, generic examples like this one is all we can give you.

Answer 4

Yes, use transactions. You are inserting records into multiple tables and you want to make sure that they all go in accordingly. There really is no reason not to.

The other thing to note is that most financial databases (including my main project, LedgerSMB) use what I call a snapshot, log, and aggregation model where you are almost always dealing with append-only data for the most part. You really want to make sure that everything goes in correctly.