Good Morning everyone,
This is my first time posting to the forum so I apologize if this is in the wrong place, not enough data or if this has already been answered. I have been searching the web for the last few days trying to find a best option for my problem but have had no luck. So here it goes.
I am building a weekly Sales database for my current company. They have one in Access that is currently being used but I feel like it does not follow any of the best practices for a database. So I am rebuilding it in FM Pro 12. Within the new database the weekly sales totals for each department are imported from an xml file from our POS system. Everything is working just fine so far. I have the sales data relating to their proper departments and locations. The problem I am having is how to handle the date aspect of this database. Each sale entry has the week-ending date but the accounting department here uses 13 periods instead of 12 in a fiscal year. This causes the fiscal year to start on a different date from year to year. But this can change the period that each week will fall under.
Now within this database they want to be able to run reports by week, period, quarter and year. So my question is how should I handle the date information? What I was going to do is have a seperate table that handles the week-ending date and what period and quarter it should fall under using calculations. The calculations are being figured from the start date of the fiscal year that they enter into a resources table. This way accounting can change the fiscal year start date at the start of each year and the calculations for that year will be calculated using that field. I then relate the week info to the sales by a weekID that is auto-generated. Is this the proper way to handle it or am I over thinking the problem and should just keep the period and quarterly data within the sales database? Any help is appreciated.