[thelist] artificial sort order in db generated online form

[Luther, Ron]

Joel Canfield asked about database sort stuff:


Hi Joel!


>>My thought was to do the sort numbers in 100s; f'rinstance, with 9
categories 
>>they'd be 100, 200, 300, . . . 800, 900 and I could add in 750, then
725, 730, 
>>710, and never have to renumber (we do eventually remove old items,
but that's 
>>We'll never have more than a dozen categories, nor more than two dozen
items 
>>in any category.

Because of the 'small' number of categories and items you have, this
could 
work ... but it requires manual upkeep.

Another (similar) alternative would be to type your sort field as
'text'.  
That should give you a little more flexibility for inserting records 
between record number "2" and record number "3"! 

(Cuz you could go "2", "2A", "2AA", "2B", etc.)

But that also requires manual care and feeding.



>>Anyone have experience with a smart way to do this which allows for
growth? 

Maybe ... I saw a company that had a 'valid part numbers' database.  It
was 
pretty good sized.  (0.5M records? 2.0+M records? ... I don't remember.)

Anyway, they wanted every application from every system everywhere in
the 
world to present those part numbers in the exact same order.
[Consistency 
is a good thing!]

Here's what they did:

(1) Start with the multi-column database of part numbers (or other
stuff).
(2) Write an app that applies all the fancy 'business rules' to sort 
the records in a unique order.  

App reads in "table" and writes out "table + new field containing this
new 
unique numeric sort number".

(3) The version of the table with the new sort number field is then 
distributed to all the app folks/systems/critters.


As long as you remind the app folks not to 'search' on this sort field,

(Cuz the same record of data could be record number "345" today and 
record number "1029" tomorrow.), it works fine ... and (unless the biz 
rules change), it never needs manual fiddling!



HTH,

RonL.