Sql-server – Examples of SQL transaction procedures for sales tracking or a financial database

database-designsql serverstored-procedures

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.

Best Answer

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.