Invoice & Normalization

Hi all,

I'm currently doing an E-commerce solution site for a Medium based company. In the development of E-commerce solution, I need to create an Invoice when customers do check-out.
I need to make sure from u experts, My invoice table and Normalizastion is correct.
In fact, I've confused a lot when I was normalizing the Invoice table.
So please have a look on it and give me a correct responses.

The below is the Final Normalized version of all tables. Can u please check the Invoice table for me?....
---------------------------------------------------------------------------------------------------------------------------
Customer (CustID, Username, Password, Email, FName, LName, Address1, Address2, City, County, Country, Postal)

Invoice (Invoice No, CustID,OrdID)

Category (Category_ID, Category name, description)

Product (ProdID, Size, Image, UnitPrice, Category_ID)

OrderLine (OrdID, ProdID, OrdQty, Tax, TotAmt)

Order (OrdID, CustID, OrdDate)

ShipLine (InvoiceNo, ProdID)

Thanks in Advance
Regards
www.teacostea.com

sivakuganAsked:
Who is Participating?
 
officedogConnect With a Mentor Commented:
It looks fine.
0
 
sivakuganAuthor Commented:
Hi,

My actual question is, The Normall and real world Invoice has following fields on it.
InvoiceNo ,FName, LName, Address,City, County, Country, Postal, OrdID, OrdDate, OrdQty, ProdID
Description, Price, Total, Tax, TotAmt.

Those fields are from various tables. According to me, After final normalization these fields are going to their particular tables. BUT More Importantly, The actual real life Invoice has these fields on it.
Therefore Do I need to include those fields in Invoice table as well? It's because my normalized invoice table has the only the following fields.


Invoice (Invoice No, CustID,OrdID) - In that case how do I generate the actual Invoice with the above mentioned fields?

Thanks in Advance
Regards
www.teacostea.com
0
 
officedogCommented:
It might be wise to create new tables with these details in specifically for invoices, such as invoice_items, invoice_customer_details, etc.

If you use your current scenario and say for example, the customer address changes. If you then need to regenerate the invoice, it will use the update address, which will mean the invoice is not correct as such.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
sivakuganAuthor Commented:
Hi,
So what would be the final Invoice tables?..

As far as I've understood according to urs, I'll have the following two tables for Invoice
Invoice_customer(Invoice_customer No, Customer No, all other customer details)
Invoice_Product (Invoice_Product No, Product No, all other product details)

If this is the case, It will duplicate the customer and product details?...Don't u?.....Because the same details are in customer and product tables.....????????

Can u please indicate the correct version of invoice tables and fields with the relationship.
So that I can get a better picure in this issue. It's because I've confused a lot in this issue.
0
 
Patrick MatthewsConnect With a Mentor Commented:
officedog said:
>>If you use your current scenario and say for example, the customer address changes. If you
>>then need to regenerate the invoice, it will use the update address, which will mean the invoice
>>is not correct as such.

Agreed.  The alternative would be to hold things like addresses in effective-dated tables.

Same holds for prices, which will certainly change over time.
0
 
sivakuganAuthor Commented:
Is it possible for u to give the normalized table with the fields on it?.......Because it's not possible to continue with just ur ideas. I just want to see how the output would be. so please........
0
 
sivakuganAuthor Commented:
What I'm all asking is, give ur answer with the tables and fields not with just the sentence. so that It won't have problem for me.

As far as the address and price changes issue, There will be an back-end Administrative page , which is connected with the product table such as add product, update product and delete product.
so if there is need to change the product price, Administrative will do that.

Does it make sense?..or seems to be stupid.
0
 
Gustav BrockCIOCommented:
As you may have more than one invoice for one order, you could go like this:

Customer (CustID, Username, Password, Email, FName, LName, Address1, Address2, City, County, Country, Postal)

Order (OrdID, CustID, OrdDate)
OrderLine (OrderLineID, OrdID, ProdID, OrdQty, Tax, TotAmt)

Invoice (InvoiceID, OrdID, ShipperID)
InvoiceLine (InvoiceLineID, InvoiceID, OrderLineID, Qty, Tax, TotAmt)

Product (ProdID, Category_ID, Size, Image, UnitPrice)
Category (Category_ID, Category name, description)

ShipLine (ShipperID, InvoiceID,  ??)

/gustav
0
 
sivakuganAuthor Commented:
ok, that seems to be fine...just need one more confirmation.
As I said before, The real world Invoice include everything from Customer details, Product details and Order details. so In order to get this, Do I need to derive those details from the appropriate tables into InvoiceLine table?......or It shoudl be process oriented task to do that..?

I apologize again, If this is stupid question. cause I haven't got the actual idea yet.
Thanks in Advance
www.teacostea.com
0
 
Gustav BrockConnect With a Mentor CIOCommented:
It depends on if these data get changed. A customer my relocate but the old invoice should remain the same in your files.

The easy way to get around this is to copy customer name, address, etc. to the order as fixed data. Now the incoice will pull those data from the order.
A more advanced method is to timestamp records ... never delete or change but copy to new if a field is changed.

/gustav
0
 
sivakuganAuthor Commented:
Thanks cactus, I appreciate u for quick replying.
Is there any sample or model Invoice development in Ms.Access database I can see with the E-commerce solution. I hope it will give me a proper understanding.

Thanks
www.teacostea.com
0
 
Jinesh KamdarConnect With a Mentor Commented:
Yes, MS Access has similar sample tables. Select the option of Creating a New Table by Wizard and it will show you a range of ready-made tables to be created from.
0
 
sivakuganAuthor Commented:
okey. thanks kamdar.

Hope u all continue to support my question, until I finish this Invoice problem

Thanks a lot.
www.teacostea.com
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.