Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Invoice & Normalization

Posted on 2007-12-06
15
Medium Priority
?
1,191 Views
Last Modified: 2012-08-13
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

0
Comment
Question by:sivakugan
  • 7
  • 2
  • 2
  • +2
13 Comments
 

Accepted Solution

by:
officedog earned 500 total points
ID: 20424074
It looks fine.
0
 

Author Comment

by:sivakugan
ID: 20424308
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
 

Expert Comment

by:officedog
ID: 20424329
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:sivakugan
ID: 20424457
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
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 500 total points
ID: 20424804
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
 

Author Comment

by:sivakugan
ID: 20424914
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
 

Author Comment

by:sivakugan
ID: 20424949
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
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 20428251
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
 

Author Comment

by:sivakugan
ID: 20429651
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
 
LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 500 total points
ID: 20429698
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
 

Author Comment

by:sivakugan
ID: 20430317
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
 
LVL 18

Assisted Solution

by:Jinesh Kamdar
Jinesh Kamdar earned 500 total points
ID: 20430497
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
 

Author Comment

by:sivakugan
ID: 20430907
okey. thanks kamdar.

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

Thanks a lot.
www.teacostea.com
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Ranking ecommerce websites is a vital process. You need to have a strong SEO (Search Engine Optimization) strategy. If you don’t have one, you are losing out on brand impressions, clicks and sales. Check this guide on how to improve website traffic …
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question