https://supabase.com/ logo
Join Discord
Powered by
Struggling with structure one-to-many relationship...
# off-topic
j
Struggling with structure one-to-many relationships in Supabase. any tips? The scenario: I'm working on a commercial real estate app. Each building has lots of different characteristics (it's got a reception area, roof terrace, 24/7 access, etc). Each characteristic has its own description, icon and more. In my front end, for each building I would like a list of it's characteristics. What would be a good way to structure this in Supabase?
a
Looks like this scenario calls for a many-to-many relationship! imo you may have three tables for example, "buildings" and "amenities", and create another child bridging/junction table (say "building_amenities") with two foreign keys referencing the two primary keys in the parent buildings and amenities table.
Copy code
buildings
+ PK + Name
| 1  | Tower A
| 2  | Tower B

amenities
+ PK + Name
| 1  | Terrace
| 2  | Reception Area

building_amenities* (Junction)
+ Building FK + Amenity FK
| 1           | 1
| 2           | 1
| 2           | 2
h
Uhh why?
Two tables are enough to do this job
Unless u want the foreign key support that is lol
Otherwise you can store the name of amenity in an text array in a column called ameneties in "buildings" table
and then a table called "amenities" with columns name, icon and description
Oh wait fuck
u need foreign key to use supabase
Yeah hes right nvm lmao
But wait
nothing
j
Hey @User ... you got my hopes up! 😩
@User seems like i'll have to go down the convoluted 'table-join' route. Thanks for the pointer 👍
a
You got this. 💪
j
It's great that this will work but it feels so much like a hack!
m
I have a similar problem. How do I make values instead of id's appearing in the table in the column with my relationship?
h
Yes, you will be able to get values with the above said example
The table acts as an intermediary
a
m
Thanks @User and @User ! Now I know the values will never appear in the table but will appear when I query using select (https://supabase.io/docs/reference/javascript/select#query-foreign-tables). I used wrong name for one of my columns when I tried this query before. Have a great day! 🙂
h
I didn't really do anything its all @User but thanks :D
m
By the way. Is it possible to have multiple values (id's) in one record with relationship to another table? For example: | customerName | products | | Mattias | 1,2,3 |
h
Yea that's something you cannot do unfortunately
The foreign key of the table and the primary key of foreign table must match to create a relationship
That's why you create another table, for eg: "customer_purchases" | customerName | product | Mattias 1 Mattias 2 Mattias 3 And then relate customerName -> customerName in "customers" table and product->id(?) in products table
And then u create relationships: customer_purchases -> customer customer_puchases -> products
So now for eg if u query
Copy code
javascript
let result = await supabase.from('customers').select('customerName, customer_purchases ( product ( name, id, etc ) )').maybeSingle();

//result : { customerName: "Mattias", customer_purchases: [{ product: { name, id, etc} }, { product: { name, id, etc} }] }
m
Thats grand! 😆 Have nice weekend!
a
Hopefully, we'd have foreign key arrays sometime in the future. https://stackoverflow.com/a/50441059
h
Ikr it's long overdue
PreviousNext
Powered by