The question might seem like a vague or too general to answer specifically, however let me be precise. I wish to build the data base where I would like to capture by month by store sales data and I also have month wise store wise budget data. I can see here a few options and please suggest what would be the best way to create so that we can monitor month wise Actual Vs Budget report, YTD actual achieved vs budget and future scale ability point of view. We have date wise invoices for actual sales, but not sure how to design if we capture by invoice sales but budget is for the whole month.
Create a table for Actual numbers and Create a text multiple choice field for year selection, another multiple choice for store selection and 12 currency field with name Jan, Feb....Dec for sales number.
Create a table for Budget numbers and create similar fields just like in actual table
Create a table for Actual numbers, create a date field to put month end date for actual sales (this option allows me to put sales by day also if we need to put later), a simple text field to put invoice number (for future), a multiple text selection field for store and one currency field for sales number.
The difference is, in the first option, I can have the month wise sales number as a part of 1 record and in the 2nd option, I will have the month wise sales in 12 records.
The same structure as option 2 for Budget table as well.
Create the structure like Option 1, but include both Actual and Budget in the same table and instead of 12 currency field create 24 currency field with Jan, Feb, ...,Dec and Jan (Budget), Feb (Budget),....,Dec (budget)
Create structure like Option 2, but include both Actual and Budget in the same table with the field options- Date filed to select last date of the month, Text multiple selection field to select data type Actual or Budget, another text multiple choice filed for store selection and One numeric currency field for Sales number. So, I will have 24 records for 12 month with type Actual and Budget.
So, which one would be good, or none of them and there may be another best way. I thought of putting store in a master table and then build relationship, but since store increase and decrease is not frequent, so thought of putting in Text Multiple choice, you may also please suggest on this.