Database Design Help Needed

[jonnyuk3]

Hi,

I am trying to create a database that would store some sales figure information.

We have 10 shop outlets which each have 3 chefs in them. Each chef has individual sales figures, with those being seperated into delivery sales and customer sales.

Each of these chefs may be assigned to any of the shops.

What would be the best way to go about assigning this information to a database.

At the moment I have one table for chefs (chefID, Name, Address), one table for outlets (ouletID, Name, Address) and then one for sales figures. This has an id, a week commencing date, delivery sales figures and customer sales figures.

What is the best way to link all of these together?

I would like to be able to assign a chef to an outlet and then to be able to enter his sales figures for a particular week.

jonnyuk3

 

[DCrake]

Simple Software Solutions

Because you have many chefs and they can work at many locations and each combination can have sales activity you will need to generate a many to many junction table. This will consist of all the location codes and a chef code for each location.

So when a chef works at a specified location you can record the activity for that session.

This will also allow you to interogate all activity for either chefs and/or locations.


CodeMaster:

 

[jonnyuk3]

Thanks for the reply.

What would the junction table contain then? Would it contain the sales data?

Or would there still be a seperate table for the sales data.

jonnyuk3

 

[DCrake]

Simple Software Solutions

When you record your sales data you will need two fields that will make up your primary key

Location
Chef
Date
Session
Takings
Covers
Etc
Etc


As well as the fields that hold the sales information

This will enable you to link to either the locations or the Chefs table independantly as well as in conjunction.

David