CODEWITHSUNDEEP
sql-serverdatabase-design
charles
charles

Reputation: 35

Database schema for end of day process

I got a POS and inventory system which contains a receipt table for the invoice headers and a receipt_lines Table for the contents in it. where the user have to press a close of day button at the end of day for the table to be dropped and recreated and the invoice number would start again from 1 at the next day. The data in the past table would be copied to a historic table before the current table is dropped.

I wonder if there is a better way to do design this schema where the receipt number resets every day and stays the primary key:

Upvotes: 0

Views: 127

Answers (1)

eddieO
eddieO

Reputation: 119

Not totally clear what you doing but first you don't have to drop a table. You can just truncate and reset identity:

TRUNCATE TABLE [Receipt ] GO
DBCC CHECKIDENT ('Receipt ', RESEED, 0)

Second, it seems that you need to have a PK receiptdate & receiptID have a another table to just hold the receitID and reset that table every day. This way no need to copy any records just query based on receiptdate & receiptID, that way no needless and recource consuming of copying of historical receipts.

Thinking a little more about it you can just have a ReceitID table to hold a counter for each date (one row per date) that contains Date and ReceitId where Date is PK and ReceitID is just an integer, and increment this ReceitID on every insert. First increment that table then use that id in your "Receipt" table.

Upvotes: 1

Related Questions