Is using -1 as a special Id a good idea in SQL?

Question

I'm trying to determine if using '-1' in an Identity column has any drawbacks, or if there is a better way to do what I want.

In my database I have User table and a Group table. All users must belong to only one group. Table definitions look something like this.

CREATE TABLE User
(
    UserId int Identity(1,1) not null primary key,
    GroupId int not null -- FK to group
    /* More columns */
)

CREATE TABLE Group
(
    GroupId int Identity(1,1) not null primary key,
    /* More Columns */
)

When the user logs into the application they are able to see info about the group they belong to. Now we want to add a special group where the users are able to view (and not edit) the data for all of the other groups. My team has already decided that users should belong to a special group to receive this functionality. I'm wondering if it is a good idea to denote this group as special by setting its GroupId to 1.

Answer 1

You're indeed correct to deduce this is a very bad idea (indicative of design smell). Using "-1" as a "special" ID/Primary Key is horrible and should never be used in any legitimate or production database. Implementing such a practice means you (or your team) have a design problem that you don't want to address correctly and fully.

I do think there is much value in reading about good database design and data modeling; especially the immense value of critically thinking through the design framework about why this would indeed be a very bad idea to implement once the aforementioned reading has been done.

Here are some general bullets points though to justify my answer more completely (though proper reading and research is strenuously recommended when it comes to data modeling and good database design):

• We're now establishing "magic" numbers in a database versus developing a full solution using proper tables and relationships
• We're probably not documenting said "magic" numbers anywhere (or very unlikely to read or notice it in the future) or updating said documentation regarding it (including in the application using said DB)
• What happens when another "special" use-case is created? Do we use -2? Then -3, -4, ... We're opening a can of worms and diving into a rabbit hole that will eventually evolve into too much pain to fix
• We're overriding a fundamental DB mechanism for a quick-fix to a domain / data modeling design issue
• We're potentially interfering with any reports that might be written (it's not unheard of in Reporting land to use -1 to create a "Select All" option for a parameter that cannot utilize the multi-value option, it happens rarely but it does happen) already or in the future (and the reports' meaning of "-1" for the table will be inconsistent with the actual meaning of the "-1" key in the DB)
• When senior DBAs join the team and want to fix the problem correctly, we're probably going to have some issues between our DBA's and developers because of the work required to change a (what will hopefully only be 1) "magic" number.

Answer 2

I think you need to separate "groups" from "permissions" completely. What you seem to be after is role and permission based authorization.

• A user has many roles
• A role has many permissions
• There should be 1 permission for each action a user can perform on each entity in the system, be it a read, write or delete.

Right now they might say that a user performs only one kind of role in the system. These things change all to often. Users can play many roles in your system, especially after you add additional modules.

Since a user can play many roles in the system, a user record should be associated with multiple roles. Perhaps right now the standard is just 1 role per user. That's ok. Model the database for multiple roles per user anyhow. It's not much extra work.

Furthermore, the "role" a user plays translates to multiple actions, and as such a role should be associated with multiple permissions.

At the foundation of all this is the "permission." Create 1 permission for each action a user can take.

This triplicate combination of "has many" relationships gives you some terrific flexibility. You can refactor the authorization in the system by adding or removing permissions from a single role, and presto! Users updated, by virtue of the fact they are associated with that role, and the permissions a user has are derived from the roles they have. In the data modeling world it would be known as normalization.

I've found authorization to be one of the frequent exceptions to YAGNI (Ya Ain't Gonna Need It). You already have a need for two roles. When the business finds out how easy it is to create roles, they will come up with more. And even if creating a role is made difficult, they will come up with more, since business needs constantly change.