5

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.

4
  • Hi @fa1c0n3r ~ Can I ask what was so hideous about my edits to your question that you felt that they needed to be rolled back? I don't mind them being rolled back, but I would love to learn how to improve the site overall. I felt they were constructive.
    – jcolebrand
    Feb 15, 2011 at 16:12
  • 3
    Is there something wrong with the shift keys on your keyboard? Perhaps you need a new one. Feb 15, 2011 at 16:19
  • Sorry was typing posts on my ipad...
    – fa1c0n3r
    Feb 16, 2011 at 5:59
  • thanks for the constructive edits I shouldn't have rolled it back/ just a mistake.
    – fa1c0n3r
    Feb 18, 2011 at 10:29

4 Answers 4

5

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.

1
  • Thanks @MR DENNY I appreciate the advice. I'm in the middle of designing stored procedures for insert new item, change item price, cancel existing item, change status of existing item .It is definitely looking like each of those stored procedures will be better dealt with and held together as transactions, especially because each product item has child nodes which will be stored in other tables and changing all together, will put a simplified db diagram here and a simplified version of the stored transaction procedures as soon as they are written out.
    – fa1c0n3r
    Feb 15, 2011 at 14:45
4

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.

1
  • Thanks. This is a good basic design. I've already created the db except for stored procedures, but basically all sales tracking systems databases look like this. Mine has to pay particular attention to the price of the car being shown and sales details which can be added in foreign key linked tables to vehicle.
    – fa1c0n3r
    Feb 15, 2011 at 14:56
4

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.

1
  • Thanks for the example. I was trying to find examples like this which outlined all the main transaction stored procedures you would need in a basic sales database (to be accessed by a c sharp app later). I do realize each database is different, will post the basic design and what the procedures will look like to ask for everybody's opinion.
    – fa1c0n3r
    Feb 15, 2011 at 15:20
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.