1. Database Design

Based on the requirements you've provided, we can design a database schema that includes:

• People Table: To store user profile information for each person in the genealogy.
• Users Table: To store information about registered users of the site (who can propose changes).
• Relationships Table: To store family relationships between people.
• Modifications Table: To track proposed changes to profiles or family relationships.
• Modifications History Table: To store each user's actions (accept or reject) for each proposed change.

This structure ensures that changes can only be made when they are approved by at least 3 users and gives the ability to track the history of each modification.

Database Schema (DBML format)

dbml
Copy
Table users {
  id bigint [pk, increment]
  username varchar
  email varchar [unique]
  password varchar
  created_at timestamp
  updated_at timestamp
}

Table people {
  id bigint [pk, increment]
  created_by bigint [ref: > users.id]
  first_name varchar
  last_name varchar
  birth_name varchar
  middle_names varchar
  date_of_birth date
  created_at timestamp
  updated_at timestamp
}

Table relationships {
  id bigint [pk, increment]
  created_by bigint [ref: > users.id]
  parent_id bigint [ref: > people.id]
  child_id bigint [ref: > people.id]
  created_at timestamp
  updated_at timestamp
}

Table modifications {
  id bigint [pk, increment]
  proposer_id bigint [ref: > users.id]
  person_id bigint [ref: > people.id]
  relationship_id bigint [ref: > relationships.id, null]
  change_type varchar
  new_data json
  status varchar [default: "pending"]
  created_at timestamp
  updated_at timestamp
}

Table modifications_history {
  id bigint [pk, increment]
  modification_id bigint [ref: > modifications.id]
  user_id bigint [ref: > users.id]
  action varchar  // 'accept' or 'reject'
  created_at timestamp
}

Ref: modifications.proposer_id > users.id
Ref: modifications.person_id > people.id
Ref: modifications.relationship_id > relationships.id
Ref: modifications_history.modification_id > modifications.id
Ref: modifications_history.user_id > users.id
Ref: relationships.created_by > users.id
Ref: relationships.parent_id > people.id
Ref: relationships.child_id > people.id
Ref: people.created_by > users.id

Schema Diagram :

Schema Explanation:

1. Users Table: Contains registered users who can propose changes, accept/reject proposals, and invite other members to join the site.
2. People Table: Represents each person in the genealogy. This table tracks personal information like name, birth details, and who created the record (created_by field).
3. Relationships Table: Defines family relationships between people, including parent-child relationships.
4. Modifications Table: Stores proposed modifications, which could be changes to people's profiles or relationships. Each modification has a status field indicating whether it’s pending, accepted, or rejected.
5. Modifications History Table: Stores the actions taken by users on modification proposals (accept or reject). This allows us to track who approved or rejected a proposal.

Link to Diagram: