Opinions on invoice tables?

Question

I want to create a table or tables for invoicing.  Below is the general design so far.  This is an academic exercise so it may be some what simplified.  I'm hoping for criticism in case I should normalize this more, or any other thoughts that any may have.

I've split it into two tables.  One is the general invoice table and the other is the for each line item in an invoice.

I'm most uncertain about how I'm dealing with the procedures performed and the medications prescribed.  I want them to be exclusive of one another.  I want each line item to represent a medication prescribed or a procedure performed.  I don't want a line item to have the potential to have both a medicine prescribed and a procedure performed in the same record.  I don't know the best way to deal with this though.   Should this be broken into two tables? I thought about a quantity column but decided to have each line only represent one med or procedure.  

Invoice Table 

InvoiceID(PK)

CustID(FK)

DateCreated

CreatedBy

Status - pending or paid (should this be just a boolean for paid?  this would assume not paid is pending)

SubTotal

Total_Cost - computed column subtotal * tax rate

InvoiceItems Table

LineItemID(PK)

InvoiceID(FK)

ProcedurePerformed - ID from procedure table

PerformedBY - ID from Employee table

MedPrescribed - ID from Medications table

PrescribedBY - ID from Employee table

Procedure_Cost

Med_Cost

LineItemTotal

Answer 1

This should answer your question and any others that may come up after. It explains many to many relationships and a lot more:

http://www.sis.pitt.edu/~valeriab/1022-spring08/Chapter6.pdf

---

John

http://knowledgy.org

Answer 2

Hmm, this is a lot to do in a forum... (and obviously there is probably a question of what kind of "academic" exercise it is :)

But your question of normalization is a good one, and I would definitely lean towards going farther, particularly in the design phase.  What is immediately uncomfortable is these sets of attributes with repeated meaning.

ProcedurePerformed - ID from procedure table

PerformedBY - ID from Employee table

Procedure_Cost

and then:

MedPrescribed - ID from Medications table

PrescribedBY - ID from Employee table

Med_Cost

What I would lean towards (sans requirements and just thinking out loud :) would be a Cost Item Superclass, which has a description, a type, and a cost.  That would have sub classes of Procedure, Medicine, etc, which would have all of the details you may need for the invoicing of such items.  Those would link to the rows that actually describe the giving of the med or the procedure performed (which are different things, wheras the charging for them is the same thing)..

The goal of database design is that one row has one meaning and represents one action. 

In the end, it might be that implementing it as you have it is "good enough", but minimally I would suggest having one column for cost that you do math on.

---

Louis

Answer 3

check this link too

http://www.maakal.com/maakalDB/ERD_Examples3.htm

Answer 4

The name InvoiceItems ties the data to the Invoice. Hence, with two types, it gets confusing.

Instead, while still following the same scheme, it could indeed be split and renamed. For example, Invoice_Procedure and Invoice_Medication. It would be upon the client to check both TABLEs, or perhaps a single VIEW can pull the data together.

However, the approach could be completely different. The procedure and medication have nothing to do with the invoice. That would be associated with a session or appointment. The InvoiceItems TABLE could then be designed to have a line item type, in lieu of referencing the actual medication or procedure. Though, ultimately, it matters on the overall datamodel.

Answer 5

Hi I would suggest like please correct me if I misunderstood the requirment

InvoiceItems Table

LineItemID(PK)

InvoiceID(FK)

ProcedureRef - (ID from procedure table OR ID from Medication

PerformedBY - ID from Employee table (One Refenece is enough)

Cost (One Shared column for Both procedures )

LineItemTotal   (R is furhter Qty Calculation there or Discounts ther Otherwise I dont think it is require at line level  )

Procedure Type (This is required only for identification Other wise from Procedureth medication/proceeduyre table field you ccan get desired results )

hope it will help you.

---

------------------ Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Maqbool.