How to organize different types of

[Conartist]

I am creating a solution to track the different functions of a trade show decorating company. Primarily the solution will track customer orders, make invoices and also create "load lists" or reports that tell the production people how many tables to bring to the event, how much on site labor will be required, etc.

When I started working here they needed something that worked asap - so I threw together a very simple database that is sort of working but could be a lot more elegant. Now I have the time to properly design the system. But I’m a novice database programmer so I’m not really sure how to make things in the most efficient way.

My current question is concerning the different types of orders a customer has and the level of detail needed for each kind of order. For example a customer can order a table which is a simple item. But they can also order labor which has a lot more data that I need collected.

Here's how I'm currently tracking stuff. Everything is in an order table. All orders are the same...they have quantity, item description, notes and price. Therefore a table order looks like this...

Quantity, Description, Notes, Price

1, 6ft table,, 100.00

And a labor order looks like this with the details only being tracked in the notes field.

Quantity, Description, Notes, Price

3, Display Labor, 1pl x 3hr Wed 8am, 100.00

It is obvious that using the Notes section to track the additional data is just not a good way to go about things. But I’m not sure how to structure the database to account for a simple order and a more complex order. In the example of the labor order I really need more fields: # of labors, # of hours for each laborer, Day scheduled, Start time.

I know that I shouldn’t have these additional fields available in a table order because then I would have a lot of empty fields in my database as most of the bulk of orders are of the more simple type. Do I create different tables for simple orders and more complex orders? Do I track all types of orders in a simple order table but have additional tables track the more detailed items of only the orders that require more detail? I also need all of these orders to come together easily on one invoice because ultimately to the customer they are all just orders even if I need to track more detail on certain types of orders.