How do you deal with Recursive Relationships N:M, in Database Relational Schema
up vote
0
down vote
favorite
When we have a N:M Recursive Relationship what is the best way to create the relational schema?
In various books I searched they analyze recursive relationships of 1:1 and 1:N but for N:M there is almost nothing.
Should I treat it like a new Relationship like we do in traditional N:M non-recursive relationships?
For example in this recursive relationship is it better to:
A.Create a new relationship
INVITE(InviterId,InviteeId,AcceptanceDate,InvitationDate) - in bold the Primary Keys . In this case they are also foreign keys.
////
B. Include this relationship in the Person Entity
PERSON(Id,Password,InviterId,InviteeId,AcceptanceDate,InvitationDate).
///
database database-design relational-database entity-relationship
asked Nov 10 at 20:40
baskon1
719
add a comment |
up vote
0
down vote
favorite
When we have a N:M Recursive Relationship what is the best way to create the relational schema?
In various books I searched they analyze recursive relationships of 1:1 and 1:N but for N:M there is almost nothing.
Should I treat it like a new Relationship like we do in traditional N:M non-recursive relationships?
For example in this recursive relationship is it better to:
A.Create a new relationship
INVITE(InviterId,InviteeId,AcceptanceDate,InvitationDate) - in bold the Primary Keys . In this case they are also foreign keys.
////
B. Include this relationship in the Person Entity
PERSON(Id,Password,InviterId,InviteeId,AcceptanceDate,InvitationDate).
///
database database-design relational-database entity-relationship
asked Nov 10 at 20:40
baskon1
719
What exactly do you mean by "recursive relationship"--in terms of the state of a database or DBMS--its DDL & table values?
– philipxy
Nov 10 at 21:00
You mean those 2 columns are the columns of a single composite CK (possibly a PK). And if two people can have more than one commitment then that CK has more columns.
– philipxy
Nov 10 at 21:29
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
When we have a N:M Recursive Relationship what is the best way to create the relational schema?
In various books I searched they analyze recursive relationships of 1:1 and 1:N but for N:M there is almost nothing.
Should I treat it like a new Relationship like we do in traditional N:M non-recursive relationships?
For example in this recursive relationship is it better to:
A.Create a new relationship
INVITE(InviterId,InviteeId,AcceptanceDate,InvitationDate) - in bold the Primary Keys . In this case they are also foreign keys.
////
B. Include this relationship in the Person Entity
PERSON(Id,Password,InviterId,InviteeId,AcceptanceDate,InvitationDate).
///
database database-design relational-database entity-relationship
asked Nov 10 at 20:40
baskon1
719
When we have a N:M Recursive Relationship what is the best way to create the relational schema?
In various books I searched they analyze recursive relationships of 1:1 and 1:N but for N:M there is almost nothing.
Should I treat it like a new Relationship like we do in traditional N:M non-recursive relationships?
For example in this recursive relationship is it better to:
A.Create a new relationship
INVITE(InviterId,InviteeId,AcceptanceDate,InvitationDate) - in bold the Primary Keys . In this case they are also foreign keys.
////
B. Include this relationship in the Person Entity
PERSON(Id,Password,InviterId,InviteeId,AcceptanceDate,InvitationDate).
///
database database-design relational-database entity-relationship
database database-design relational-database entity-relationship
asked Nov 10 at 20:40
baskon1
719
asked Nov 10 at 20:40
baskon1
719
edited Nov 10 at 21:02
asked Nov 10 at 20:40
baskon1
719
asked Nov 10 at 20:40
baskon1
719
asked Nov 10 at 20:40
baskon1
719
719
What exactly do you mean by "recursive relationship"--in terms of the state of a database or DBMS--its DDL & table values?
– philipxy
Nov 10 at 21:00
You mean those 2 columns are the columns of a single composite CK (possibly a PK). And if two people can have more than one commitment then that CK has more columns.
– philipxy
Nov 10 at 21:29
add a comment |
What exactly do you mean by "recursive relationship"--in terms of the state of a database or DBMS--its DDL & table values?
– philipxy
Nov 10 at 21:00
You mean those 2 columns are the columns of a single composite CK (possibly a PK). And if two people can have more than one commitment then that CK has more columns.
– philipxy
Nov 10 at 21:29
What exactly do you mean by "recursive relationship"--in terms of the state of a database or DBMS--its DDL & table values?
– philipxy
Nov 10 at 21:00
What exactly do you mean by "recursive relationship"--in terms of the state of a database or DBMS--its DDL & table values?
– philipxy
Nov 10 at 21:00
You mean those 2 columns are the columns of a single composite CK (possibly a PK). And if two people can have more than one commitment then that CK has more columns.
– philipxy
Nov 10 at 21:29
You mean those 2 columns are the columns of a single composite CK (possibly a PK). And if two people can have more than one commitment then that CK has more columns.
– philipxy
Nov 10 at 21:29
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
Definitions are recursive, not relationships/associations. You seem to be talking about FK (foreign key) cycles or the special case of that where a FK references its own table. A FK constraint says values appear elsewhere as PK/UNIQUE. Alternatively, that values that satisfy one relationship satisfy another in just one way. There's nothing special about this from a relational design standpoint. Most SQL DBMSs are needlessly poor when the explicit/declared FK graph is not a tree.
Whether you could or should combine your tables depends on the information modeling & database design method you are using plus relational database design principles. There are many different "traditions". Find & follow a published academic textbook on information modeling, the relational model & database design. (Dozens are free online, also slides & courses.) PS Information & manuals on a tool to manage designs does not constitute an introduction on how to design.
The diagram you give is a Chen original true pure ER (entity relationship) diagram. Under that method you can't combine your entity & relationship into a relationship because an entity needs to have its own box & table. But you could in the relational model & in pseudo-ER methods & products that don't use diamonds in their diagrams & in methods that allow more choice in mapping from Chen diagrams.
answered Nov 10 at 21:01
philipxy
11.2k42149
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
Definitions are recursive, not relationships/associations. You seem to be talking about FK (foreign key) cycles or the special case of that where a FK references its own table. A FK constraint says values appear elsewhere as PK/UNIQUE. Alternatively, that values that satisfy one relationship satisfy another in just one way. There's nothing special about this from a relational design standpoint. Most SQL DBMSs are needlessly poor when the explicit/declared FK graph is not a tree.
Whether you could or should combine your tables depends on the information modeling & database design method you are using plus relational database design principles. There are many different "traditions". Find & follow a published academic textbook on information modeling, the relational model & database design. (Dozens are free online, also slides & courses.) PS Information & manuals on a tool to manage designs does not constitute an introduction on how to design.
The diagram you give is a Chen original true pure ER (entity relationship) diagram. Under that method you can't combine your entity & relationship into a relationship because an entity needs to have its own box & table. But you could in the relational model & in pseudo-ER methods & products that don't use diamonds in their diagrams & in methods that allow more choice in mapping from Chen diagrams.
answered Nov 10 at 21:01
philipxy
11.2k42149
add a comment |
up vote
0
down vote
Definitions are recursive, not relationships/associations. You seem to be talking about FK (foreign key) cycles or the special case of that where a FK references its own table. A FK constraint says values appear elsewhere as PK/UNIQUE. Alternatively, that values that satisfy one relationship satisfy another in just one way. There's nothing special about this from a relational design standpoint. Most SQL DBMSs are needlessly poor when the explicit/declared FK graph is not a tree.
Whether you could or should combine your tables depends on the information modeling & database design method you are using plus relational database design principles. There are many different "traditions". Find & follow a published academic textbook on information modeling, the relational model & database design. (Dozens are free online, also slides & courses.) PS Information & manuals on a tool to manage designs does not constitute an introduction on how to design.
The diagram you give is a Chen original true pure ER (entity relationship) diagram. Under that method you can't combine your entity & relationship into a relationship because an entity needs to have its own box & table. But you could in the relational model & in pseudo-ER methods & products that don't use diamonds in their diagrams & in methods that allow more choice in mapping from Chen diagrams.
answered Nov 10 at 21:01
philipxy
11.2k42149
add a comment |
up vote
0
down vote
up vote
0
down vote
Definitions are recursive, not relationships/associations. You seem to be talking about FK (foreign key) cycles or the special case of that where a FK references its own table. A FK constraint says values appear elsewhere as PK/UNIQUE. Alternatively, that values that satisfy one relationship satisfy another in just one way. There's nothing special about this from a relational design standpoint. Most SQL DBMSs are needlessly poor when the explicit/declared FK graph is not a tree.
Whether you could or should combine your tables depends on the information modeling & database design method you are using plus relational database design principles. There are many different "traditions". Find & follow a published academic textbook on information modeling, the relational model & database design. (Dozens are free online, also slides & courses.) PS Information & manuals on a tool to manage designs does not constitute an introduction on how to design.
The diagram you give is a Chen original true pure ER (entity relationship) diagram. Under that method you can't combine your entity & relationship into a relationship because an entity needs to have its own box & table. But you could in the relational model & in pseudo-ER methods & products that don't use diamonds in their diagrams & in methods that allow more choice in mapping from Chen diagrams.
answered Nov 10 at 21:01
philipxy
11.2k42149
Definitions are recursive, not relationships/associations. You seem to be talking about FK (foreign key) cycles or the special case of that where a FK references its own table. A FK constraint says values appear elsewhere as PK/UNIQUE. Alternatively, that values that satisfy one relationship satisfy another in just one way. There's nothing special about this from a relational design standpoint. Most SQL DBMSs are needlessly poor when the explicit/declared FK graph is not a tree.
Whether you could or should combine your tables depends on the information modeling & database design method you are using plus relational database design principles. There are many different "traditions". Find & follow a published academic textbook on information modeling, the relational model & database design. (Dozens are free online, also slides & courses.) PS Information & manuals on a tool to manage designs does not constitute an introduction on how to design.
The diagram you give is a Chen original true pure ER (entity relationship) diagram. Under that method you can't combine your entity & relationship into a relationship because an entity needs to have its own box & table. But you could in the relational model & in pseudo-ER methods & products that don't use diamonds in their diagrams & in methods that allow more choice in mapping from Chen diagrams.
answered Nov 10 at 21:01
philipxy
11.2k42149
edited Nov 10 at 21:23
answered Nov 10 at 21:01
philipxy
11.2k42149
answered Nov 10 at 21:01
philipxy
11.2k42149
answered Nov 10 at 21:01
philipxy
11.2k42149
11.2k42149
add a comment |
add a comment |
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%2fstackoverflow.com%2fquestions%2f53243208%2fhow-do-you-deal-with-recursive-relationships-nm-in-database-relational-schema%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
What exactly do you mean by "recursive relationship"--in terms of the state of a database or DBMS--its DDL & table values?
– philipxy
Nov 10 at 21:00
You mean those 2 columns are the columns of a single composite CK (possibly a PK). And if two people can have more than one commitment then that CK has more columns.
– philipxy
Nov 10 at 21:29