0

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?

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

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.

| improve this answer | |
  • 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. – Kenneth Fisher Nov 5 '14 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. – Neil McGuigan Nov 5 '14 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. – Kenneth Fisher Nov 5 '14 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. – AmadeusDrZaius Nov 5 '14 at 16:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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