Question Time table related to sales (1 Viewer)

[wakiak]

Hi all,

I have a question. How to create a time-based sales database and get the correct sales for my query? Given that my time table doesn't follow a Julian calendar.

For example, if I have three tables. The time table, the territory table and the sales table.

Time Table

PeriodID        PeriodStart    PeriodEnd  
2014, PERIOD 01         1/1/2014     2/1/2014   
2014, PERIOD 02         2/2/2014     3/1/2014   
2014, PERIOD 03         3/2/2014     3/29/2014  
2014, PERIOD 04         3/30/2014    5/3/2014   
2014, PERIOD 05         5/4/2014     5/31/2014  
2014, PERIOD 06         6/1/2014     6/28/2014  
2014, PERIOD 07         6/29/2014    8/2/2014   
2014, PERIOD 08         8/3/2014     8/30/2014  
2014, PERIOD 09         8/31/2014    9/27/2014  
2014, PERIOD 10         9/28/2014    11/1/2014  
2014, PERIOD 11         11/2/2014    11/29/2014
2014, PERIOD 12         11/30/2014   12/31/2014

Territory Table

Territory    SalesRepName    StartDate    EndDate
North East     Jim          1/1/2014    5/31/2014
Central        Jim          6/1/2014    12/31/2014

Sales Table Recorded Jim's sales number for Period 1- Period12

SalesRepName    PeriodID    Sales
Jim    2014, Period 01    1,500
Jim    2014, Period 02    2,500
.
.
.
Jim    2014, Period 12    4,500

From the territory table, Jim starts working in North East from Period 1 - Period 5. Then he moved to Central from Period 6 - Period 12. And I want to know the sales for North East, say, in Period 3, etc.

How should I construct this database and establish the relationship between the Time table and Territory table?

Thank you very much for your help!

 

[plog]

You can't, your data is unkosher. You're keeping times/territories/salesreps at the wrong levels throughout your data.

First, your Territory table isn't really a territory table, it should be named TerritoryReps. A Territory table would just list your Territories (North East, Central, etc.). From what you've posted, Territory table is a junction table; its creating a many-many relationship between SalesReps and Territories. It's definitely a table you need, you've just given it a poor name.

Second, and most importantly, you've allowed SalesReps to be assigned to a Territory by specific date but your Sales are recorded by period. One of those has to change if you want to report sales by territory using your periods. Either sales get recorded to a specific date, or salesreps need to be assigned by periods.

Third, SalesTable shouldn't have a SalesRepName field, instead it should use the primary key of Territory (more accurately, the table I described in the previous paragraph. Again, from what you've posted, you have no primary key in Territory table, you will need to add that.

Fourth, you need better primary/foreign keys. Time table (which I hope isn't named that--Time is a reserved word) shouldn't use that text field you've concocted as its ID field. You should use an autonumber primary key. Since '2014, PERIOD 01' is data you need to keep, I suggest you create 2 new fields to hold that data (1 field for the year, 1 field for the period). Also, Territory table should have an autonumber primary key since you will be using it as a foreign key in Sales.

 

[wakiak]

Hi Plog,

Thank you very much for your input. This is very helpful.

One of the question I still have. If I choose to assign SalesRep in my junction table TerriotryRep, by period. For example:

Territory        SalesRepID         StartDate               EndDate
North East        0000123           2014, Period 1          2014, Period 5
Central           0000123           2014, Period 6          2014, Period 12

Will I be able to establish a relationship between this table and the sales table?

Thank you very much for your help!

 

[plog]

Yes, but you need to start using better ID fields. TerritoryRep shouldn't look like that. It's should look like a bunch of numbers:

TerritoryRep
TerritoryRepID, TerritoryID, RepID, StartPeriod, EndPeriod
1, 4, 23, 9, 11
2, 4, 38, 11, 13
3, 9, 3, 8, 9
4, 7, 38, 8, 10

TerritoryRepID is the autonumber primary key of the table and would be used in as the foreign key in Sales to link the two tables. Every other value in TerritoryRep is itself a foreign key to its respective table.