I've seen a number of messages lately from people trying to do record numbering with prefixes: W00001 or d0003, etc.
This sort of approach is typical of human beings, but database designers must be ubermenschen (or uberfrauen) and must resist this tendency in favor of being smart.
Typically human beings like to encode a lot of information into an identifer:
example:
R2X9-99-44 means
R=Right
2X=you need 2
9=Type of widget
99 = manufactured in Taiwan
44 = Found in bin 44
etc...
You've all seen information encoded this way.
Database designers call this "intelligent data".
They also say: "All intelligent data is inherently stupid".
They then say: "Move the intelligence out of your data."
This means: Separately capture and record the attributes of this data element. Don't try to build an all-purpose identifier.
A good database designer would keep the above information cataloged separately:
Record Number: unique integer
Position (L, R, C)
Number required: integer
Widget type: integer
Manufacturer Location: integer, foreign key
Bin: integer, foreign key
Etc.
A second example can help clarify the problems with intelligent data: somebody invents a new widget type (10).
11 required.
It gets manufactured in New York (location 3).
The next new intellegent id is:
L11X10-3-1
Somebody says; Show me all the widgets of type 9 or type 10, or type 11 please.
If all you have is intelligent data, you have to some serious munging:
SELECT *
FROM myTable
WHERE substring(id,position("X",id)+1,length(id)-position("-",id)) in ('9', '10', '11')
By the way, since you're comparing substrings here, if you want to order by widget type, all the 10's will come first, then the 11s, 9s.
You could ORDER BY (1* substring(id,position("X",id)+1,length(id)-position("-",id))) to put things in numerical order
Suppose they also want the same query sorted by manufacturing location??? (And they will) Anyone care to code the 'order by' clause for that?
But if your data is properly captured and non-intelligent
SELECT *
FROM mytable
WHERE widgetType BETWEEN 9 AND 11
ORDER BY manufacturingLocation
If your users insist, you can easily recreate the intelligent human identifier from non-intelligent data:
SELECT concat(position,numberRequired,'X',widgettype,'-',mfgLoc,'-',bin) as humanTypeIntelligentData
FROM mytable
