Beginner question, how do I when creating a table create a reference 1-to-Many? e.g. one person can have many items.

Hey @edgaras, for one to many you'll have a table where: 1. Table A has no reference to Table B 2. Table B has a reference to Table A -> id For many to many (n-to-n) you'll need a bridging table: 1. Table A has no reference to Table B 2. Table B has no reference to Table A 3. Table C has a reference to Table A .id and Table B.id Then you may be looking for a

JOIN

statement to bring the data together.

Thanks for the clarification! Few questions. Does the C table also have to have a Primary Key? What should that be?

@edgaras Sorry for the slow response. If you expect that there is only one unique combination of TableA.id and TableB.id columns then your primary key can be a composite of those two columns. If however you expect that a TableA row might reference a TableB row (and vice-versa) multiple times, then you'll need a separate primary key. With CASCADE it simply means that it will follow all foreignkeys and delete the related data (I think from memory if Table A mentions Table B which mentions Table D you'll only get the data deleted on tables that directly have CASCADE turned on for those foreign keys... but don't quote me on that). What I've seen a lot in db design is that we don't actually delete anything from the db and rather have a column: deleted_at which is a nullable DATETIME which you can set. That way you aren't doing any extremely dangerous operations on the database. This won't obey cascade though so you'd need to execute any side-effects you needed done in some sort of transaction. Hope that helps.

@jaitaiwan thank you very much for your reply and your thoughts! I implemented the composite PK as you suggested. There will be only one M-to-N unique combination. Many Items for many Persons. And the same Item cannot be owned twice by the same Person in my case. I will experiment more with

CASCADE

. For now I will leave without it and see when I meet with the problem when I need to update or delete multiple rows in different tables at once.

deleted_at

is a good idea, but on top of that there should be a period when all rows marked with

deleted_at

should be cleared. Otherwise we would be storing unnecessary data and in some cases violating the user rights for data deletion. Something I will think about too. Most likely some kind of scheduler that runs and clears all deleted_at after half a year or a year.

I agree re deleted. I would use pgcron for that 🙂

Nice, is that a part of Supabase?

I believe so, you may need to enable the extension though

Found it in extensions, nice!