Database design : Could I make a column in a table only allows one 'true' for some specific rows and others false for same specific rows

Question

Problem statement - [Business] 1---* [Branch] A business must have one or more branch(es). Only one branch can be main branch.

I am have two design in my mind

1. [Branch table] - {id, BusinessID (FK), Name, etc , IsMainBranch}
2. [Branch table] - {id, BusinessID (FK), Name, etc }, [MainBranch table] - {BranchID (PK, FK), BusinessID (FK)}.
3. [Business table] - {id, name, mainbranchid}, [Branch table] - {id, BusinessID (FK), Name, etc}

Problem with #1 - To put constrain on IsMainBranch I have to use triggers, which can go out of sync latter.

Problem with #2 - Bit complicated to access the data in EF.

Problem with #3 - Don't think its a good design.

I am using EF 4.1 as ORM and don't want to make things complicated, which design I should choose. Please suggest if there is a better/alternate way available for the same.

Answer 1

With SQL Server 2008+ you can use filtered indexes

In your Branch table, just have a flag IsMainBranch. Then create a unique filtered index: this will allow one row only where IsMainBranch= 1 per Business.

Branch table:

id (PK)
BusinessID (FK)
Name
...
IsMainBranch 

Then

CREATE UNIQUE INDEX IX_MainBranch 
        ON Branch(BusinessID) 
        WHERE IsMainBranch = 1;

This gives

• simpler table structure
• no triggers
• the DB engine enforces this for you
• no circular FKs (no MainBranchID in the Business table)

Also see:

• The proper way to implement unique constraint that allows multiple NULL values in SQL Server
• SQL Server bit column constraint, 1 row = 1, all others 0

Answer 2

Each "satellite" branch will have a main branch, correct? So why not have a field "main branch". If branch id == main branch, then it is a main branch. Otherwise, it refers to the main branch.

You should not have an "isMainbranch" column.

"Branch type" column might be OK: types might include "main", "satellite", "ATM kiosk", etc. But a boolean "isMainBranch"? No.

IMHO...

Answer 3

I would go for number 3, create a mainBranchId in the business tables.

1) this will ensure that at any given time there is at most one main branch per business. (this might happen in the other 2 designs)

2) you won't need to create an extra table that is a replication of the branch table (this is what you would be doing in the 2nd design)