CODEWITHSUNDEEP
sqlasp.net-mvcentity-frameworkdatabase-design
Josef
Josef

Reputation: 585

Invoice and transactions design

I am currently thinking of how to design my invoice entity. Specifically when it comes to the invoice amount.

These are the options I have in mind, what pros and cons do you see?

  1. The invoice entity does not hold the amount itself, instead it holds a virtual list with all the transactions related to the invoice and the amount is calculated and presented through those. InvoiceId as a foreign key.
  2. The invoice entity does hold the amount and the transactions list(as a safety insurance I assume?), still InvoiceId as the foreign key.
  3. The transactions entity does not contain a foreign key. Instead the amount is calculated from the transactions and stored in the invoice table. With some other reference to the corresponding invoice.

Currently I am using option number 1. But when I think about the future I realize that my transactions table will contain a lot of transactions. And at some point I might want to move those to increase the performance, without necessarily moving(droping) the invoice table. So number 3 would be my choice here. But, are there any other option that would be prefered?

Upvotes: 0

Views: 1369

Answers (1)

Twelfth
Twelfth

Reputation: 7180

Option 4?

Given your setup, I would go with a three table setup here...lets get rid of the entire update idea for this. Lets take 2 base tables...you'll have data fields, this is just name and ID.

Invoice Table
-InvoiceID

Transaction table
-TransactionID

Notice the lack of invoiceID? We'll have a third table control that relation.

 InvocieTransactions
-InvTransID
-InvoiceID
-TransactionID

Now when you do invoicing, you are simply doing an insert into this table and not an update...and that insert can be done with one select statement that grabs all the transactions and associate it to your new invoice ID.

I added the ITID as a unique identifier for this table...it's optional, but I like having the unique number on the table so you can refer to one row in the future (mostly for troublshooting purposes, or perhaps adding in a new layer of reporting)

Make sense? We can continue to talk through this if you would like

Upvotes: 2

Related Questions