Table Relationships

JayyyTeee

Just something i am considering for my setup.

When sharing relationships between 2 tables do you guys store both values into both tables? Example:

You have a table of items, lets say hats. You've decided to integrate a like button for each item on the front end in which the user would "like" that specific hat.

Do you store both values inside both tables (users table with an array of hats "liked hats") & (hats table with an array of likes "users" ) or do you ONLY store one of those arrays inside 1 of those tables?

Ray Deck

Generally, you want to store a fact in one place to ensure data integrity. If you store the same relationship in two places, you have to make sure you update both places every time.

A relational database makes this easier through foreign keys (in xano they're called "table references"). To handle your situation, we often use something called a "relationship table" that stores one record for every hat-user combination. We use joins (and sometimes add-ons) to connect the one entity (the user table) to its many likes (the hat-user table) to the things they like (the hats table). This approach gives you equal access to the questions "what hats does Joe like?" and "who likes this hat?".

In a more advanced document-ish database like what Xano provides, you can also store the likes as an array field for either the hats or the user. This reduces the number of tables and reduces the complexity of the query operations. But it means you are focusing on one of the two questions above. For example, an array of "liked hats" in the user table will let you answer "what hats does Joe like?" but it will be a lot more work to find out "who likes this hat?" So if you know one question is important and the other is generally not, this is a safe way to proceed.

Finally, I'd give yourself permission to be wrong the first time you do this. You can adjust the database structure later if you discover you went the wrong way - its work, but finite. Keep moving and ship v1 so you can learn what v2 requires!

This kind of data structure question is a frequent topic in our State Change Pro daily office hours where we work on the hardest 5% of a project with an eye toward unsticking so we all make progress.

JayyyTeee

Thank you for your Response Ray.

I was discussing this with someone and they said they store both values in both tables and i thought that was a bit excessive, + more could go wrong that way.

For the majority of my APIs I found that storing everything away from the user table was the best way to go (array of likes "users" in hats table), I can still create a call that can answer the "all the hats joe likes" question with a simple condition filter Xano provides.

I never thought about creating an actual table the relates to both as a way to do this, ill have to look into that.

Thanks again!