Flag column or foreign key?

Question

I have ENTERPRISES and DOMAINS table. The property of each enterprise is that it should have a single primary domain, but it can have more than one domain. I have come up with this table structure

+---------------------------------------+
| ENTERPRISES                           |
+----+--------------+-------------------+
| ID | Name         | Primary Domain ID |
+----+--------------+-------------------+
| 1  | Enterprise A | 2                 |
| 2  | Enterprise B | 4                 |
+----+--------------+-------------------+

+---------------------------------------+
| DOMAINS                               |
+----+------------------+---------------+
| ID | Domain Name      | Enterprise ID |
+----+------------------+---------------+
| 1  | ent-a.com        | 1             |
| 2  | enterprise-a.com | 1             |
| 3  | ent-b.com        | 2             |
| 4  | enterprise-b.com | 2             |
+----+------------------+---------------+

My co-worker suggested this alternative structure:

+-------------------+
| ENTERPRISES       |
+----+--------------+
| ID | Name         |
+----+--------------+
| 1  | Enterprise A |
| 2  | Enterprise B |
+----+--------------+

+----------------------------------------------------+
| DOMAINS                                            |
+----+------------------+---------------+------------+
| ID | Domain Name      | Enterprise ID | Is Primary |
+----+------------------+---------------+------------+
| 1  | ent-a.com        | 1             | False      |
| 2  | enterprise-a.com | 1             | True       |
| 3  | ent-b.com        | 2             | False      |
| 4  | enterprise-b.com | 2             | True       |
+----+------------------+---------------+------------+

My question is, which one is more efficient/correct?

Also, in the first example should I use ID for primary domain column or a string value, so ENTERPRISES table does not have a circular dependency on DOMAINS table?

Answer 1

Both are correct. But go for the FK.

The one you suggest has less sparse data, while in the second example you may have 100 domains belonging to the same company, all with IsPrimary set to False and just one domain set to True.

Also, it's easier to enforce exactly one primary domain in the first scenario, while in the second you'll have to write a trigger or a check in your code to see that there is one, and only one, primary domain at all times.

Again, stick to the FK.

Answer 2

Circular references are OK. Circular dependencies are not. As long as Primary Domain ID is nullable, then you're fine. Otherwise you'll have a chicken-or-the-egg scenario, being unable to create a Domain without an Enterprise, but also unable to create an Enterprise without a Primary Domain ID.

I would choose the former (your proposed solution), because you're defining a one-to-one relationship. While the Enterprise->Domain relationship is one-to-many, the Enterprise->Primary Domain relationship is one-to-one.

Answer 3

In the first model you say an Enterprise should have a single primary domain. Expand that a moment and say it will have a single primary domain. At this point you'd be inclined to mark that column as not nullable.

The problem then is you won't be able to insert data since you've created a circular depedancy. You can't insert an enterprise without a domain and you can't insert a domain without an enterprise.

I prefer the first model as it is cleaner and more explicit. Your model enforces that there is a single primary domain where there is nothing in the second model so you'd be forced to enforce this rule using some other mechanism.