Is using JOINs to avoid numerical IDs a bad thing?

Question

Yesterday I was looking at queries like this:

SELECT <some fields>
FROM Thing
WHERE thing_type_id = 4

... and couldn't but think this was very "readable". What's '4'? What does it mean? I did the same thing in coding languages before but now I would use constants for this, turning the 4 in a THING_TYPE_AVAILABLE or some such name. No arcane number with no meaning anymore!

I asked about this on here and got answers as to how to achieve this in SQL.

I'm mostly partial to using JOINS with existing type tables where you have an ID and a Code, with other solutions possibly of use when there are no such tables (not every database is perfect...)

SELECT thing_id
FROM Thing
JOIN ThingType USING (thing_type_id)
WHERE thing_type_code IN ('OPENED', 'ONHOLD')

So I started using this on a query or two and my colleagues were soon upon me: "hey, you have literal codes in the query!" "Um, you know, we usually go with pks for that".

While I can understand that this method is not the usual method (hey, it wasn't for me either until now), is it really so bad?

What are the pros and cons of doing things this way? My main goal was readability, but I'm worried about performance and would like to confirm whether the idea is sound or not.

EDIT: Note that I'm not talking about PL/SQL but straight-up queries, the kind that usually starts with a SELECT.

EDIT 2: To further clarify my situation with fake (but structurally similar) examples, here are the tables I have:

Thing
------------------------------------------
thing_id | <attributes...> | thing_type_id
    1                              3
    4                              7
    5                              3

ThingType
--------------------------------------------------
thing_type_id | thing_type_code | <attributes...>
       3           'TYPE_C'         
       5           'TYPE_E'
       7           'TYPE_G'

thing_type_code is just as unique as thing_type_id. It is currently also used as a display string, which is a mistake in my opinion, but would be easily fixable by adding a thing_type_label field duplicating thing_type_code for now, and changeable at any time later on if needed.

Supposedly, filtering with thing_type_code = 'TYPE_C', I'm sure to get that one line which happens to be thing_type_id = 3. Joins can (and quite probably should) still be done with the numerical IDs.

Answer 1

Primary key values should not be coded as literals in queries.

The reasons are:

• Relational theory says that PKs should not convey any meaning. Not even a specific identity. They should be strictly row identifiers and not relied upon to be a specific value
• Due to operational reasons, PKs are often different in different environments (like dev, qa and prod), even for "lookup" tables

For these reasons, coding literal IDs in queries is brittle.

Coding data literals like 'OPENED' and 'ONHOLD' is GOOD practice, because these values are going to be consistent across all servers and environments. If they do change, changing queries to be in sync will be part of the change script.

Answer 2

I assume that the question is about the two versions of the query -- one with the numeric comparison and the other with the join and string comparison.

Your colleagues are correct that the form with where thing_id in (list of ids) will perform better than the join. The difference in performance, however, might be quite minor if thing_id is not indexed. The query will already require a full table scan on the original table.

In most other respects, your version with the join is better. In particular, it makes the intent of the query cleaner and overall make the query more maintainable. For a small reference table, the performance hit may not be noticeable. In fact, in some databases, this form could be faster. This would occur when the in is evaluated as a series of or expressions. If the list is long, it might be faster to do an index lookup.

There is one downside to the join approach. If the values in the columns change, then the code also needs to be changed. I wouldn't be surprised if your colleague who suggests using primary keys has had this experience. S/he is working on an application and builds it using joins. Great. Lots of code. All clear. All maintainable. Then every week, the users decide to change the definitions of the codes. That can make almost any sane person prefer primary keys over using the reference table.

Answer 3

See Mark comment. I assume you are ok but can give my 2 cents on matter. If that value is in the scope of one query I like to write that this, readable, way:

declare HOLD int = 4

SELECT <some fields>
FROM Thing
WHERE thing_type_id = HOLD

If that values are used many times in many points (queries, SP, views, etc) I create a domain table.

create table ThingType (id int not null primary key, varchar(50) description)
GO
insert into ThingType values (4,'HOLD'),(5, 'ONHOLD')
GO

that way i can reuse that types on my selects as an enumerator

declare TYPE int
set TYPE = (select id from ThingType where description = 'HOLD')

SELECT <some fields>
FROM Thing
WHERE thing_type_id = TYPE

that way I keep meaning and performance (and also can enforce relational integrity over domain values)

Also I can just use enumerator at app level and just pass numeric values to the queries. A quick glimpse in that enumerator ill give me that number meaning.

Answer 4

In SQL queries you will definitely introduce a performance hit for JOINs (effectively multiple queries are taking place inside the SQL server). The question is whether the performance hit is significant enough to offset the benefits.

If it's just a readability thing then you may prefer to go for better performance and avoid the JOINs, but I would suggest you take into account potential integrity problems (e.g. what happens if the typed value of 4 in your example is changed by another process further down the line - the entire application may fail).

If the values will NEVER change then use PKs - this is a decision for you as the developer - there is no rule. One options may be best for one query and not for another.

Answer 5

In case of PL/SQL it makes sense to define constants in your package, e.g.

DECLARE
   C_OPENED CONSTANT NUMBER := 3;
   C_ONHOLD CONSTANT NUMBER := 4;
BEGIN
SELECT <some fields>
INTO ...
FROM Thing
WHERE thing_type_id in (C_OPENED, C_ONHOLD);
END;

Sometime it is usefull to create global package (without a body) where all commonly used constants are defined. In case the literal changes, you only have to modify the constant definition at a single place.