Does it violate any normalization rules if I add a boolean column to a db table?

Question

Database tables:

Servers

Id	ServerType
ServerId1	1
ServerId2	2
ServerId3	3
ServerId4	4
ServerId5	5
ServerId6	1
ServerId7	2
ServerId8	3
ServerId9	4
ServerId10	5
ServerId11	1
ServerId12	2
ServerId13	3

ServerType

Id	ServerType
1	Developer
2	Basic
3	Standard
4	Advanced
5	Premium

[dbo].[Servers].[ServerTypeId] is a foreign key to the column [dbo].[ServerTypes].[Id]

I have a requirement to set the priority server for a serverType.

So I added a column IsPriority to Servers:

ALTER TABLE [dbo].[Servers]
ADD IsPriority BIT NOT NULL DEFAULT 0;

Does that violate any normalization rules?

Should I create a new table PriorityServers and add relations to the Server and ServerType table?

Answer 1

Techincally speaking, proper database normalization would ask for a separate table. But enforcing uniqueness would get complicated, requiring two unique keys and two foreign keys.

Your existing solution is much easier. You just need to enforce that only one Server of each ServerType can exist at once. You can do this using a filtered unique index.

CREATE UNIQUE INDEX UQ_Server_ServerType ON dbo.Server
  (ServerType) INCLUDE (Id)
WHERE (IsPriority = 1);

Answer 2

If IsPriority is a server attribute, then adding it does not violate normalization rules, because it's atomic and there is no transitive functional dependencies, i.e., FDs where the source is not the primary key.

You can enforce your only 1 prio per server type via a trigger or application code.

Alternatively you can add the IsPriority field to your server type table instead of the server table, but the problem would be that it would be less intuitive for humans and you would always need to join to server type in order to find out whether a given server is priority. It is doable that way as well though.