CODEWITHSUNDEEP
database-designconstants
Jonn
Jonn

Reputation: 4661

Where Constants should be stored in the database

I've been wondering whether constants like "Approved, disapproved, pending" or "Single, married, divorced" should be grouped up in a single table holding references to what module they are used at in the database or into separate tables for each relevant module. Which one is more practical?

Upvotes: 0

Views: 5202

Answers (6)

Harsh Varde
Harsh Varde

Reputation: 144

It's depends on how many type of constants you required in database.

  1. if its only 1 or 2 tables then single or separate table is fine
  2. Suppose if you need many different types of constants then instead of creating 20-30 tables for each type, you can follow structure like below:

TermSet and Term Structure

Here, you can maintain multiple type of constants in single table with mapping those with it's type.

Also you can create Foreign Key constraint on MaritalStatusID and LeavesStatusID on [User] table by referencing ID column in [Term] table.

Upvotes: 0

Ray
Ray

Reputation: 21905

I agree with mnemosyn - whenever possible I would enforce these things which cannot be modified by users with constraints rather than foreign keys.

However, one wrinkle would be if you need to include user-friendly names in queries for reporting, BI, or ad-hoc users. Then a foreign key table would be pretty handy.

Upvotes: 2

Mark
Mark

Reputation: 2952

You shouldn't be storing constants in a database in my opinion, keep them in the code.

Both examples you gave I would store as ENUM's in the database.

Upvotes: 3

Greg Shackles
Greg Shackles

Reputation: 10139

They should go in separate tables, so you can make use of foreign keys. For example, let's say you're talking about a Users table here, designed as such:

UserId           int
Status           int
MaritalStatus    int

You could define a UserStatuses table

StatusId    int
Name        nvarchar

with the rows for Approved, Disapproved, and Pending, and then do the same approach for UserMaritalStatuses. This also maps nicely to making the same constants in code for when you're referencing these tables.

Upvotes: 1

mnemosyn
mnemosyn

Reputation: 46301

How 'constant' are these constants? I'm currently storing constants in code, since they can, by definition, not change. If it's not user-configured, don't put it in the DB.

Having a zillion foreign key constraints of that type in the DB is useless and will make your performance suffer badly, if that is of any concern.

But I know my opinion on this is rather rarely shared.

Upvotes: 3

Philip Fourie
Philip Fourie

Reputation: 116857

Separate tables in order for the database to enforce foreign key constraints that only applies for the referencing table.

Upvotes: 9

Related Questions