Dealing with co-dependent not null columns

dependenciesnormalizationnull

Say I have a table entities (subclassed by people and organizations tables), where each entry requires a primary_email_id. The entities table looks like this:

entities
========
id INT AUTO_INCREMENT
full_name VARCHAR(255)
primary_email_id
primary_address_id
primary_tel_id

This primary_email_id is a foreign key to a table contacts, which is structured like this:

contacts
========
id INT AUTO_INCREMENT
entity_id INT NOT NULL
type VARCHAR(255) NOT NULL -- ex: "tel", "email", "address", "fax", etc.

and has a companion table contact_attributes:

contact_attributes
==================
id INT AUTO_INCREMENT
contact_id INT NOT NULL
name VARCHAR(255) NOT NULL -- ex: "ext" or "username"
value VARCHAR(255) NOT NULL -- ex: "+25" or "coolboss27"

I think it makes sense requiring that the primary_email_id be NOT NULL and that the entity_id in the contacts table be not null, but this co-dependence creates a problem. When creating an entity for the first time, we must have a primary_email_id to put in the row. But to do this, we must create a contact (and contact_attribute of email: ...@...). However to create that contact, we need to have already created the entity we're trying to create. So we are stuck.

I guess that means that I cannot have both the entity_id in contacts and the primary_email_id in entities be NOT NULL, and presumably primary_email_id is the one that should be nullable. This could be partially solved by including the actual primary_email_address instead of an id, but then the table is no longer normalized and I definitely don't want that.

Is there something I'm missing here or is making primary_email_id nullable just a necessary tradeoff?

Best Answer

First, a name like 'entities' is too vague. The common name for org or individual is 'parties'.

Second, people can have zero, one, or many addresses, and can share the same address. It's a many2many relationship.

I would do it like this (using STI here, but break it out to CTI if you like):

PARTIES
id
type (org or individual)
name

ADDRESSES
id
type
address_info...

PARTY_ADDRESSES
party_id
address_id
role (ex 'sales', 'service', 'mobile', 'work', ...)
priority

Your validation rule would be that a party must have an address of type 'email' whose priority is higher than any other of their addresses. Business rules are best enforced via triggers any applicable logic and not your data model.

Related Question