I'm working on a project that requires tracking the sales data for six
stores (although that number may increase). Each store is open Monday
through Saturday and only four specific holidays are observed. With
that said, I'm wondering, what is the best way to store the sales data
for each location?
One approach would be to simply use a 'stores' table and a
'sales_figures' table with each row in the latter containing a
store_id, the date, and the required sales figures. But this feels
repetitious to me as the same date will be stored six times (once per
store per day).
Would I be better served adding a 'sales_dates' table (and perhaps a
'holidays' table), thereby reducing the number of times a given date
is stored to 1, and doing some relationship business in the 'figures'
table (using 'store_id' and 'sales_date_id')? Or am I doing what I
always do and thinking about this one too hard? I'm just trying to
stay agile!
Sounds too complicated to me, and you'd end up repeating the
sales_date_id in a similar manner anyway.
I'd use the first option because it is straighforward and represents
the data in a natural way: this store sold this on such day. Thus the
involved code will equally be easy as far as this choice is concerned.
Not sure what the project's intention is. If it is just an aggregate
of total sales figures by date, then it appears you only need a table
called sales_aggregate, and another table called stores to store
details about each store.
Seems unlikely though. Aren't you getting more granular data, like
actual sales. If that is the case then your db structure should
include orders, order_lines, customers, stores, products. Then you are
in a better position to report on any of thos. The date or the sale is
stored in the order, and possibly you will need to cache at the order
level the period_id, which would be in another table to symbolize the
most granular timeframe you would report in, probably month_yr.
