Image

Imagedivineright wrote in Imagephp

Setting up a database

I have been asked to build a sales/order/customer/employee management system for my company. I am having a problem deciding how to set up the "sales/order" tables. The company sells domain registration, hosting, web design and on hold messaging. I cant store all the orders in one table called orders simply because all the different products have different information that needs to be stored but I am just not sure that 3 separate tables is the way to go.

The website table would contain:
order# | designer | start_date | completion_date | salesman | customer# | sales_date

The hosting table would contain:
order# | domain_name | start_date | location | salesman | customer# | sales_date

The domain table would contain:
order# | domain_name | start_date | salesman | customer# | sales_date

Should I have one table called orders that contains the common columns like salesman, customer# and sales_date and then use the order# to get the information from appropriate table? The company will probably add more products in the future.

Any advice would be appreciated.

cross posted in PHP and webdev