Optional oneToOne relations and criteria field

Posted on

Question :

Visitors can fill reports on my website.
Those report can either be filled by a legalPerson or a naturalPerson.

Constraint: I don’t like empty fields and I would like to normalize as much as possible my database.
Hence, I have those relations:

Database model

Question: For these kind of optional relations, should I add the field notifier_type (we could call it a criteria field) in Report Object? I mean I can obtain a close result with:

if($this->getNaturalPerson) { 
    $notifierType = 'naturalPerson';
} ...

The only drawback would be that I can’t query on notifier_type if it’s not in DB.

What is the best practice here?

Answer :

I’d put person_id as second field of Report and get rid of notifier_type. Then create a relation persons (persona?) with id field, then make foreign key from Report.person_id to persons.id.

This relation persons (persona?) would have all of fields from both your legal- and naturalPerson relations, plus one additional – person_type. You would have NULLs when a field would be not applicable for given type of person (namely legal_Statut would be NULL for natural person and birthday would be NULL for legal person).

I would then exclude headquarters_address and home_address and make additional relation addresses with person_id field – again, foreign key to persons.id. You would then have a possibility of assigning multiple addresses to persons, mark them as current or previous, whatever you wish. That’s what I would do.

EDIT: Visualization

enter image description here

Leave a Reply

Your email address will not be published. Required fields are marked *