Question :
Introduction:
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:
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