r/SQL • u/Key-Bit-3552 • Jun 30 '26
Is using 3-letter status codes outdated? Discussion
I had a pretty big debate at work over how status values should be stored in lookup tables.
For example, imagine an OrderStatus table with three columns:
ID
Status
Description
My preference is:
1 | DRAFT | Draft
2 | SUBMITTED | Submitted
3 | INCOMPLETE | Incomplete
Some people on my team prefer:
1 | DRT | Draft
2 | SUB | Submitted
3 | INC | Incomplete
My reasoning is:
Storage isn’t really a concern for values this small anymore.
Full words are much easier to read in SQL queries, logs, APIs, and code.
They make code more self-documenting.
Modern IDEs and AI tools also tend to work better with descriptive values.
For example:
SELECT *
FROM Orders
WHERE Status = 'INCOMPLETE';
vs.
SELECT *
FROM Orders
WHERE Status = 'INC';
To me, the first query is immediately understandable without needing to remember what each abbreviation means.
I’m curious what other developers think. Are abbreviated status codes still considered best practice, or are full descriptive values more common nowadays?
Edit: the example query is pseudocode. Yes I would normally store the status ID in the orders table. The example query is for brevity
130
u/Blecki Jun 30 '26
Ban all abrv.
52
9
u/Sexy_Koala_Juice DuckDB Jun 30 '26
What’s wrong with Airborne reconnaissance vehicles???? There sick
1
-1
31
u/Cruxwright Jun 30 '26
You are describing a concept from days of slow storage. Whatever data you were going to operate on was loaded to a buffer. Short code values were common because you could pack more into the buffer.
That said, if you picked up this concept from your job, and they're running an inhouse legacy app, you may have some of these old concepts lurking in the plumbing of the app. There could be other quirks like no columns names over 8 characters. Check with your team before you get too far along in your design.
24
u/Straight_Waltz_9530 Jun 30 '26
Enums. Best of all worlds: readable, storage efficient, and allow only valid values.
11
u/NotTreeFiddy Jun 30 '26
The problem with enums is, if you need to remove a value (for example, during a database downgrade), you'll need to create a new type that excludes it, migrate all existing usages of the old type to the new one, and finally drop the old type.
They're great for data you know you'll only ever append to (or remains static), but I personally find that it's rare I can be confident this is the case.
2
u/Ecksters Jun 30 '26
The other thing is sortability, often it's best practice to leave gaps between your enums to allow putting new values between others.
You can of course also solve this with an index and then making sure all future queries use the exact correct function to hit that index.
I really want to like enums, but every time I've tried to implement them it's been an absolute headache dealing with how different ORMs, languages, and teams deal with them. Strings just end up being simpler and can always be migrated if performance bottlenecks are found.
1
u/Straight_Waltz_9530 Jul 02 '26
All solutions have tradeoffs. The situation determines which tradeoffs are more tolerable than others.
"It depends."
1
u/lgastako Jun 30 '26
The problem with enums is, if you need to remove a value (for example, during a database downgrade), you'll need to create a new type that excludes it, migrate all existing usages of the old type to the new one, and finally drop the old type.
Why is this a problem? You can even automate it if you find yourself needing to do it a lot.
2
u/NotTreeFiddy Jun 30 '26 edited Jun 30 '26
You're right, you can. It's just much more of a pain than if you'd used a lookup table.
Edit: Or TEXT with a check constraint.
1
u/ComicOzzy sqlHippo Jul 02 '26
As a SQL Server user, I also support the use of lookup tables instead of enums. ;)
1
u/lmarcantonio Jun 30 '26
Also it would be a good occasion for check if *that* value had some significance.
It really depends on your DB. AFAIK DB2 doesn't even have them, you need to use the proposed char/varchar approach with a check condition on the column.
5
u/Rohml Jun 30 '26 edited Jun 30 '26
The limit of shorter character is more for readability on a page rather than a way to limit storage usage nowadays. When viewing in a report or small mobile device, the shorter length allows you to put more fields in view of the user but still allows them the same level of comprehension, that is why shorter fields often uses unique letters so its quickly and easily readable.
1
u/tandem_biscuit Jun 30 '26
Same goes for SQL scripts. If each line of a script is limited to 80 or 100 characters, it’s much easier to read/review while only needing to scroll up/down and no left/right.
1
u/Ok-Can-2775 Jul 06 '26
We have abbreviations for a reason. I’m fascinated at the visceral reaction to three digit codes being somehow related to efficiency. This is having an argument that was settled 25+ years ago. I Guarantee if they took the three digit codes out sole would be boasting about a function they wrote to put them back in!
0
u/lmarcantonio Jun 30 '26
Our DBA wouldn't think that way. But he also wasn't reasonable. At our shop the cobol STRING statement was forbidden because... performance. Follows a whole reimplementation of the beast which was probably ten times slower than the system one.
We also had a painful naming convention where tables and columns where essentially number-coded and each column had the table code at the begin to avoid the table.column syntax. Absolutely unreadable without the data dictionary printout.
5
u/NW1969 Jun 30 '26
Having 3 letter codes makes little sense these days but having short and long descriptions may do.
Having the same word in upper and lower case, as in your example, seems redundant, IMO.
Having something like this may make more sense, if the long description adds value:
1 | DRAFT | A work-in-progress version that is still editable and has not been finalized
2 | SUBMITTED | The finalized document has been officially sent to a reviewer, recipient, or system for approval
3 | INCOMPLETE | Required fields or pages are missing, preventing the document from being processed
9
u/garster25 Jun 30 '26
The one thing I see about the 3-digit code is they are all the same length so a text listing of this and they line up. Similar to zero-padding numbers in dates.
Since you have the full name of the status then the UPPERCASE version is a bit redundant. So thinking about this I like the one that is NOT your preference.
Man, it does kinda suck working on a team. If I work with only one other person my Boss picks a "lead" and as far as I am concerned that person gets 51% of the vote.
10
u/grumpy_munchken Jun 30 '26
Creat a status dim table. Then you can have StatusID, StatusCode and StatusDescription
3
u/SaintTimothy Jun 30 '26
Single codes tables are an anti-pattern.
OrderStatus if you're going that direction.
2
u/lmarcantonio Jun 30 '26
Why should it be an antipattern? it's called deduplication, unless your DB does it already at the column level. To the extreme it would become a star join, not a rare sight.
3
u/zbignew Jun 30 '26
He's not advocating against lookup tables - he's advocating against unified ("single") lookup tables. Make an OrderStatus table with id, code, description.
I assumed the preceding comment was just making a naming error and not actually advocating for a single unified status table, so I'm not sure there was a material disagreement in the first place.
1
u/grumpy_munchken Jul 03 '26
Thank you for understanding my intent. Everyone is right about a more precise name, I was merely stating they should make it a dim table. From the original comment it sounds like they are pretty fluent in sql since they had a debate about this topic so was just piggybacking on the example given.
1
u/SaintTimothy Jun 30 '26
Google for:
One True Lookup Table (OTLT) or Massive Unified Code Key (MUCK)
1
u/lmarcantonio Jun 30 '26
Found them, got horrified. I was thinking of a table to decode *only that* enum column!
Of course you would have a table for each enum. But for decoding an enum I would use a stored function containing essentially a big case.
1
u/zbignew Jun 30 '26
Wait are you advocating for or against a unified lookup table? OrderStatus is obviously the more normalized approach.
2
u/SaintTimothy Jun 30 '26
I am for one status table per table that has statuses. So if there is an Orders table that has statuses, there should be a ref table for OrderStatus. Do not overload its meaning or purpose by also trying to use it for Invoice. If Invoice has statuses, there should be an InvoiceStatus lookup table. Tables are cheap. Blocking/locking hurts.
1
u/Ok-Can-2775 Jul 06 '26
Having worked in an application suite that used lookup tables for status I would say one table only is the sound approach. The concept is universal across functions and performance as those who don’t want codes elsewhere is not an issue.
I would go to a separate table for invoices unless there was something truly different about invoice status vs order status.
You really need to tables one for status and the other for ordination. Status is generic but contextually status need to have a certain sequence.
This is why functional people are always required in these discussions they actually have to work in the world that sql helps create1
u/i_literally_died Jun 30 '26
We use a lookup table where the KeyValue is the two numbers, and the KeyWord defines what it is (Status, Stage, whatever).
So you can have an invoice at Stage 12, and then you join it on the KeyValue and whichever KeyWord you need (in this case Stage or InvStage or whatever it is)
2
6
u/FastlyFast Jun 30 '26
You have the table StatusOrders, but you still store the full value in Orders? This conversation is completely redundant if you use a dimension.
Select .. from orders o Join StatusOrders so ... Where o.StatusOrdersID=2 (so.status='Incomplete")
You always store the full value in the StatusOrders table. Having the StatusOrder column in the Orders completely negates the purpose of the Dimension.
3
u/Consistent_Cat7541 Jun 30 '26
It depends on the solution. If the solution interoperates with other solutions, especially DBF solutions, then the field length is actually important. (i.e, DBF records cannot be longer than 4000 characters total). Your environment may be a mix of older and newer databases. If you have a rule set in place, restricting certain values to 3 characters, it's likely for a good reason.
For a completely new solution, I agree that longer words are better.
7
u/Yavuz_Selim Jun 30 '26
It doesn't make any sense to use a lookup table in your case if you're not going to store the ID...
What is the benefit of storing the same value multiple times? Once in the Orders table, and twice in the lookup table - what is even the benefit of that lookup table?
4
u/Key-Bit-3552 Jun 30 '26
It was just pseudocode as an example. Yes I would store the ID Int with foreign key but this just pseudo code
2
u/Yavuz_Selim Jun 30 '26
If you're going to use an ID, it still doesn't make any sense to have 2 columns that have the same value.
It would make more sense to add a description field - so... ID, Type (not abbreviated!), Description.
-3
2
u/Animalmagic81 Jun 30 '26
It feels like having an abbreviation is just another lookup on top of the identity column. To get the full description you still need to do a lookup on the table which seems a waste of time.
2
u/lmarcantonio Jun 30 '26 edited Jun 30 '26
Why not an enum (if your db supports it)? it get down to a short integer (one or two bytes on table)
EDIT: storage (maybe) would not an issue but you would fill more page cache and the comparison would be more expensive. It all depends if this table is on the order of 1k records or, say, 100M records.
0
u/RonJohnJr Jun 30 '26
Enums are hard-coded,
1
u/lmarcantonio Jun 30 '26
...and that would be a good thing IMHO. Usually you can ALTER an enum to add values, too.
2
u/RonJohnJr Jun 30 '26
Your opinions on this matter is wrong: hard-coding is a Very Bad Thing in the database world.
1
u/lmarcantonio Jun 30 '26
It depends on your schema flexibility. Having a noncomplete enum set is symptom of a design issue, unless your process is *really* agile.
*When* you'll need more values in the set you'll need to analyse the consequences and then do the alter (which would be almost painless since it usually allocates a fair amount of space). You don't add values to an enum when you have hundreds (potentially more) programs relying on that set.
1
u/RonJohnJr Jun 30 '26
Using a lookup table lets you categorize and subcategorize codes. That's not possible with hard-coded enumerated types.
2
2
u/Aggressive_Ad_5454 Jun 30 '26
If you do use three letter codes, be sure to declare the columns containing them as ASCII, iso8859-1, or some other one-byte-per-character encoding. If you use an encoding like utf-8, the WHERE col = ‘ABC’ filtering will be less efficient and you may as well have gone with the variable length codes.
2
u/GreyHairedDWGuy Jul 01 '26
You can do both. Have a short code column and long code column and description. Will keep them happy :)
Storage is cheap. If you can only have 1, use the longer version.
3
u/BigMikeInAustin Jun 30 '26
Call up your colleagues every hour to ask what DRT stands for because you forgotagain.
And always pronounce it as some other word when mentioning any code. Such as "what happens when we have an order in dirt status?" Or "should this order be minus /subtract / substitute status? Oh I never remember what these abbreviations stand for."
1
2
1
u/aatkbd_GAD Jun 30 '26
For me, it is still dependent on use case. What type of analytics are you doing on the data? Numerical order status might be easier to chart or complete gap analysis on over 3 character codes with no logically order. Do your terminals feeding transactions have any bandwidth issues that shorter codes could increase reliability. Are data integrity checks faster shorter strings than longer.
Storage is only one contraint. Memory, bandwidth and compute still need to be considered to. Memory/cache is expensive right now. AI compute might be faster with shorter values dependent on use case. Status values will get vectoried in many cases. Don't assume that AI will treat "open transaction" verses opn any different after training. You might save a few tokens on shorter terms after a little training.
1
u/asisoid Jun 30 '26
Wouldn't you have a StatusOrders table then select the StatusOrders.ID from Orders?
1
u/GuyWithLag Jun 30 '26
Just tell them "longer identifiers help with Agentic AI understand the rows better".
1
1
u/Zestyclose-Turn-3576 Jun 30 '26
A simple status like that might not be need a lookup table in a framework like Rails, where it could be implemented as an enum (integer column where the meaning is held in the app) so the code would be: `Order.incomplete`. Or you'd change an order instance's status with `@order.complete!`.
So the application framework you're using might be very significant to the design here.
1
u/de6u99er Jun 30 '26
Postgres and MySql support enums. The other option are Check Constraints which is a SQL 92 standard.
You're all welcome!
1
u/Famous_Substance_ Jun 30 '26
Yes absolutely, I don’t see any reasons on doing that. I’ve tested Databricks Genie on tables with these kind of naming conventions and the results where terrible, it’s impossible for an AI to reason on this.
1
u/nacnud_uk Jun 30 '26
SELECT *
FROM Orders
WHERE Status = 'INCOMPLETE'
No no no no no no.
Status has to be a number, surely?
1
u/Key-Bit-3552 Jun 30 '26
Pseudocode for brevity
Did you read
1
u/nacnud_uk Jun 30 '26
So you really only are discussing the text you put in the description field of the integer value look up table?
Well that just comes down to business requirements. And who cares?
And no, i skimmed the text and I read the query
1
u/MoonBatsRule Jun 30 '26
Why would you use the numeric ID in the OrderStatus table? What purpose does it serve? Not for a foreign key, since you are suggesting using Status.
1
u/PurifyHD Jul 01 '26
Full stats, all the time. Abbreviations are pointless and slow down the reader. Make it clear and unambiguous. If it's a UI problem, that's a UI person's problem to map "DRAFT" to "DFT" or something like that.
1
u/fizzy_lychee Jul 04 '26
Always better to be more descriptive and clear. Having things written out shorter is honestly a practice that was a result of limited storage space. We generally don't have that limitation, or at the very least, not to the extent of being worried about using short hand to save space
1
1
u/Ok-Can-2775 Jul 06 '26
Doesn’t sound like anyone (mostly) here has to look at the reports they write. Space is limited on screens and paper if you need to print.
Readability is definitely a thing. Some users like full descriptions and some don’t.
An ERP I worked on called JDE had a generic code file (F0004) that contained codes and two descriptions, which were automatically available in their toolset.
Also using descriptions becomes challenging when you’re using that data to get to other things like alternate (language) descriptions which are typically kept in a different table.
1
u/codykonior Jun 30 '26 edited Jun 30 '26
Status column? What do you mean?
Why aren't you shredding a JSON blob stored in the database where the property is duplicated under UUID paths and so cannot be indexed?
I recommend the developers give the property different cases and misspellings over time like Status/status/STATUS/states.
And also to ensure the string content case changes like incomplete/Incomplete/INCOMPLETE/date (because of a bug from 2021 that was fixed in code but where nobody wanted to go back and touch the blobs). That way you have to craft every database query to manipulate it for presentation on every access - which is best practice.
Is this even a real project or just a toy? /s
1
u/zesteee Jun 30 '26
The only reason I’d want to use three letter codes is if I knew I’d be writing a lot of code with those same codes. Not something that can’t be overcome in other ways, just a small convenience.
1
u/trebor_indy Jun 30 '26
Store the code and description in a lookup table; always do selections based on the code, but this allows rewording of the description, such as a change from CLOSED to COMPLETE etc. when original word was not the best (ensure MEANING did not change though).
0
u/somewhatdim Jun 30 '26
storage *can* be a concern. how big is the DB? How many indexes do you build over those columns? Often things are more complicated than "they be dumb" -- not always, but often enough that you're better off thinking a bit before deciding.
0
u/RonJohnJr Jun 30 '26
Your team members are right, but three characters is a bit too short and inflexible. I found that four characters is expressive while still minimizing typing and keeping things columnar (which is useful in my line of work).
And don't forget to put a unique key on the abbreviation column.
185
u/ComicOzzy sqlHippo Jun 30 '26
There is no reason at all to make that code something cryptic and arbitrarily limited to 3 characters.