Solved

Invoice & Normalization

Posted on 2007-12-06
15
921 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 2
  • 2
  • +2
15 Comments
 

Accepted Solution

by:
officedog earned 125 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 125 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 50

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 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 125 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 125 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Original post  on Monitis Blog. Web performance monitoring is broken into two camps: passive and active. Passive monitoring is defined as looking at real-world historical performance by monitoring actual log-ins, site hits, clicks, requests for …
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

752 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