Optional oneToOne relations and criteria field
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
asked Jul 29 '14 at 19:26
Xavier13Xavier13
101
bumped to the homepage by Community♦ 10 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
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
asked Jul 29 '14 at 19:26
Xavier13Xavier13
101
bumped to the homepage by Community♦ 10 mins 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
asked Jul 29 '14 at 19:26
Xavier13Xavier13
101
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
database-design database-recommendation
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
asked Jul 29 '14 at 19:26
Xavier13Xavier13
101
asked Jul 29 '14 at 19:26
Xavier13Xavier13
101
101
bumped to the homepage by Community♦ 10 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 10 mins 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 |
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
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
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
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 |
1 Answer
1
active
oldest
votes
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
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
});
}
});
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
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
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
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
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
answered Jul 29 '14 at 20:15
KitetKitet
2211211
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
answered Jul 29 '14 at 20:15
KitetKitet
2211211
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
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 |
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
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
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
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 |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
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
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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