제작의뢰가 맞을 것같네요.

아이디어만 보고 혹해서 만들어 내놓는 멍청한 개발자는 없습니다.

이런 질문은 ChatGPT에게 하는 게 답입니다.

몇 번 질답 주고 받으면 원하는 결과 나오더라구요.

https://dev.mysql.com/doc/refman/8.0/en/with.html

검토해 보세요.

남의 의견과 제안에 비난과 비판만 할게 아니라 "함께하고 연대하고 공유"하는 스피~릿. 그게. 개발자 정신 아닐까요?

개발의 개. 자도 모르는 사람이지만. 2006년도 부터 그누보드로 취미삼아 홈페이지를 만들어 오던 사람으로서 요즘 드는 생각입니다. 

stackkoverflow 검색해 봤는데 이렇더군요. 계속해볼겁니다. 그리고.... 비난하시는 분들. 내 아이디어 훔쳐서 돈 벌 생각마쇼 ㅋ^^

https://stackoverflow.com/questions/8558777/sql-members-table-to-show-family-relationships-between-members

쳇. 녀석에게 물어보니 아래와 같이 답.. 기록해 둡니다.

To create a SQL member table statement that shows family relationships between members, we'll create two tables: one for storing the member information, and another for storing the family relationships between members. Here's an example of how you can create these tables in MySQL:

-- Creating the Member table

CREATE TABLE Members (
  member_id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255) NOT NULL,
  birth_date DATE,
  gender ENUM('M', 'F', 'O', 'U') NOT NULL
);

-- Creating the FamilyRelationships table

CREATE TABLE FamilyRelationships (
  relationship_id INT AUTO_INCREMENT PRIMARY KEY,
  member1_id INT,
  member2_id INT,
  relationship_type ENUM('SPOUSE', 'CHILD', 'PARENT', 'SIBLING') NOT NULL,
  FOREIGN KEY (member1_id) REFERENCES Members (member_id),
  FOREIGN KEY (member2_id) REFERENCES Members (member_id)
);

The Members table stores basic information about each family member, such as their ID, first name, last name, birth date, and gender. The FamilyRelationships table stores the relationship information between pairs of family members, with relationship types including 'SPOUSE', 'CHILD', 'PARENT', and 'SIBLING'.

The relationship_id is the primary key for the FamilyRelationships table, and member1_id and member2_id are foreign keys referencing the member_id in the Members table, ensuring the relationships are connected to valid member entries.

--------------

회원 간 가족 관계를 설정하려면 먼저 위에서 제안한 Members 및 FamilyRelationships 테이블을 사용하여 관계를 데이터베이스에 저장합니다. 그런 다음 프로필에 표시될 내용을 조회하기 위해 SQL 쿼리를 작성합니다.

먼저 예를 들어 앨리스가 밥의 딸이라면 다음과 같이 데이터를 입력할 수 있습니다.

--------------

-- Inserting Member data

INSERT INTO Members (first_name, last_name, birth_date, gender)
VALUES ('Alice', 'Smith', '2010-01-01', 'F'),
       ('Bob', 'Smith', '1980-02-15', 'M');
-- Inserting FamilyRelationship data
INSERT INTO FamilyRelationships (member1_id, member2_id, relationship_type)
VALUES (1, 2, 'CHILD'),
       (2, 1, 'PARENT');

그런 다음 프로필에 가족 관계를 표시하기 위해 다음과 같은 SQL 쿼리를 사용할 수 있습니다.

---------------------

-- Query for Alice's profile (assuming Alice's member_id is 1)
SELECT m2.first_name, m2.last_name, m2.birth_date, m2.gender, fr.relationship_type
FROM FamilyRelationships fr
JOIN Members m1 ON fr.member1_id = m1.member_id
JOIN Members m2 ON fr.member2_id = m2.member_id
WHERE m1.member_id = 1;
-- Query for Bob's profile (assuming Bob's member_id is 2)
SELECT m2.first_name, m2.last_name, m2.birth_date, m2.gender, fr.relationship_type
FROM FamilyRelationships fr
JOIN Members m1 ON fr.member1_id = m1.member_id
JOIN Members m2 ON fr.member2_id = m2.member_id
WHERE m1.member_id = 2;

위 쿼리를 사용하면 각 프로필에 대한 가족 관계가 반환되어 출력됩니다. 이 쿼리를 프로그램에서 실행한 다음 결과를 사용하여 프로필 페이지에 가족 관계와 함께 사진을 표시할 수 있습니다. 이 쿼리를 프로그램에 포함시켜 회원의 가족 관계가 정확하게 표시되도록 해야 합니다.

----------------------------

Yes, it's possible to implement 4th cousins with the code I suggested, but it requires some modifications to the FamilyRelationships table and the way you store and query the relationships.

First, you'll need to change the relationship_type in the FamilyRelationships table to include more specific types:

-- Recreate the FamilyRelationships table
DROP TABLE IF EXISTS FamilyRelationships;
CREATE TABLE FamilyRelationships (
  relationship_id INT AUTO_INCREMENT PRIMARY KEY,
  member1_id INT,
  member2_id INT,
  relationship_type ENUM('SPOUSE', 'MOTHER', 'FATHER', 'SISTER', 'BROTHER') NOT NULL,
  FOREIGN KEY (member1_id) REFERENCES Members (member_id),
  FOREIGN KEY (member2_id) REFERENCES Members (member_id)
);

In this case, we've changed the relationship types to 'MOTHER', 'FATHER', 'SISTER', and 'BROTHER', which will help to build the family tree more accurately.

Now, let's assume you have a family tree with all the required members and their relationships stored in the Members and FamilyRelationships tables. To find 4th cousins, you would need to traverse the family tree by querying the parent-child relationships repeatedly. This can be done using recursive Common Table Expressions (CTEs) in MySQL.

Here's an example of how you can find the 4th cousins for a given member (let's say their member_id is 1):

WITH RECURSIVE FamilyTree AS (
  SELECT member_id, first_name, last_name, birth_date, gender, 0 AS generation
  FROM Members
  WHERE member_id = 1
  UNION ALL
  SELECT m.member_id, m.first_name, m.last_name, m.birth_date, m.gender, ft.generation + 1
  FROM Members m
  JOIN FamilyRelationships fr ON (m.member_id = fr.member1_id AND fr.relationship_type IN ('MOTHER', 'FATHER'))
  JOIN FamilyTree ft ON fr.member2_id = ft.member_id
),
Cousins AS (
  SELECT ft1.member_id AS cousin1_id, ft2.member_id AS cousin2_id, ft1.generation AS cousin1_generation, ft2.generation AS cousin2_generation
  FROM FamilyTree ft1
  JOIN FamilyTree ft2 ON ft1.member_id <> ft2.member_id
  WHERE ft1.generation - ft2.generation = 4 OR ft2.generation - ft1.generation = 4
)
SELECT m1.first_name AS cousin1_first_name, m1.last_name AS cousin1_last_name, m2.first_name AS cousin2_first_name, m2.last_name AS cousin2_last_name
FROM Cousins c
JOIN Members m1 ON c.cousin1_id = m1.member_id
JOIN Members m2 ON c.cousin2_id = m2.member_id;

In this example, the FamilyTree CTE recursively builds the family tree for the given member by finding their ancestors, and the Cousins CTE finds pairs of members that are 4 generations apart. The final query returns the names of the 4th cousins.

Keep in mind that this query may not be very efficient if you have a large family tree, as it requires traversing the entire tree. It's also worth noting that this solution assumes there are no duplicate relationships or loops in the family tree.