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:

full_name VARCHAR(255)

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

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

and has a companion table contact_attributes:

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?

1 Answer 1


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):

type (org or individual)


role (ex 'sales', 'service', 'mobile', 'work', ...)

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.

  • Generally you want to avoid using triggers to enforce business rules if you can at all help it. Use constraints if possible. Foreign keys, check constraints etc. Triggers tend to be slow and are really easy to mess up. Nov 5, 2014 at 3:37
  • @Kenneth_fisher, foreign keys are part of the data model. Check constraints are great if you got em and if single table. Triggers necessary for multi table biz rules. My main point was that data model should be generalized and biz rules should not compromise a good data model. Nov 5, 2014 at 6:54
  • I can certainly agree with that. If you try to build your business rules to tightly into the data model then when your business rules change (which they almost always do) then you're messed over. Nov 5, 2014 at 11:55
  • "Second, people can have zero, one, or many addresses, and can share the same address. It's a many2many relationship." I agree that someone can have multiple addresses, but I don't believe in tying the same address record to multiple people, because if one person moves, you will probably accidentally move both people's records. Your advice about the name "entities" is well taken. Nov 5, 2014 at 16:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.