All geek questions in one place

SQL Database Development

I am creating a database for a program where I have to model some relationships in the family. for example: X - father Y, Y - son X

So, I have a Members table with all the information about each member, so I thought about making many, many relationships between the Members table and myself, so that Member_Member will have the columns "FK_FromID, FK_ToID" as compound (is this correct?) And "FK_RelationType" as a foreign key to the RelationTypes table , which will have the relationship type "Father, mother, son, daughter" and two relations, going from one to many from the "Participants" table to these two foreign keys

My problem: when deleting, if I choose cascading, then I will do loops, because if I delete the element, then there will be two deletion of passes to the related entries in the Member_Member bridge , knowing that in the program whenever I insert relationships with father, I’ll also insert the son’s relationship in the Member_Member table, is there a way or workaround to enable cascading so that whenever I delete a member, I delete the related entries in Member_member regardless of whether they are written in column a or from an external the key

So, I don’t know what to do, is it the right design in the first place or what ?, what should I do with cycling, also, what do you think, the best design for the same problem should know that I need to indicate what is the relationship between the two sides

Thanks so much for any help and sorry for the bad english bishoy

+3
source share
2 answers

SQL does not cope with such "network" problems as it is very good.

The member bridge table is a terrible name. This is the parent-child (or parent-child) bridge. Bridge tables must not have a composite key. Bridge tables have a surrogate key (serial number only) and a couple of FK links to other tables. Two FKs must have names such as "member" and "parent" so that it is completely clear what the relation is in this table.

. . ; " ".

, - FK NULL. , - --; .

"", .

, - SQL - . SQL, , .

, ( , ), SQL ( FK).

, ( ) "member-parent" , "member- " , .

, . --. . ? , -, , . ? , ?

standard. . .

SQL , , , .

+2

  • , , , mother_id father_id members.

    , . , X Y, , X Y .

    , . .

  • member_member, , , , X Y, Y X. , . , . , , X Y, , Y X.

, , . S.Lott, "" .

0

Source: https://habr.com/ru/post/1735024/

More articles:


All Articles