What are the pest practices for relating data?

[CarcajadaArtificial]

Hello! I've been using kvdex for a couple of weeks now and I absolutely love it. I wouldn't use DenoKV without it honestly. Currently, I'm struggling to design a solution for databases that are a little more complex than a couple of unrelated models because I'm unsure of the best practices for relating them. In other words, what would be the best way to connect two models in a one-to-many or many-to-many relationship? My current solution is storing an array of the stringified IDs (Deno.KvKeyPart.toString()) of a model, inside of the other one. For example:

type Wheels = {
  brand: string
  model: string
  ...
}

type Car = {
  ...
  wheels_ids: string[]
}

[oliver-oloughlin]

Hi, amazing to hear you have been enjoying it so far, thanks!

I think the answer depends a little on your exact use case. If you want to do it in a more traditional relational database way, where each model has its own collection, I believe your best option is something like you already show.

In your example you can get a one-to-many or a many-to-many relationship where the Car owns the relationship. Alternatively, for two-way relationships, you could create an additional model and collection for holding the relationship and make efficient queries using indexing. Here is an example using secondary indexing for many-to-many:

import { 
  kvdex, 
  collection, 
  indexableCollection, 
  model, 
  KvId 
} from "kvdex"

// Models
type Wheel = {
  brand: string
  model: string
  ...
}

type Car = {
  ...
}

// Relationship model
type CarWheel = {
  car_id: KvId
  wheel_id: KvId
}

const kv = await Deno.openKv()

// I added the cars_wheels relationship collection to its own nested grouping just for sorting
const db = kvdex(kv, {
  wheels: collection(model<Wheel>()),
  cars: collection(model<Car>()),
  relationships: {
    cars_wheels: indexableCollection(model<CarWheel>(), {
      indices: {
        car_id: "secondary",
        wheel_id: "secondary",
      }
    })
  }
})

// Add wheel and car
const cr1 = await db.wheels.add(...)
const cr2 = await db.cars.add(...)

// Add relationship entry
if (cr1.ok && cr2.ok) {
  await db.relationships.cars_wheels.add({
    wheel_id: cr1.id,
    car_id: cr2.id 
  })
}

// Query the relationship by car id (could also be done by wheel id)
const { result } = await db.relationships.cars_wheels.findBySecondaryIndex("car_id", "id")
const wheelIds = result.map(doc => doc.value.wheel_id)
const wheelsByCarId = await db.wheels.findMany(wheelIds)

To get one-to-one, one-to-many or many-to-one, you can change the indices between secondary and primary as appropriate. I still think it might be a little less bloated to simply let your models contain the id(s) of their related model, you could then also use indexing to ensure a unique id for one-to-many relationships.

[oliver-oloughlin]

The only issue with letting your models control the relationships is that updating many-to-many or many-to-one might be cumbersome, as you might have to extract the array of ids, add or remove ids depending on what sort of update you are doing, and then update the entire model with the new id array. That's why I said it depends on your use case. If you are regularly making updates that effect the relationship, then I think my example using a relationship collection should be considered best practice.

[CarcajadaArtificial]

Your answer was incredibly helpful. You literally answered all of my questions, even the ones that I didn't knew I had. Thank you very much. I still have some other topics I'd like to discuss in the near future but first I'd like to try this out.

[oliver-oloughlin]

I have actually given a lot of thought towards how I could support relationships, but my conclusion so far is that it would require a lot of work and refactoring to make something usable and type safe. Thanks for giving me your thoughts on this topic.

[CarcajadaArtificial]

I've pondered about it for a bit and this sounds totally understandable. I'll be trying to implement anything that might aid in relating models, and let you know if I come up with something worthwhile.