3

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.

  • 8
    I think what you want could be better achieved using a GroupPermission mapping table which maps Group to Permission. The meaning of a magical number in an identity column will be soon forgotten. – Dan Wilson Oct 5 '18 at 20:39
  • I think the important thing here is to make the concept of GroupPermission explicit. The concept doesn't have to live in the database. If the "Admin" group has this permission, it doesn't matter what the ID is. You can build the mapping of its ID to the GroupPermission "view all" in the database or the codebase. And yes, magic numbers are frowned upon :-) – bitsoflogic Oct 5 '18 at 20:59
  • Agree with Dan, plus all too often business rules such as 'users will only belong to one group' have a tendency to change. Better to have a mapping. – GrandmasterB Oct 5 '18 at 21:00
  • 1
    The -1 solution is sort of "hacking" your own system. The identity now has 2 meanings. You will have an if condition everywhere in your system to give this special treatment. Not a good idea. – CodingYoshi Oct 7 '18 at 21:09
  • 1
    Not an actual answer, but tangentially addressing the "in SQL" part of your question: The database's responsibility is to store the values it is given, not question those values (unless you explicitly decide to validate your data here - which I advise against). The meaning of those values are not a database responsibility, that up to your business logic. – Flater Oct 8 '18 at 7:05
5

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.
  • As dumb luck would have it...I just ran into a very similar problem today whereby someone was using -1, in a legacy application, for a 'special' case and the associated table didn't have a -1 entry in it (DB migration?) -- 25 minutes later, I found the single place where the -1 is inserted/used and then 30 minutes more to figure out how everything else handled that scenario (since it was not clear throughout the app the ID could be -1). An hour of my time was wasted to figure out something from an improperly designed & implemented model. PS Dunno why stylistic edits to the answer were made... – B1313 Oct 15 '18 at 22:59
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.