Database design challenge

Jay

I find the hardest to do is decide on how to structure the database. 

For example, I'm never quite sure when tables should have columns on both tables pointing to each other, or only one pointing to the other.

A User has a Post and it links to the Post table record, but should a Post have a Created_by column, pointing to the User? Is there a reason why to have it one way or another? Does it depend on how the front end is built or is able to query the database? (like Draftbit vs Bubble vs Webflow)

I've tried to read articles about this but i found nothing coherent and clear. 

Does the way Xano is built inform how i should build a DB in xano vs elsewhere? 

Any good guides (video preferably) or general suggestions on this? 

Karl Larson

This (link) documentation article on database relationships might help. Specifically:
“AQ: When should I put things in an object vs a separate tableThere is no hard and fast rule on this, but we like to say if you anticipate your object list growing over 100, it's probably time to start thinking about separating it into a new table. You can still keep everything in an object, but it becomes a little unwieldy to manage especially because when you update objects, you're updating the whole thing (so potentially 100+ records) and if you make a mistake on how you update the data on the front-end, it could lead to some trouble.”


Here's an example I'm using with a Users tables and Proofs table (probably the same as your Posts table) 👇

User table is not linked to Proofs.
Proof table does have a user field for the user who created the proof. When I want to GET proofs of the authenticated user, I query all Proof records and use a filter where proof.user_id = user_id of auth user. Or if I want proofs of a specific user, I add an input to the function and query all proofs where proof.user_id = input's user_id.