table and relationship structure

[ragnarok75]

I have been tasked to develop a database that tracks departmental sales per day and location. I am having some trouble setting up the initial tables. I have a customer count that applies to the day only, then I have sales figures for each of 12 locations for each department (10) for every day. Which data\fields do I place in which table? Anyone have any ideas?

 

[Golom]

Usually its
[tt]
SaleHeader
SaleNumber
SaleDateTime
Location
... Other Info About the WHOLE Sale ...

SaleLines
SaleNumber
LineNumber
ItemSold
Department
Quantity
Price
[/tt]
and then

Select Department, Sum(Quantity*Price) As [Sales]

From SaleHead H INNER JOIN SaleLines L
     ON H.SaleNumber = L.SaleNumber

Where H.SaleDateTime Between [Enter Start Date] AND [Enter End Date]

Group By Department

will give you the sales by department for some date range.

 

[ragnarok75]

The client is looking for something at a higher level. They do not want individual transactions in the DB. They just want a sales number for every store, location, and department that shows total daily sales. There are no items or transactions in the DB. My question is do I create a different table for every location or do I lump all sales into one table and make the date, location, and department the primary key?

 

[Golom]

OK ... but where does the data come from to supply those values?

At some level they are doing individual sales and the mechanism to roll up sales by department or by location must involve the sort of calculations that I illustrated above. If someone is doing all that manually and just entering the data after it is cumulated then the structure that you suggest would probably work.

I would use one table with location as one of the columns and the primary keys would be as you suggest ... Date, Location & Department. Multiple tables make it very challenging to answer questions like "Which Location had the Highest Sales in Department 10 on July 7?" but that's simple if Location is just another column.

 

[ragnarok75]

Should each year of sales be a different table?

 

[Golom]

No. The sales numbers are an attribute of the date which includes Day, Month and Year. Splitting into multiple tables for each year raises the same problems that splitting into different tables for each Location does.

 

[lespaul]

have you read the fundamentals document linked below?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi