0

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:

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?

1
  • The way you have your FKs, a person (normal or legal) can file only one report and never more than one. I doubt that is your intention. Right? Jul 30 '14 at 12:54
1

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

2
  • Thanks for your answser. However your solution doesn't appear 3NF compliant. Each field on Report are not dependent only on the PrimaryKey -> if the legal persons field are n/a, the naturalperson field will be filled and the inverse is true.
    – Xavier13
    Jul 30 '14 at 6:36
  • Look at the picture and tell me what NOT depents on what, because I don't think I understand you, or you don't understand me.
    – Kitet
    Jul 30 '14 at 12:31

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.