> With the foreign key table, performance would suffer, but probably not enough to matter for most use cases.
Citation needed :) That's going to really depend.
I'm not for or against NoSQL (or any platform). Use what's best for you and your app!
In our case, NoSQL makes for a bad database approach. We do many cross-sectional queries that cover many tables (or documents in that world). For example, a Post document doesn't make a ton of sense, we're looking at questions, answers, comments, users, and other bits across many questions all the time. The same is true of users, showing their activity for things would be very, very complicated. In our case, we're simply very relational, so an RDBMS fits the bill best.
Sorry for being unclear. I'm not proposing NoSQL. I'm saying that many NoSQL users really mainly want NoDDL, which can be implemented on top of Postgres JSON storage while retaining SQL.
- data (string type, int id, json fields)
- fk (string type, int subj_id, int obj_id)
select
data.id,
data.fields,
fk_1.obj_id as 'foo_id'
fk_2.obj_id as 'bar_id'
from data
join fk as fk_1 on data.id = fk_1.subj_id
join fk as fk_2 on data.id = fk_2.subj_id
where
data.type = 'my_table'
and fk_1.type = 'foo'
and fk_2.type = 'bar'
What would the performance characteristics of that be versus if "foreign keys" are stored in the same table as the data, if fk has the optimal indexes?
Citation needed :) That's going to really depend.
I'm not for or against NoSQL (or any platform). Use what's best for you and your app!
In our case, NoSQL makes for a bad database approach. We do many cross-sectional queries that cover many tables (or documents in that world). For example, a Post document doesn't make a ton of sense, we're looking at questions, answers, comments, users, and other bits across many questions all the time. The same is true of users, showing their activity for things would be very, very complicated. In our case, we're simply very relational, so an RDBMS fits the bill best.