Data modeling question

Users
1

I would like some advice about data modeling in XTDB. For now I’m using V1 because V2 isn’t ready yet. I wish it was because XTQL looks awesome, and I’m struggling with limitations in catalog. In particular the question of nesting vs linking documents is something I have found hard to follow in the various examples in the documentation because they tend to be very abstract, or super verbose. It would be good if there was an example with a real world data model one could implement in a RDBMS - something like “how not to think RDBMS terms” by porting a model from, say, PostgreSQL to XTDB.

I am using Biff which uses malli for schema validation and have no problem on that end. The difficulty is querying based on criteria in nested maps. Let’s say I have:

{:xt/id #uuid"..."
  :company/name "ACME, Inc"
  :company/address {:address/address-1 "123 Acme Way"
                                    :address/city "Anywhere"
                                    :address/state "Ohio"
                                    :address/zip "12345"}}

I want to query all company records where the address is in Ohio.

I don’t want to do something like:

{:xt/id #uuid"..."
  :company/name "ACME, Inc"
  :company.address/address-1 "123 Acme Way"
  :company.address/city "Anywhere"
  :company.address/state "Ohio"
  :compnay.address/zip "12345"}

because address is a recurring type I’d like to use across documents (malli makes it easy to validate such composite data elements), but not necessarily make into a first class document. Though it may make sense to do so.

Or is it really necessary to make “address” a first class document and link it by ID? In which case, how can I query company to also add in the address data? How it looks above is how my UI wants to interact with it.

If this doesn’t make sense I can probably provide some of my examples. I also have questions about many-to-many type relationships and how to model them as well.

Still transitioning from RDBMS mindset so any assistance welcome!

2

Hey @eternal.recursion, welcome :wave:

For now I’m using V1 because V2 isn’t ready yet. I wish it was because XTQL looks awesome, and I’m struggling with limitations in catalog.

This is great to hear - and fwiw we also wish it was ready :sweat_smile:

Still transitioning from RDBMS mindset so any assistance welcome!

Openly, in terms of data modelling, you can’t go far wrong with the RDBMS mindset in XT too - the principles and benefits of data normalisation apply here too.

In your address example, either of your approaches would be valid.

  • For your inline example, I’ve not used Malli in anger, but in Spec you could define that your company document has both company keys and address keys with merge - is there something similar in Malli, perhaps?
  • Regarding separate documents - this is also valid, but would involve you deciding on an identity/PK for the address, and it makes updates/deletes more complex, so on balance I’d personally go the other route.

I also have questions about many-to-many type relationships and how to model them as well.

With many-to-many, the usual RDBMS route works here too (it manifests itself as separate join documents in XT), but you additionally have the option to include the relationship on either of the entities (likely as a set of ids) if either side naturally ‘owns’ the relationship.

e.g. if you had ‘students’ and ‘courses’, you could consider join documents, each containing a student-id and a course-id, or (if you decided that the relationship should be owned by the student) you could give each student a set of course-ids.

HTH!

James