EasyCFM.COM ColdFusion Forums / Other / Need second pair of eyes maybe three

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Need second pair of eyes maybe three

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

kicker
07-28-2008 @ 11:28 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 717
Joined: Oct 2004

OK I am creating a free invoicing program in AIR and need some help figuring out the db for optimum performance. Here is the table structure I have on paper. Just need to know if there is a better way or anything that should be added.

Customers (table)(Stores contact information for repeated invoices)
CustomerID (Identity)
Company Name
ContactPerson
Email
Address
Telephone
City
City
State
Zip

Invoices (table)(Stores the invoices created for customers)
InvoiceID (Identity)
InvoiceID (A number for the invoice to look up later)
CustomerID (From Customers table)
Total (invoice total)
Subtotal (subtotal of all lines)
Shipping
Tax
Notes (for invoice notes)
DateCreated

Items (Table) Stores each item from invoice so I don't have a common delimited list in the db)

ItemID (Identity)
InvoiceID (From invoice table for which item belongs)
Item (name for item ie: Web design)
Description (description of item)
Qty (qty of items being totaled)
Price (per unit price)
Subtotal (subtotal for line item)

That's it for the actual invoice portion. I have a separate table for preferences. Again just looking to see if there is a better way or if there are things are missing or whatever input I can get. Thanks in advance.


Craig

louissto56
07-29-2008 @ 1:18 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1149
Joined: Jan 2007

It looks good.

Why are their duplicates of city and InvoiceID?

Also some people leave out derived columns such as total and subtotal if they can be created in SQL. That way if something like the price changes, the total and subtotal can update accordingly. Up to you though.

With the items table you have invoiceID. Isn't it possible for many invoices to have many of the same items which would cause redundancy?

Maybe you should have an extra table called InvoiceItem that has two columns InvoiceID and ItemID that way you can link the two entities up without redundancy and without comma delimated lists or whatever.

Louis

-----------------------------
www.MyBizList.com.au - QLD Business Directory and rent-a-site

kicker
07-29-2008 @ 7:50 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 717
Joined: Oct 2004

Sorry the double city is a typo. It was late and I was seeing double I guess.  Playful Wink

The InvoiceItem table sounds like a plan thanks.

Craig

louissto56
07-29-2008 @ 10:41 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1149
Joined: Jan 2007

Been there bro, been there Playful

Glad to help! Good practise for my next assignment in computer studies (mysql databases).

Louis

-----------------------------
www.MyBizList.com.au - QLD Business Directory and rent-a-site


Website Designed and Developed by Pablo Varando.