Sorting

"Allen Browne" wrote in
:

You probably know that storing dependent data is a no-no, but if you
have lots of data (tens of thousands of records) and you are
constantly seeking/parsing/sorting on this field, it might be worth
the trouble.


In terms of longterm robustness, it would be worth thinking about
changing the design:

"translate" all these messy string identifiers into a rational scheme,
for example a long integer or a properly formatted string

store the sensible key, so that it will sort etc properly

parse it back into the brackets and things for display


If you start with a record 9.1001(a)(i), rewrite it as either 9100111 or
"9100111" [1] and update all the FKs in all the other tables. Then use a
format or a public function to traslate it back into "9.1001(a)(i)".

[1] The difference lies in how you manage depth of nesting. If you
suddenly decide you need a fifth level, say 9.1001(a)(i)(1), then you'll
have to multiply all the numbers by 10, whereas the string version uses
left-to-right character compares and will cope quite happily with and
extra bit on the end.

Whatever you decide, don't do anything till you have had a long and hard
think about your design needs. You really don't want to have to
restructure an important PK in a production database more than once!

All the best


Tim F