What would be the best way to design a database in Quickbase

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

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. 

Option 1: 

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

Option 2:

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. 

Option 3

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)

Option 4

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.

Photo of Ankur

Ankur

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,572 Points 50k badge 2x thumb
I have done this many times and it is not complicated.

Set up a table of Store Budgets.  The Key field will be in the format Store-YY-MM, where the Store is whatever identifier you use for the Store, either a Store number or the Store Name.  Lets call this field [Store-YYYY-MM]

On that form you will have a field for the Store,  and numeric fields for the month and the year.

Then make a formula like called [Store-YYYY-MM (formula)]

List("-", [Store],ToText([Year]),ToText([Month]).

Lastly on the form make a Form rules that says when year is not 99999

change [Store-YYYY-MM] to the value in the field [Store-YYYY-MM (formula)]

So, now you go ahead an enter your budgets on the form and populate the budget table and that Key field.

On the detail Sales records you need to make a formula field that will match up the store and date to the budget Key field which is in the format [Store-YYYY-MM]



So it would be

List("-",[store], ToText(Year([Invoice date])), ToText(Month([Invoice Date])))

 Then simply make a relationships where 1 Store Budget has many Invoices and use that formula field on the Invoice Table as your reference field on the right side of the new relationship. 
Photo of Ankur

Ankur

  • 0 Points
Hi Mark, thanks for this help. I am new to quickbase, so let me try out this. If I face any issue, I will comment on this. Thanks for your help.

Ankur