Search code examples
mysqldatabase-designprimary-keyfallback

Is Database primary key 0 as fallback allowed or recommended?


I want to use a Primary Key = 0 as fallback for my Application.
Where id=0 means to use System Default, and all above are user defined Entrys. There is no user-access to id 0 and no possibilities for deletions in that Table.
Is it a maybe rare but usual way or should I better never do that.

Edit:
For usages like:
SELECT...FROM...WHERE IF(id=0, system_col, other_col)


Solution

  • There are a couple of aspects to this. It's not a "good" versus "bad" decision - it depends (as usual).

    Your design is conceptually similar to the null object pattern in object oriented design. By introducing a "default", you can simplify the code - instead of always checking for "null", you can guarantee that there's a record, and treat it like any other record for data retrieval purposes. You might also get some performance benefits by not having to check for null (though I doubt this will be a major impact).

    On the other hand, you are introducing a magic number - generally considered to be a bad thing, as developers have to know that records with id 0 are "special", in a way that is unique to your application (as opposed to null, which is a well-established concept). This is especially nasty if the concept spreads across layers of the application - from the database to the business logic, or even into the presentation layer, for instance.