CODEWITHSUNDEEP
databasedatabase-designrelational-databaseentity-relationship
baskon1
baskon1

Reputation: 330

How do you deal with Recursive Relationships N:M, in Database Relational Schema

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).

///

enter image description here

Upvotes: 1

Views: 3521

Answers (1)

philipxy
philipxy

Reputation: 15158

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.

Upvotes: 1

Related Questions