Sfrgttk

How can I track script which gives me "command not found" right after the login?

What do Xenomorphs eat in the Alien series?

Recruiter wants very extensive technical details about all of my previous work

Opacity of an object in 2.8

Employee lack of ownership

How big is a MODIS 250m pixel in reality?

Sailing the cryptic seas

Why is the President allowed to veto a cancellation of emergency powers?

Happy pi day, everyone!

Python if-else code style for reduced code for rounding floats

What exactly is this small puffer fish doing and how did it manage to accomplish such a feat?

Most cost effective thermostat setting: consistent temperature vs. lowest temperature possible

Define, (actually define) the "stability" and "energy" of a compound

Does Mathematica reuse previous computations?

How to simplify this time periods definition interface?

Interplanetary conflict, some disease destroys the ability to understand or appreciate music

Are all passive ability checks floors for active ability checks?

If I can solve Sudoku can I solve Travelling Salesman Problem(TSP)? If yes, how?

How could a scammer know the apps on my phone / iTunes account?

How Could an Airship Be Repaired Mid-Flight

Instead of Universal Basic Income, why not Universal Basic NEEDS?

Why doesn't the EU now just force the UK to choose between referendum and no-deal?

The difference between「N分で」and「後N分で」

It's a yearly task, alright

Optional oneToOne relations and criteria field

Ternary Relations three attributes and an associationRelations, dependencies and normal formSQL tables relationsdatabase design for optional association and temporalClarification for database creation and relationsModeling Optional Foreign Key from Composite Primary Key field (Oracle Data Modeler)Optional Relational FiltersUnderstanding relationsOptional Parent-Child relationshipModeling Optional Column

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:

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?

database-design database-recommendation

share|improve this question

asked Jul 29 '14 at 19:26

Xavier13Xavier13

101

bumped to the homepage by Community♦ 1 min ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  As you have report_id in both tables you can compare report_id of particular table with id in Report table to get information on particular notifier_type. I guess it is notifier_type not required in Report table. But query should be written considering all aspects like performance etc. optimally.
  
  – user44629
  Jul 29 '14 at 20:19
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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?
  
  – ypercubeᵀᴹ
  Jul 30 '14 at 12:54
  

  
  
  
  

add a comment | 

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:

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?

database-design database-recommendation

share|improve this question

asked Jul 29 '14 at 19:26

Xavier13Xavier13

101

bumped to the homepage by Community♦ 1 min ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  As you have report_id in both tables you can compare report_id of particular table with id in Report table to get information on particular notifier_type. I guess it is notifier_type not required in Report table. But query should be written considering all aspects like performance etc. optimally.
  
  – user44629
  Jul 29 '14 at 20:19
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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?
  
  – ypercubeᵀᴹ
  Jul 30 '14 at 12:54
  

  
  
  
  

add a comment | 

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?

database-design database-recommendation

share|improve this question

asked Jul 29 '14 at 19:26

Xavier13Xavier13

101

if($this->getNaturalPerson) { 

    $notifierType = 'naturalPerson';

} ...

share|improve this question

asked Jul 29 '14 at 19:26

Xavier13Xavier13

101

share|improve this question

asked Jul 29 '14 at 19:26

Xavier13Xavier13

101

asked Jul 29 '14 at 19:26

Xavier13Xavier13

101

asked Jul 29 '14 at 19:26

Xavier13Xavier13

101

1 Answer
1

active

oldest

votes

0

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

share|improve this answer

edited Jul 30 '14 at 12:30

answered Jul 29 '14 at 20:15

KitetKitet

2211211

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f72680%2foptional-onetoone-relations-and-criteria-field%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

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

share|improve this answer

edited Jul 30 '14 at 12:30

answered Jul 29 '14 at 20:15

KitetKitet

2211211

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

0

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

share|improve this answer

edited Jul 30 '14 at 12:30

answered Jul 29 '14 at 20:15

KitetKitet

2211211

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

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

share|improve this answer

edited Jul 30 '14 at 12:30

answered Jul 29 '14 at 20:15

KitetKitet

2211211

share|improve this answer

edited Jul 30 '14 at 12:30

answered Jul 29 '14 at 20:15

KitetKitet

2211211

answered Jul 29 '14 at 20:15

KitetKitet

2211211

answered Jul 29 '14 at 20:15

KitetKitet

2211211