Best structure to use for categorising order records

[Angus McKinnon]

 

I’ve got a bit of an analysis task coming up over the next few weeks. The idea is that we’re going to be sifting through a database of orders, and essentially tagging each order with assorted variables. 

 

At the moment what we have is a fairly basic invoicing system, there’s a table of orders and a related table of line items for each order. The plan is that we’d add field(s) to the Order table for the variables we want to track, and then it’ll be a process of manually reviewing each order. 

 

The variables have to be added manually, there’s nothing there that could be derived from a calculation or summary on other fields unfortunately. It’s things like whether the customer arrived by car, whether they booked by phone, that kind of thing. We’re relying largely on people’s recollections but since all I’m after is the broad brushstrokes perfect accuracy isn’t vital. One record could easily have multiple variables tagged. 

 

At the moment I’m trying to think of the best way to structure this. The seemingly obvious way to do it would be to add a field for each variable we want to track. Since they’re largely mutually exclusive I don’t think a single value list is the way to go. However adding 8-10 fields is going to be messy. Once we’re finished tagging orders I’ll be looking to generate some simple reports, in terms of order numbers and value for each variable. 

 

I suppose I’m just looking for advice as to the most efficient, least cumbersome way of structuring this. The less “footprint” this process has, the better, as it’s not a core function of the system. 

 

The other thing I need to take into account is the tagging process itself. Even clicking on a check box is going to take quite a bit of time - we have several hundred orders to get through and a few wasted seconds per order soon stacks up. The last time I did something similar I was able to build a stand-alone database for this particular purpose (sifting through mailing data to work out whether it was worth sending anything to each address). I ended up writing a simple script which set the field and moved onto the next record, then had them appear at the top of the menu so that Command-1 etc. would trigger them. This sped up the process quite a bit, but it would be tricky to implement something similar on a live database - users are already regularly using the shortcuts for other things. Any good tips for quick selection of each variable?

[LaRetta]

If you use records with a new related join table instead of fields, you will enjoy maximum flexibility and grouping/reporting your information as you wish will be very simple.  I am dropping the name 'variables' because variables has a specific meaning in the software industry and I think it would haunt you down the road.  It seems management would data-enter what they wish to know, presented in the form of a question (either for the staff to ask the customer) or simply for the staff to answer.  Here is one approach:

Table:  Questions (holds one record for every question or bit of information you wish to gather )

record 1:
  ID 1 (unique auto-enter serial or UUID for this table)
  Description:  Arrived By Car?
  Control Type:  Checkbox (single)

record 2:
  ID 2 (unique auto-enter serial or UUID for this table)
  Description:  Booking Method?
  Control Type: Popup menu (value list of booking methods: In Person, Phone, Letter

etc --- then another table which will be the join table ... 

Table:  Order Answers (record will be created for every answer created against the Order)

record 1:
  ID 15 (unique auto-enter serial or UUID for this table)
 OrderID 36
 QuestionID 1
 Answer: 1 (yes)

record 2:
  ID 16
 OrderID 36
 QuestionID 2
 Answer: Phone

Reports will then be generated from this join table where you can group by the Questions and even include in the Questions table a Type field to further categorize them.   Your graph would look like this:

Orders --< Order Answers >-- Questions

The Questions table will allow Management to add questions as they wish and they can even change their wording on the questions since you select ( behind the scenes ) the actual QuestionID.  You can even have them define the entire questionnaire structure and use Control Type to open the right slider to display the dynamic control.  Include an IsActive checkbox in Questions and you can turn them on and off for different seasons.

You can of course name it Variables if you wish, LOL, it just seems wisest to name it something else. 

Edited December 22, 2015 by LaRetta

[LaRetta]

By the way, you don't NEED the Questions table - that is simply for automation and manager-controlled creation.  If you want the User to enter the question or information-bit and then the result, it can be simple one-to-many Orders --< Answers where both the 'question' and 'answer' reside in the same child table.  Placing an 'allow creation' portal on the Order layout is all that is required.

However ... this opens up possibility that Users will not be consistent enough to provide you with any report which can be grouped because they all might enter different questions or data-bits to answer.  A good, consistent structure will produce grouped reports with meaningful results.

[comment]

7 hours ago, Angus McKinnon said:

It’s things like whether the customer arrived by car, whether they booked by phone, that kind of thing.

 

7 hours ago, Angus McKinnon said:

Once we’re finished tagging orders I’ll be looking to generate some simple reports, in terms of order numbers and value for each variable. 

 

7 hours ago, Angus McKinnon said:

I suppose I’m just looking for advice as to the most efficient, least cumbersome way of structuring this.

The devil is in the details. I would like to see a full list of the attributes you intend to track and understand fully the reporting requirements, before making a judgment.

To be a little more specific: if you anticipate a scenario where orders need to be summarized by more than one attribute in the same report, for example:

Arrived by:
• Car:   51%
• Train: 28%
• Boat:  14%
• Other:  7%

Booked through:
• Phone: 41%
• Mail:  36%
• Web:   23%

then you will need a related table for the attribute values, as described by LaRetta.

 

Edited December 22, 2015 by comment

[LaRetta]

Ah.  Attribute and values ... that more closely names the new entities.  

If they are needed at all.

Edited December 22, 2015 by LaRetta

[Angus McKinnon]

Thanks for all the replies - that's a very helpful start. 

I agree that "variables" was a poor choice of terminology. Already I'm beginning to realise that I need to think things through in a bit more detail before jumping in and starting coding, which is good. 

From a business perspective, we have a few unrelated questions that we want to answer. I'm still fleshing out the exact questions, but I'm fairly sure that most of them will be simple "Yes/No" propositions. Things like "Would they be further away from us if we moved to x location?"   My concern is that with half a dozen questions we're adding a lot of overhead to the file, for something that is going to be used very rarely. 

I'm not anticipating that we'd need to summarise by more than one of these new attributes, it'll more be a case of, taking the example above, looking at the total order value of people who would be further away if we moved. That's a very useful data point for informing a potential discussion. 

Reading through LaRetta's first post, I'm thinking that a separate table holding the question data for each order might be the neatest way to do it, since it avoids adding more fields to the main order table. Once the process is finished I could potentially bin the entire table and be more or less back to where we are now.

[comment]

33 minutes ago, Angus McKinnon said:

I'm thinking that a separate table holding the question data for each order might be the neatest way to do it, since it avoids adding more fields to the main order table.

IMHO, adding 8-10 fields to a table is negligible compared to the overhead created by adding two related tables and all the necessary paraphernalia.

 

On the same note:

14 hours ago, Angus McKinnon said:

we have several hundred orders to get through and a few wasted seconds per order soon stacks up.

Several hundred orders is practically nothing. Even with a thousand orders, if you spend an hour designing a process that saves 4 seconds per order, you would barely come out even.

[LaRetta]

2 hours ago, comment said:

IMHO, adding 8-10 fields to a table is negligible compared to the overhead created by adding two related tables and all the necessary paraphernalia.

Just for consideration ...

Is it it 8 or 10?  Or 12?  15?  The number of 'questions' is unknown and adding fields will:

1. require a developer
2. require modification of layouts if fields are added
3. field names may not end up matching the 'question' placed on the layout as business changes their minds thus adding future confusion
4. fields will be left as residual in the table for years to come, long after this need is exhausted
5. a good questionnaire solution can even be another file, bolted onto any solution easily for use in other areas as needed
6. the wider the table (orders), the slower the download.

Edited December 22, 2015 by LaRetta

[Angus McKinnon]

4 minutes ago, LaRetta said:

the wider the table (orders), the slower the download.

Sorry, I'm not sure I understand that. Does it mean that the more fields there are in a table, the slower it operates, even if those fields aren't being used for much?

 

At the moment we have four questions/data points that we definitely want to look at. However I'm sure more will come up as the exercise proceeds, hence my estimate of 8-10. 

I can definitely appreciate points 3+4, as one of my jobs when revamping the main table a while ago was culling old fields that had been set up for a single-use purpose and abandoned thereafter. Some had helpfully descriptive names (like "Should we send them a 2004 Christmas mailshot?") but others were a bit more obscure. 

There is something quite attractive about a "bolt-on" solution that can be left out entirely when not required. Is it better to do this a separate file, rather than a new table within the same main file?

 

[LaRetta]

It does not need to be a separate file.  But the same questionnaire 'setup' can be used for multiple businesses, attaching to different tables within each business's solution (more a comment for developers who reuse their code).    It can be tables within a single solution as well which might work better for  a single business. 

When a record is fetched by the server, ALL fields except container must be downloaded whether the field is empty or not.  True they would be lighter without data (for future Orders after the process has ended) but the field would still download as well.

As Comment indicates, it will take a bit more to set up initially but not by much.  And the same process, once built, can attach next time to your Customers table so your staff/management can ask questions of customers when they call.  It can even be used for gathering information about your Products.  I've used the same process for surveys.

We're just talking through your options and perspective here.  Really only you can decide how much you might use such a tool and whether it is worth the additional effort to set up.  :-)

[LaRetta]

Here is an example file.  Notice that there is script trigger attached to both the Orders layout and Customers layout (in Layout Setup).  They both use the same Values table (answers).  The Attributes table can be provided to management to ask their questions.  

Notice how 'allow creation' is indicated in the graph and how the tables are related and that the portal is based upon Attributes but the value (yes) checkbox is based upon your Values table.

Surveys.fmp12.zip

Edited December 22, 2015 by LaRetta