Academic question about table design

Question

I just had an argument with my professor about surrogate key usage in my project's database design. My professor insisted that a primary key (natural or surrogate) should not ever be exposed, even in the URL, and using an auto-increment as primary key is a bad practice because something like german-tank problem.

I argued that the table, for example, USER table, doesn't have natural key by design because it doesn't store unique stuff like email, and using it in the URL should be okay (is this considered "exposing"?) because there is authorization step to check if the user is authorized to access the resource. I also argued that using auto-increment should be okay for a project at a scale that won't need databases merger (which usually when auto-increment can be a problem). Even big software like Oracle use auto-increment (sequence).

But my professor won't acknowledge it, and even bring column naming to the argument. My design is that the name of column ID of a table is just ID and not [tablename]_ID because it should be clear that column is the ID of the table. So for example if I had a table called USERS with ID column, and a table called PROFILES with USER_ID column, it should be clear that USER_ID is related to USERS.ID (I presented the ERD drawing). But my professor insisted that I should use [tablename]_ID, which I didn't even know why anymore because my professor just keep saying that the people who see the design won't know which ID is for which table. Isn't that the point of ERD drawing?

I'm quite bothered with it, so is there any "academically" or "practical" reason for why my design "should" be changed? I feel that my professor only arguing using his/her own knowledge/experience because his/her reason doesn't quite clicked with me.

Edit : Thank you everyone for all the input. I'll learn more how to workaround that exposed ID on URL, and I have to agree using only ID as column name can be confusing for a lot of people.

Answer 1

I see two independent questions:

1) Is it OK to expose an auto-incremented key in URL's

2) Should primary key columns be named ID or <TableName>_ID

I don't think these two questions are related in any way.

Ad 1: Your professor have a good point. If you are a business, your competitors will be able to see your total number of users just by signing up, and watch the growth by signing up multiple times with a certain interval. You almost certainly don't want that. In some cases it matter, in other cases it doesn't matter.

Ad 2: This is mostly a question of conventions, and I don't see any significant argument either way. Most important is to be consistent. I believe some old databases had the requirement that column names was unique across all tables, which is the origin of the convention of prefixing all column names with the table name. But that constraint does not exist anymore, so following it for that reason would be cargo culting.

Answer 2

The question is comprised of several related (but ultimately separate) questions.

using it in the URL should be okay (is this considered "exposing"?)

"Exposing" is defined as "letting the outside world know". A URL is specifically to be used by the outside world to access your resource, so that is indeed exposing the value.

The main takeaway from exposing a value is that you require your consumer to know the value, and when your consumer knows this value, you can't just up and change it without having to coordinate that with the consumer. That can be a cumbersome process and thus should be avoided.

But my professor insisted that I should use [tablename]_ID, which I didn't even know why anymore because my professor just keep saying that the people who see the design won't know which ID is for which table. Isn't that the point of ERD drawing?

For an enterprise grade application, you're not going to be able to remember every field and its name and purpose, nor is it going to be easy to fleece through the ERD every time you want to use the name of a field/column.

Think of it this way: just because you have a thesaurus and dictionary by your side doesn't mean that every sentence I write (conveying the same message) will be exactly as easy to understand as every other sentence I could've written. It's still a lot more efficient if I use language that is immediately understandable without requiring you to dig into the documentation (thesaurus/dictionary).

To the same effect, using [table name]_[PK column name] as the FK column name immediately telegraphs which PK this FK is referencing.
Note that I'm ignoring the underscore. It's a holy war that I don't want to stake a claim in. I tend not to use underscores but your mileage may vary.

That's not a hard and fast rule, but it's a convention that makes developers' lives significantly easier.

using an auto-increment as primary key is a bad practice because something like german-tank problem

You're not wrong, but the german-tank problem isn't particularly relevant for your average application. And it can still be easily avoided using a non-sequential type such as a GUID - which is advisable over an integer for several reasons, e.g. avoiding collisions in distributed systems. You did already touch on this:

I also argued that using auto-increment should be okay for a project at a scale that won't need databases merger (which usually when auto-increment can be a problem)

...but you've glossed over the part where you can't know for a fact that just because an application doesn't run distributed today, it's not going to run distributed tomorrow.

If you already use GUIDs from the get go, you application can be scaled much more efficiently than if you started using ints and now have to refactor the codebase to account for the collisions from your distributed system.

Software development entails making reasonable judgment calls about the possibility of something changing and the cost (effort/time/technical debt) of accounting for it (or ignoring it). The ability to do so comes with experience, and based on you glossing over this I would make an educated guess that you don't have a lot of practical experience in a non-academic environment yet.

My professor insisted that a primary key (natural or surrogate) should not ever be exposed, even in the URL

Yes, though it does bear mentioning that the scope of an application matters a lot here. Effort of implementation is always something that needs to be considered, and whether a high-effort implementation is worth implementing very much depends on the context of the application you're going to be implementing it in.

But it makes sense. Suppose that you have a web shop which has a history of orders. You've told your customer that their order number is 12345. That reference number shouldn't change, ever. Supposing you drop and recreate your database (for whatever reason), then the PK may be different, but the order number still shouldn't have changed (because otherwise your customer can't refer to the order anymore). The only logical conclusion here is that the order number is not the PK (and vice versa).

That being said, it's perfectly possible that in some systems, even though you've separated PK and identifier, that these two will always contain the same value simply because you haven't (yet) hit on an event that would cause these two values to go out of sync when adding new entries.

I argued that the table, for example, USER table, doesn't have natural key by design because it doesn't store unique stuff like email, and using it in the URL should be okay (is this considered "exposing"?) because there is authorization step to check if the user is authorized to access the resource.

Authorization is not the same as authentication!

• Authentication = who is this? It's Bob.
• Authorization = is Bob allowed to see this data? Yes.

Authorization can only occur after authentication, but that doesn't mean that you should lump them into a single blob, which is what your "because there is authorization step" comment is suggesting you're doing right now.

To authenticate a user, you must first identify them, and to identify them, you must be able to refer to the user you claim to identify as. In short: it all starts by referencing a user.

The same logic applies as above with the web shop example: if you refer to a user by their PK, then if you drop and recreate the database, everyone's PK may have changed, and that's going to be a costly affair to fix.

Even big software like Oracle use auto-increment (sequence).

Do not assume that just because something exists, it's therefore good. Especially in the field of enterprise software, backwards compatibility is a main feature (and Oracle especially touts their backward compatibility). This means that you're also going to see outdated features because they are kept in for compatibility reasons.

I'm not saying that int PKs are wholly outdated, but I am countering your suggesting that just because it exists it must mean that it's the right way to go.

Answer 3

It sounds like your professor is so used to teaching people about certain potential problems that he's forgotten about practicality. You should do what works, and be prepared to change your mind if you find something better. That said, you need to be aware of all the potential problems that he needs to teach you.

In this case, does it matter if someone can estimate the total number of users from an ID (especially as many companies explicitly give that information away in their marketing)?

An ID in a URL can be terrible if a hacker can just change it to impersonate someone else, but it's not intrinsically bad. There are times where an auto-incrementing ID can cause problems, but there are also plenty of times where they are perfectly acceptable. This page has an ID in the URL, which appears to be auto-incrementing. Amazon, eBay and YouTube (probably) have non-auto incrementing IDs because the scale of the system requires it.

Personally I like ID fields to be called the same in all tables. There's no practical difference between specifying the table explicitly (Users.ID) and implicitly (User_ID) in your SQL - however it's not a scalable naming scheme. Your Book object will now have to have a Author_User_ID field, instead of just AuthorID and you'll end up with fields called User_Report_Expression_Condition_TimeLimit_Units and eventually hit a name length limit.

Also, if many of your classes have fields with the same name, you are starting to discover an interface that may be of use.

However, you should to stick to the coding standards of your organisation even when you disagree with them. In this case your professor probably just wants to you to demonstrate that you've understood the things you've been taught. You'll often find yourself in a disagreement about style where you're just arguing about what might happen later but it doesn't really matter. Make your point and move on, and maybe one day you'll be able to say "I told you so" but save your energy for things that really matter.

Answer 4

Some of these things are personal preference. Personally, I think naming a primary key id makes it easier to find, especially in a table that might contain several foreign keys. However, if I were working in a group that felt otherwise, I would conform to the group. You write software for others to read, not just yourself.

As far as security-related design decisions, they can seem a little superstitious at times, but you can't predict how the software will change in the future. You don't just want to design it so it's safe today. You want to design it so it's safe when someone who is not familiar with your reasoning makes a seemingly unrelated change, or uses your code as an example of what to do in another context. That person can even be you six months down the road.

That's why generally the burden of proof is on the person who wants to do a less secure option. You should prove why you need an autoincrement key or exposing a key in the url now. If you can't, you should go with the more secure future-proof option. It feels like more work because you didn't think of it and because it requires an additional layer or two of abstraction, but it's a lot less work than changing it down the road.

Think of security in terms of layers. What you are working on may not be the first layer an attacker has to go through, but can be critical in minimizing the impact of an attack if those other layers are compromised.

Answer 5

On the consecutive ids. Does it expose any sensitive information? Can a third party gain intelligence by running a script against each id in order? Those are the concerns.

If there is no sensitive information exposed there - where sensitive mean anything that could be used to harm - it is probably not a problem.

On a security audit, you will not have to justify why is it OK to have URLs with consecutive ids in them (and why it will never be a problem in the future when new fields are added), if you do not have URLs with consecutive ids.

It is good practice to not do it. And why not teach the good practice?

Consecutive ids have other potential problems, which might vary from engine to engine. For example, they depend on database engine specific means to retrieve the last inserted id, you may run into a maximum value for the consecutive, you may have todo extra steps to ensure the auto-increment works correctly when moving the database to a different environment, and they may cause performance issues.

---

About the field names, I had that argument with my teachers too… Eventually I got to understand the problem: joins. So, you join to tables, and then filter the results by some predicate where you use their fields. It is very easy to forget to specify the table, and if there are fields that are called the same…

If your database engine (or static checking) complains and forces you into specifying the table name, that is a non-issue. On the other hand, if it will pick one (say the left side of the join) then you have a problem※. Your code is fragile, it might appear to be working, yet it was only luck, and a bug will appear with more data, or on a refactor where you change the join, or something like that.

※: You have two problems. You must be using some very old or very obscure engine, that does not report this as an error. Use something better.

Please note I'm not arguing for the names of the fields in a foreign key to match. Also note that not every system have an - up to date - diagram to look at.

Answer 6

There's a broad mix of advice here and I think you are asking good questions that deserve a little more detail. Let's start off here:

My professor insisted that a primary key (natural or surrogate) should not ever be exposed

This is really good advice and you should follow this. Other answers elaborate on this so I won't go into depth but when you expose your keys, you make other people, systems, and/or organizations care about them. This severely limits your flexibility around managing your database. Keep the ids you tell people about separate from the canonical ids.

using an auto-increment as primary key is a bad practice is a bad practice because something like german-tank problem.

This is where things get kind of iffy for me. If you are not exposing your keys or supporting access to records using them, whether they are sequential or no more relevant than their physical location on disk with regard to this kind of system. If you are using sequential keys, this problem alone is a good enough reason to not expose them.

I have a few qualms about the advice that GUIDs (UUIDs) should be used as primary keys. First off, it's important to understand that all UUIDs are not created equal. There are 4/5 different types of UUID and only one of them is purely random. If you are using one of the other types, you are going to have a lot of ids that start and end with similar values but the middle part of the id is very different. Combined with the overly wide space, this is problematic for common indexing strategies. If you are using version 4 UUIds, that doesn't necessarily mean you are in the clear. I'm not an expert in this area but there's a lot of controversy around this. Here's one such debate.

A common refrain about using random UUIDs is that they cause massive index fragmentation and larger index sizes. Most cite more than 99% fragmentation. Even the answer in the link above that advocates their use (under certain constraints) asserts that this is the case.

Here's a (rather old) article on the subject: GUID vs INT debate. This has some specifics about a number of performance and space penalties associated with using random UUIDs and a few potential benefits.

I'm not going to suggest that random GUIDs never be used as primary keys but I would be hesitant to start using them across the board without some discussions with a DBA (or database expert) about the implications.

One note about the concerns related to merging databases. This isn't really a big problem if you've kept your primary keys private: take the larger database and find the largest key. Add that to the keys from the smaller database and collisions are eliminated. There are many other similar approaches that can be used to avoid collisions across multiple databases without using a random 16 byte value.

Lastly with regard to naming: I personally prefer the approach of having every table use 'id' or 'key' as the primary key and using the table name to define the foreign key (e.g. 'foo_id'). However, this approach is really uncommon and people are adamantly against it for reasons I don't understand. The argument that no one will know what table it refers to is silly IMO. If all tables have 'id' as the key, we don't need id column name to tell me what table it belongs to, I just need to know what table we are talking about. I know what the pk is for any table: it's 'id'. In any event, redundant naming in databases is a religion and it's probably not worth fighting over. Your futile resistance against the department of redundancy department is futile resistance.

Answer 7

We do not choose column names for the benefit of the computer. The computer does not care one way or another. We choose names to make life easier for humans, both ourselves now and those who have to read our code in the future. In my experience life is easier with the primary keys named [tablename]_id.

In the example you give PROFILE.USER_ID = USER.ID it seems to me self-evident that either naming convention will produce a intelligible query. But this is a simple clause and easy things are easy after all. How does it fare in a complex scenario?

When tables contain multiple FKs referencing the same PK the referenced table must be aliased. These aliases should reflect the purpose of the table within the query but often do not. Moreover aliases are scoped to the query so it is more likely the chosen alias will change from query to query, developer to developer. So the obvious code PROFILE.USER_ID = USER.ID morphs into P.USER_ID = U.ID. We assume 'U' references the table 'USER' but this isn't automatically obvious, and cross-referencing is increasingly awkward as the query size increases. Without the strict naming conventions and documentation of the data model the tacit understanding of what aliases represent is diminished making the query more difficult to comprehend.

With multiple FKs the opportunity for confusion increases. For example ABC.EDITOR_ID = DEF.ID AND ABC.PRESENTER_ID = GHI.ID. To understand the code we can look at the documentation (hoping it's up-to-date) and see that EDITOR_ID and PRESENTER_ID both reference USER.ID. The code would be more self-documenting if that PK had been named USER.USER_ID. ABC.EDITOR_ID = DEF.USER_ID AND ABC.PRESENTER_ID = GHI.USER_ID. (Yes, those are poor alias names, but that's my point.)

With every table having a column "ID" it is impossible to sanity-check code using "ID" by sight-reading. The aliases have to be traced back, perhaps through nested sub-queries and CTEs. This is tricky.

Neither can the compiler detect incorrect aliases because every table contains ID. Say alias GHI actually references the table PAYMENT. The above is a logic error but not a syntax error. However the equivalent with full names is easier to detect:

ABC.PRESENTER_ID = GHI.USER_ID  -- compile error
                                -- GHI references PAYMENT, likely doesn't have column USER_ID

ABC.PRESENTER_ID = GHI.PAYMENT_ID  -- code review error, easy to see the discrepancy