Help structuring database for a new project
Unanswered
Franklin's Gull posted this in #help-forum
Franklin's GullOP
I am trying to learn next.js by creating a new project using supabase for backend/auth. I am struggling to understand how to set up the database.
My goal for this project is to have an application where a user can log in, and invite other users to join their "family". The concept is to have a singular family tree structure so that everyone can add onto it.
So a user can create an account, create a family tree via forms, then invite others in the family to look at the family tree or add to it.
I have seen this done before with simple family trees using d3, but I want to make something a little more complex.
I want to also add in a real estate aspect to this family tree, in that a person can own a property or a fraction of a property.
The tree would have different types of nodes ( people, house, apartment, ect), and the eges would be the relationships ( father, sibling) or (owns %, rents).
I'm having difficulty wrapping my head around the data model and would love some ideas or pointers in the right direction.
My goal for this project is to have an application where a user can log in, and invite other users to join their "family". The concept is to have a singular family tree structure so that everyone can add onto it.
So a user can create an account, create a family tree via forms, then invite others in the family to look at the family tree or add to it.
I have seen this done before with simple family trees using d3, but I want to make something a little more complex.
I want to also add in a real estate aspect to this family tree, in that a person can own a property or a fraction of a property.
The tree would have different types of nodes ( people, house, apartment, ect), and the eges would be the relationships ( father, sibling) or (owns %, rents).
I'm having difficulty wrapping my head around the data model and would love some ideas or pointers in the right direction.
8 Replies
Bengal
I think a graph data structure would be suitable for this, since you will be allowing circular connections between nodes
@Bengal I think a graph data structure would be suitable for this, since you will be allowing circular connections between nodes
Franklin's GullOP
Are you suggesting to have a table for nodes and another table for edges?
Bengal
Yes, that seems reasonable. Then any logged in user would have an associated
node_id, which you could select for in the nodes and edges table to get any edges pointing to or away from the user joined on the other nodes to e.g. display on the user's profile page or dashboardto handle different node and edge types you could also store a
node_type enum and store separate tables people, houses etcand select all the relevant information using a join like this: https://stackoverflow.com/a/58549077
(i guess technically you don't need the
node_type enum for the select, but it may make type checking easier on the js side)I'm assuming a standard sql database above but you could also look into graph-oriented DBs such as orientdb or neo4j, but I'm not familiar with those
@Bengal I'm assuming a standard sql database above but you could also look into graph-oriented DBs such as orientdb or neo4j, but I'm not familiar with those
Franklin's GullOP
Thanks for the input! I'm looking into neo4j, as it seems like it would make things easier, but I have never worked with it. I have worked with SQL so I can wrap my head around that. I may take the time to expeirment with neo4j for a little bit