Solved

Invoice & Normalization

Posted on 2007-12-06
15
890 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
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 49

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 49

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DBF to ... Converter 5 69
Best practice for a landing page 3 62
Grunt Copy file to another destination. 1 33
Generate Scripts of Schema/Data with "WHERE" clause 6 60
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Color can increase conversions, create feelings of warmth or even incite people to get behind a cause. If you want your website to really impact site visitors, then it is vital to consider the impact color has on them.
This video teaches users how to migrate an existing Wordpress website to a new domain.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

786 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