1

I am trying to develop a small business web application for my friends business. I do have some php,html experience so I have done the interface and all. Now I am trying to work on the database. The whole process is simple , the user creates a quote and once the quote is approved , he can use the quote to create an invoice. Here are the items in the quotation page ( I will call below items as variables/columns)

  1. Quotation Id ( will be automatically generated)
  2. Date
  3. Requester ( alpha numeric with space )
  4. Reference code ( alpha numeric with space )
  5. Quote valid for ( alpha numeric with space )
  6. Bill to ( Company Name, City , Country , Zip code , Telephone )
  7. Ship to ( Company Name, City , Country , Zip code , Telephone )
  8. Items ( there will be more than one items ) [ Item Name, Item Description, Unit of Measurement , Quantity , Unit Price ]
  9. Total amount
  10. Terms and Conditions ( this will be a 3 line sentence, it can be anything , it will be a text .. example below ) [ stock available/ not available / 2 -3 weeks delivery period / cash on delivery / 35 days credit ]
  11. Created by

and the invoice page will have ( I will call below items as variables/columns)

  1. Invoice number
  2. Date
  3. Requester
  4. Reference code 1
  5. Reference code 2
  6. Purchase order code
  7. purchase order date
  8. Credit terms(days)
  9. Bill to ( Company Name, City , Country , Zip code , Telephone )
  10. Ship to ( Company Name, City , Country , Zip code , Telephone )
  11. Items ( there will be more than one items ) [ Item Name, Item Description, Unit of Measurement , Quantity , Unit Price ]
  12. Total amount
  13. Terms and Conditions ( this will be a 3 line sentence, it can be anything , it will be a text .. example below ) [ stock available/ not available / 2 -3 weeks delivery period / cash on delivery / 35 days credit ]
  14. Created by

As you can see both the quote and invoice has lot of similar columns , how can I make normalized table in sql for the above. I am guessing there will be quote table, item table , customer table and invoice table. But in that case there will be duplicate datas in both quote table and invoice table [ example , total amount is same, terms and condition is same , requester is same etc.. ].

What the application will do is , when the user press 'add quote' , a tab opens with fields and the user can input all the above(quote) variable/columns. And once he is done he can save it , in that case the variables it will be saved in the database in respective tables, later once the client approves the quote, the user can go to the list of quotes section and can press the action button to convert this quote to an invoice, when the user press the action button a new tab opens with the invoice format style, most of the fields will be automatically filled from the database, the remaining fields like [ purchase order number, purchase order date etc.. ] he will have to enter.

I guess I have explained what I am trying to do. What I am hoping from you all as help is the SQL statement for creating the table or at-least an idea of how many tables do I need and what all will be the foreign keys, may be ERD diagram with entities.

Making the tables 3NF , so there will be no duplications, trying to save the database space.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.