3 ms·
I've done this on many occasions, but I actually only go with a single nodes table. Schema is like this id type: string (or enum) data: jsonb
by ralusek 4y ago
I've done this on many occasions, but I actually only go with a single nodes table.
Schema is like this
id
type: string (or enum)
data: jsonb
indexed: jsonb (but this one gets a GIN index)
toId: nullable foreign key to this same table
fromId: nullable foreign key to this same table
createdAt: date
updatedAt: date
So if I had a post with a comment on it, the data would look something like this
{
id: '1111',
type: 'users',
data: { name: 'John Doe' },
}
{
id: '2222',
type: 'users',
data: { name: 'Jane Doe' },
}
{
id: '3333',
type: 'posts',
data: { text: 'Nice day today' },
}
{
id: '4444',
type: 'comments',
data: { text: 'For you, maybe. Raining here' },
}
And now for the edges:
{
id: '5555',
type: '(posts < users)',
toId: '3333',
fromId: '1111',
}
{
id: '6666',
type: '(comments < users)',
toId: '4444',
fromId: '2222',
}
{
id: '7777',
type: '(posts < comments)',
toId: '3333',
fromId: '4444',
}
So then, for example, if I have a post, and I want to find the comments on it, I search for toId = <my post id>, type = '(posts < comments)'.
- TOMDM 4y agoThis works well for nodes with at most 1 parent and child, the post is aimed at supporting many to many relationships though
- ralusek 4y agoWhat I've described here supports many to many...
- fauigerzigerk 4y agoYes, but the downside of your schema is that the distinction between nodes and edges is somewhat muddled. Presumably, all nodes would have nulls in both fromId and toId, but the schema doesn't enforce that. The schema also allows linking edges to other edges. Don't you think this is a bit too much flexibility if the intention is to model a graph?