Reference vs Custom Key

Help
1

Hi! I’m new to Fauna, and currently trying to wrap my head around a few concepts. The one that has bugging me the most, is what the best practice is when it comes to relationships. On a sample project, we have one collection with invoices, and another with users.

Now, the question is:
When looking up data, should I do that via an index which grabs the user_id from the invoice, or should this be done via the ref of the user, which is stored on the user_ref field?
The second approach seems safer, however then I have to use a function which converts the input from the query parameter to the ref of the user id, and then uses an index by refs.
This seems like an extra overhead, and extra operations, thus extra time and cost.

What’s the best-practice here?

2

Mind throwing in some sample user and invoice data so we can suggest you without assumptions. By the way, here is a good blog which explains Relations in Fauna.

3

Hey Jay, of course! Thank you in advance!

We will likely max out at 250 client records, probably many fewer. If it would be something along the line of 2500+, I know it would logically be better to go by ref to avoid duplication.

User

{
  "ref": Ref(Collection("customers"), "281897901352813061"),
  "ts": 1605097638502000,
  "data": {
    "first_name": "Julian",
    "last_name": "Engel",
    "company_name": "Julian Engel",
    "email": "julian@email.com",
    "phone": "+35 77 77 77 77",
    "address": {
      "street": "Some Street ",
      "city": "Limassol",
      "country": "Cyprus",
      "post_code": "1234",
      "office": "14"
    },
    "user_id": "jengel",
    "vat": "00000",
    "updated_at": {
      "$date": "2019-11-17T11:56:00.399Z"
    },
    "created_at": {
      "$date": "2019-11-17T11:56:00.399Z"
    },
    "stripe_id": "xxxxxxxxxxxxxxx"
  }
}

and a sample invoice for me:

{
  "ref": Ref(Collection("invoices"), "281899666372564485"),
  "ts": 1605103227464400,
  "data": {
    "status": "PAID",
    "invoice_number": 1,
    "due_date": "15.01.2020",
    "date": "01.01.2020",
    "customer_id": "jengel",
    "items": [
      {
        "discount": 0,
        "qty": 1,
        "item": "Domain",
        "description": "Domain order for the Mile 26 Domain. ",
        "price": 10,
        "lineTotal": 10
      }
    ],
    "sub_total": 10,
    "discount": 0,
    "net_total": 10,
    "vat": 1.9,
    "total": 11.9,
    "updated_at": {
      "$date": "2020-01-13T12:54:24.573Z"
    },
    "created_at": {
      "$date": "2020-01-13T12:54:24.573Z"
    },
    "stripe_id": "00000000",
    "client_ref": Ref(Collection("customers"), "281897901352813061")
  }
}

Thank you, I’ll check out the blog post now!

4

@Julian_Engel

  • There are two keys that you are using for relations ?
  1. "user_id": "jengel", from clients and "customer_id": "jengel" from invoices.
  2. In customers collection "ref": Ref(Collection("customers"), "281897901352813061") and
    in invoice collection "client_ref": Ref(Collection("customers"), "281897901352813061")

This is a one-to-many relationship (One user Many invoices).

SELECT * FROM customers a , invoices b WHERE a.ref = b.data.client_ref AND a.data.user_id = ‘jengel’;

The access pattern is driven by user_id = 'jengel',

  1. create an Index on customers with Term = data.user_id (returns ref by default)
  2. To Join with invoices, create an index on invoices with Term = client_ref.

SELECT * FROM customers a , invoices b WHERE a.data.user_id = b.data.customer_id AND a.data.user_id = ‘jengel’;
The access pattern is driven by user_id = 'jengel'

  1. create an Index on customers with Term = data.user_id and Values = data.user_id
  2. To Join with invoices, you can create an index on invoices with Term = data.customer_id.

SELECT * FROM customers a , invoices b WHERE a.data.user_id = b.data.customer_id AND b.data.invoice_number = 1;

The access pattern is driven by invoice_number = 1'

  1. create an Index on invoices with Term = data.invoice_number and then a Get(Var(‘Client_ref’)) to get customers data. You would not need join.

All these would perform the same and would be upto your convenience. Hope this helps!!

1 Like
5

Yes, sorry, I didn’t catch that error when posting. I went ahead and changed the clients collection to customers collection.

Now the question is what is the best practice here. Is using client_ref the recommended way to go, or is it okay to just use customer_id?
Is there any advantage to using the client_ref?

6

I updated the post to use Customers and please let me know if it helps.

1 Like
7

Hey Jay!
Yes, this absolutely helps, thank you so much. What you outlined is exactly what I had prepared, as I was testing with both indexes, what I wasn’t sure about was which the prefered way was.

Thank you so very much for your help, and the awesome example, I really appreciate it! Have a wonderful remainder of your day!

8

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.