3 ms·

To quote myself: > or normalize to yet another join table This is also semantically incorrect. There needs to be exactly 2 rows there. You cannot in a declara

To quote myself: > or normalize to yet another join table This is also semantically incorrect. There needs to be exactly 2 rows there. You cannot in a declarative way, prevent that if you use this approach. It's also a known dilemma, so we don't have to rediscover the wheel here.

noctune 5y ago
What if you have something like (relatee_a_id, relatee_b_id, [rest of relation fields ...]) with the constraint that relatee_a_id < relatee_b_id? Granted, it might not be very nice to query.
- patates 5y ago
  Well, that is what I actually use most of the time, but to nitpick, it still is not declarative, and semantically a and b have no meaning. Just to be clear, I'm saying these are in 99% of the cases not deal breakers but they can be if all you want to do is map graphs (and follow chains through many members), as there is also a significant performance cost to all this abstraction. What you described is, IIRC, also what some file-based graph DBs do in the background with sqlite and abstract away from you (I've seen a couple custom ones in some projects). Left with a RDBMS, you just need to do it yourself and analyze how much it costs.
Groxx 5y ago
Custom indexes or insert triggers can both guarantee that, both in a declarative way (in that they prevent normal "blind" inserts. I'm not entirely sure what you mean by declarative here tbh). Also I have no idea why a separate table is a negative / constraint of some kind. It's a natural way to model it.
- patates 5y ago
  > no idea why a separate table is a negative When you are following a chain of relations (a vector), you end up losing a lot of performance. > not entirely sure what you mean by declarative Guaranteed by the database semantics, not imperative code even when it runs on the DB. I don't want to come off sounding like I think it's a bad idea to map graphs on RDBMSs, it's just inconvenient sometimes. Usually not enough to complicate your setup, adding another database, but rarely it is. I'm not sure though about the suggested use case here, I really don't know it enough to make a suggestion. In such cases I always pick sqlite or postgres (depending on the client model) though. You can't be too wrong with those, and you are most of the times right :) Not to mention the crazy extensibility of pg and the stuff you can do with it.
  - Groxx 5y ago
    Imperative code run on the DB (i.e. triggers) seems entirely in-scope. It's an incredibly powerful tool provided natively by the DB, intended to be used for exactly this kind of custom constraint. Whether it's begin/end with imperative code or modeled some other way declarative seems absolutely, 100% irrelevant to me - it's the perfect implementation detail, it could have been done with `create unique index max 2 (x, y) on mytable` and you'd never know. If you meant to prevent "all inserts are now [run this stored procedure / multi-statement operation / transaction with N steps]", then yeah - I agree that runs counter to "normal" use in most cases. It's certainly dramatically more error prone. Triggers don't need this though. re chain of relations: I haven't poked too hard in this direction in SQLite, but I do totally believe its query planner isn't too sophisticated. And though you can get pretty good data locality with fancy design around the DB, you certainly won't get good locality just by inserting a bunch of data and running queries.
harperlee 5y ago
A directionless link or relation does not require exactly 2 rows here, that’s overconstraining the definition. A simple example would be to link 3 brothers with the same relation: the worse solution would be dividing it in 3 different binary relations; the reality is that each brother is part of the “brother relation” set by way of sharing parents.
- galaxyLogic 5y ago
  Brothers yes. But what about children? child_of is a directional relation, right? Semantic web I assume is full of such directional relations. Bidirecitonal relations are the exception. Things like "causes", "indicates", "enables", "prevents", ...

noctune 5y ago

What if you have something like (relatee_a_id, relatee_b_id, [rest of relation fields ...]) with the constraint that relatee_a_id < relatee_b_id? Granted, it might not be very nice to query.

patates 5y ago
Well, that is what I actually use most of the time, but to nitpick, it still is not declarative, and semantically a and b have no meaning. Just to be clear, I'm saying these are in 99% of the cases not deal breakers but they can be if all you want to do is map graphs (and follow chains through many members), as there is also a significant performance cost to all this abstraction. What you described is, IIRC, also what some file-based graph DBs do in the background with sqlite and abstract away from you (I've seen a couple custom ones in some projects). Left with a RDBMS, you just need to do it yourself and analyze how much it costs.

Groxx 5y ago

Custom indexes or insert triggers can both guarantee that, both in a declarative way (in that they prevent normal "blind" inserts. I'm not entirely sure what you mean by declarative here tbh). Also I have no idea why a separate table is a negative / constraint of some kind. It's a natural way to model it.

patates 5y ago
> no idea why a separate table is a negative When you are following a chain of relations (a vector), you end up losing a lot of performance. > not entirely sure what you mean by declarative Guaranteed by the database semantics, not imperative code even when it runs on the DB. I don't want to come off sounding like I think it's a bad idea to map graphs on RDBMSs, it's just inconvenient sometimes. Usually not enough to complicate your setup, adding another database, but rarely it is. I'm not sure though about the suggested use case here, I really don't know it enough to make a suggestion. In such cases I always pick sqlite or postgres (depending on the client model) though. You can't be too wrong with those, and you are most of the times right :) Not to mention the crazy extensibility of pg and the stuff you can do with it.
- Groxx 5y ago
  Imperative code run on the DB (i.e. triggers) seems entirely in-scope. It's an incredibly powerful tool provided natively by the DB, intended to be used for exactly this kind of custom constraint. Whether it's begin/end with imperative code or modeled some other way declarative seems absolutely, 100% irrelevant to me - it's the perfect implementation detail, it could have been done with `create unique index max 2 (x, y) on mytable` and you'd never know. If you meant to prevent "all inserts are now [run this stored procedure / multi-statement operation / transaction with N steps]", then yeah - I agree that runs counter to "normal" use in most cases. It's certainly dramatically more error prone. Triggers don't need this though. re chain of relations: I haven't poked too hard in this direction in SQLite, but I do totally believe its query planner isn't too sophisticated. And though you can get pretty good data locality with fancy design around the DB, you certainly won't get good locality just by inserting a bunch of data and running queries.

harperlee 5y ago

A directionless link or relation does not require exactly 2 rows here, that’s overconstraining the definition. A simple example would be to link 3 brothers with the same relation: the worse solution would be dividing it in 3 different binary relations; the reality is that each brother is part of the “brother relation” set by way of sharing parents.

galaxyLogic 5y ago
Brothers yes. But what about children? child_of is a directional relation, right? Semantic web I assume is full of such directional relations. Bidirecitonal relations are the exception. Things like "causes", "indicates", "enables", "prevents", ...