Is it always a terrible idea to store semantic data in IDs?

Question

Here is a couple of (incomplete) database tables that store information about the rooms of a hotel. The information they store is the same, but their design is different:

1. Store floor information in a separate column:

   | id | floor 
   |----|-------
   | 1  | 1 
   | 2  | 1 
   | 3  | 2 
   | 4  | 2 
   

2. Store floor information in IDs.

   | id 
   |-----
   | 101  
   | 102  
   | 201  
   | 202
   

Is it always a terrible idea to store semantic data in IDs the way table 2 does or are there cases where having more expressive IDs is valuable enough to justify it?

Answer 1

There's absolutely nothing wrong in identifying rooms with numbers where the most significant digit identifies the floor. I think every hotel I have ever stayed in does that. I'm sure you will have to have such a room number attribute in your table.

The doubts of yourself and others are due to you calling the room number an "id". Room number is an identifier - presumably it is the only room identifier that really matters - but if that's what your attribute refers to then just call it "roomnumber", not "id".

Answer 2

If you want to use a natural key, then use a natural key. Don't name a natural key id.

If you use a synthetic key, treat it as an arbitrary value that must be unique, but has no other meaning.

Answer 3

Yes, it is always a terrible idea:

• if later on the "value" changes, you would have to change the ID too, which is an even worse idea
• if you find to have to insert data with a value between two existing rows that have numerically adjacent IDs, there isn't room for the new ID
• the purpose of an ID is to uniquely identify the row. If the ID coveys info, you are overloading its role, which in programming is never a good idea
• if you bury info in the ID, you have to then extract it back out again, which takes code everywhere it's used, which means effort and maintenance
• any framework you may use will not be able to make use of your "optimisation"; they will expect values to be in columns, not in a fragment of the ID
• what are you really saving anyway? Disk is cheap and software/hardware is so fast now, the savings are negligible, and would never outweigh the negatives

I've done it myself in the past and have always regretted doing so, because you can't predict the future and the business and data change in ways you can't predict. Locking yourself into any particular view of the world is dangerous. In fact every case I've ever seen this done, it's lead to lots of problems.

KISS

Answer 4

This isn't really about semantics of data, it's about atomicity and whether you'll be violating the 1NF. The question you should ask yourself is:

Should the room number be treated as an atomic piece of data from the data management perspective?

In other words, will you always read from (and write to) the database the room number as a whole (regardless of whether you treat it as a whole in the client code)?

• If yes, you can safely make it a single attribute (and then make it a key or part of a key if that is what's needed). Whether you'll also have a surrogate key is another matter (as touched briefly below).
• If no (for example: because you need to query for floors, or use the floor as a FK etc.), then you have to split it to separate attributes (e.g. floor + room per floor), else you'll be violating the 1NF.

NOTE: I don't know if it's intentional or not, but your scenario (1) doesn't contain enough data to reconstruct the room number, so it models a different domain compared to the scenario (2) that does.

---

BTW, storing semantic data in a key is not at all a bad practice in and of itself. If some attribute or combination of attributes has to be unique, then you must create a key on them, whether they have intrinsic meaning or not. You can't replace that key with a "surrogate" key, you can just add the surrogate (which has its pros and cons, as you can imagine).

Answer 5

Having semantics in the primary key of your table is certainly a bad idea.

The purposes of the primary key in a table are:

• to be unique,
• to be used as an identity of a record.

If you put such semantics in the primary key then you assume that:

• all rooms will have a floor,
• there will not be more than one hotel,
• room numbers cannot have letters in them.

These assumptions will easily fall apart in the future. Then you will need to change the primary keys of existent records - which will make them not to be an identity of the records anymore.

Such logic could be applied to other cases as well. For example you could use bar codes as unique identifier for books, but in the future you may have books without a bar code.

That being said I would even go further and change your original table to have id, room_number and floor. Some hotels don't have room numbers like 1301, 1302 and so on, because of superstitions. But that doesn't mean they don't have a 13th floor.

To finally answer your question - it may not be always a terrible idea, but I can't think of an example where it is not a bad idea.

Answer 6

I say yes, it's pretty much a terrible idea. The idea of an id is that it is a single column unique identifier for a row that potentially substitutes for a multi-column key. If you are calling the column "id" anyone looking at your schema is expecting a primary key. You are also losing semantic value in knowing that that thing is a representation of floor.

If you have no id column, and just call the column "floor" then that is fine. The thing you are concerned about is, if you ever need to locate a specific record, are you able to do it? If your particular application has no need for any further information than floor for lookup, then continue to use "floor" as the column name and don't add an ID column. If however, you have a composite key, like floor, and hotel id, then you may want to add the id column again as a "shortcut" single column key that you can use as a foreign key in other tables.