Joins and foreign keys

[gedw99]

Corrosion has no foreign keys and so it’s like a NoSQL database.

Then, typically, the data is often denormalized, meaning that it is duplicated across multiple collections or documents to improve query performance and make up for a lack of Foreign keys.

Instead of using joins, these types of databases often use techniques such as embedding and referencing to combine data from multiple documents or collections.

An example of this is if you want to describe the config for a system in the DB, but some of the values are derived from another foreign system and its config which is also modelled in the DB.

higher level generic example also exist of course , like a todo that wants to reference data from other tables.

I am wondering if any patterns or recommendations for doing this in corrosion exist yet ?

It could be backed into the rust code or done at a high level .

[jeromegn]

Hmm, even if foreign keys aren't supported, you can still reference rows from other rows by primary key. Foreign key features that you'd be missing are things like cascading deletes.

The reason foreign keys or unique indexes (except the primary key's) aren't supported is that the CRDT wouldn't be able to arrive at the same final state depending on the order it received the changes.

For some perspective: In our production schema, we're referencing rows from different tables everywhere and we are using JOINs extensively. We have millions of rows across ~20 tables. We constantly write new data and execute large queries across multiple tables.

I'm sure Corrosion could be used in the way you describe. You could use tables with a primary key and a single JSON column and use it like a document store.

Do you have a more specific use case to explore?

[hkrutzer]

Also take a look at https://vlcn.io/docs/cr-sqlite/constraints and perhaps the other doc pages.

[gedw99]

That’s fantastic . Problem solved. Yes the CRDT sync problem is a well known one of containment.

suggest an example is added that uses FK with a subscription. I believe the todo does not use FK.