Say I have a table
entities (subclassed by
organizations tables), where each entry requires a
entities table looks like this:
entities ======== id INT AUTO_INCREMENT full_name VARCHAR(255) primary_email_id primary_address_id primary_tel_id
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 ================== 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
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
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
contacts and the
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?