This is more of a 'what is a general best practice' question than a specific code or syntax question.
Recently the data users here have started to define names of specific combinations of data, or want data broken out by sub-parts of a field. I am considering different ways of making sure the various report writers and ad-hoc users are arriving at the same datasets when quering for these.
The simplest example is: We have a source sales table with around 7 million rows. There is a new need to report against how the sales order was given to the company, that being electronic submission, paper, or scanned. The way to determine this is from a single character position in the order#. There are a dozen values, which break down to the three categories.
I am considering these options to make sure all users of the data get these categories correct.
A user function, which takes in the order ID, and returns the category. The function really is just a case statement, so not doing anyother selects or queries.
A calculated field in the table which again, uses a case statement to set the category.
A new field in the table, which is populated as the records are added.
In this which makes the most sense in your opinion/experience to do? There is a possibility of perhaps a dozen other groupings or flags which would work like this.
The second situation is a grouping which will require self-joins, or lookup queries to arive at the correct flag. In these I could either create a function, or add fields to the table with the categories. In this situation type, there could be twenty or more flags.
My inclination is to in all cases add either fields to the table, which are marked during the data loads, or to create a new table with the key from the sales data, and fields for the flags being used. However, I do understand there are differences in how one sets up a data warehouse vs a transactional system, and I have found many times when browsing through this forum amazing ideas which seem slightly counter-intuitive to me. So, I think it is worth asking others their thoughts on this, perhaps there is another option I have not thought of which is considerably better.
Any thoughts? I am sure 'It depends', what sorts of things should be considered in such a depends case?