Database design

Question

I have a database in which I store product sales. My sales table is made up of the following fields: [saleID], [itemCode], [Date], [storeID], [quantitySold]. The problem is I want the user to be able to insert a payment for multiple saleIDs.

Prices come from the items table but there may be a discount if multiple items are purchased. I want to be able to store a finalPayment field and multiple sales connected to it, then group the sales by the specific finalPayment. However there could be same sum finalPayments that do not belong to the same sales batch so I cannot group by finalPayment as a field in the sales table.

I could create a finalPayments table and every time a multiple item sale and discount is made, store a new final payment in it, retrieve the last finalPaymentID and then store it in the sales table (in a new field [finalPaymentID] that I will create) for every sale that belongs to it. Then i could group sales by finalPaymentID.

Could the last finalPaymentID in the table be created by some other store? Can there be a concurrency problem? Do I need to lock the table in some way until the finalPaymentID is retrieved and stored in the sales table? How would you implement this?

Answer 1

I actually did with the payments table. I used string insertSQL = "INSERT INTO [payments ] ([paymentSum]) VALUES (@paymentSum);SELECT @@Identity";

and then stored the paymentID retrieved into the sales table for each sale that is part of the batch.

Answer 2

Sounds like a cart schema - something like this should do:

Product (your Items table)
 - | ProductId | Whatever

Payment
 - | PaymentId | Date | Whatever

Cart
 - | CartId | UserId | ProductId | PaymentId

For each product, you have a record in Product. Each 'Payment' record is a sum of all the products (the cart total). Each record in sale is a unique identifier to each item in the 'cart', so that you can say UserID 1 has 3 items in his cart for a specific payment Id (3 records).

Answer 3

I would create multiple tables.

Table Order would contain OrderId, Date, StoreId

Then I would create an OrderItem Table which would contain OrderId, Item, Qty, Price

There would be a one-to-many relationwhip from the orderItem table to the Order table.

That way you can group by OrderId to get your quantities and values.