Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Invoice & Normalization

Posted on 2007-12-06
15
Medium Priority
?
1,080 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 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 51

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 51

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
The viewer will learn how to dynamically set the form action using jQuery.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

715 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