Forum Academy Marketplace Showcase Pricing Features

Database organization one-to-many(?)

Hi guys, I am having trouble figuring out how to organize my database, so I made this image to clarify everything a bit (maybe it only confuses even more but here we go):

If I understand it correctly I´m trying to build a one to many relationship within my database: A client can make multiple damage requests and each damage request can receive multiple offers. However, each repairshop can only make one offer per damage requests.


One Damage Requests – Many Offers, Every Offer only One Damage Reqeusts.

Where I get lost is how to structure this within my database. I structured it now how can be seen in the image, in which I incorporate the type “Damage Request” as a value in the type “Repair Offer”. As such every offer is linked to a unique Damage Request ID, and I feel it should be like this more or less.

I created a page for the Client in which I want to show which Damage Requests were issued by the Client (Through a repeating group) and then, when selecting a certain Damage Request show, again through a repeating group, which offers were made by Repair Shops. Dave would select Damage Request with unique ID 1, and then see offers from repairshops 1,2,3 respectively, all linked back to him through the same Unique ID which was incorporated as a value within the “Repair Offer” type.

My problem is filtering on the unique IDs which belong to Dave. Let´s say Dave created a request with Unique ID 1 (image) as well asUnique ID 3 (not in image). I would like to make a filter so that all Damage Requests made by Dave will show up in the repeating groups, which subsequently can be selected to show the (in this case 3) offers made by the repair shops.
I hope I made myself clear enough, if not just let me know so I can clarify the situation a bit better.

Thanks a lot!

If I have understood you correctly, I would do something like this:

  • User {email; type; etc} - type can be client or shop
  • Request {requester(User); etc} - requester is the User (client) that placed the request
  • Offer {request(Request); shop(User)} - shop is the User (shop) that made the offer

Your repeating group for Dave’s requests will have a content type of Request and a source of Do a search for … Requests, with Constraint: requester = current user (assuming Dave is the current user). If Dave is not the current user, you can use Current Page User and send a selected user (e.g. Dave) to the page. If you need to refer to Dave by email/unique id, you can of course do this too in your constraint.

Now, the above is a very relational database way of doing things. You may find as you go that you need to add Offers as a list field to your Request table, as it may make it easier to display a list of offers for a request. Personally, I tend to like my databases normalised, so I don’t roll like this :slight_smile:

I hope this helps.

2 Likes

Thank you for taking the time to write a response!

I just got a bit lost reading the last alinea:

Doing this it’s easier to show the offers received in the repeating group with content type request?

And you would organize as I have done/am planning to implement, or is it a bit suboptimal what I’m doing here?

Thanks again!

That last line was just me acknowledging a method that I have seen people recommend a lot, here on the forums, where you have a field that lists a number of related items. It makes sense to do this in some cases and given the way Bubble searches work, it can make things more straightforward. If I were doing this, I would structure the database as I laid out in my bullet points, and then if I got stuck (when doing searches), I would consider adding offers as a list field in Request and possibly other changes.

To be honest, I found it a bit difficult to follow your diagram, sorry. I looked more closely at what you described as your objective and outlined how I would approach it. You may want to compare your approach with mine - perhaps they are already very similar.

Fortunately, for the most part, it is fairly easy and not too disruptive in Bubble to make incremental changes to your data structure as you go, so I wouldn’t worry too much about getting it absolutely perfect from the start.

Haha no worries, I can imagine. I’ll give it a try tonight and will let you know how it ended up

So I can´t get it to work somehow, it´s all good and well, I´m just still not able to filter all of the repair-offers so that the user will only see offers on damage requests that he or she made.

http://pietjepuk.bubbleapps.io

Could you (or anyone else for that matter) maybe have a look in there to see what I´m doing wrong? This is not my real app by the way but just one I use to test all these functionalities, so don´t get upset with the design etc. haha

Please share a link to your editor.

1 Like

You need to put a constraint on your search. Right now it’s bringing back everything.

Based on your data structure, there are two ways you could do this I think:

  1. Do a search for offers - constraint: request is in (do a search for requests - constraint: user is current user). This method doesn’t make use of the offers list that you have in the request table.

  2. Do a search for offers - constraint: offer is in (do a search for requests - constraint: user is current user)'s offers.

I hope this makes sense. It’s late where I am but let me know if you don’t figure it out and I can make some changes in your test app directly tomorrow, if you don’t mind.

1 Like

Great I´ll have a look there again then andwill give a shout if I still find difficulties. I was trying the constraint issue already just that it didn´t work out yet for me. Thanks!

So the issue for me right now is as follows. I added the type “Damage_ Request” as a value in the type Damage_Offers, and as such the unique ID of the requests which are responded to are visible in the table:

However I just can´t seem to find a way to link the Damage_Offers, through this unique ID, to the Damage Requests for which the offer is made.

I am simply looking for a constraint/filter to filter out just those offers which were made on requests made by current_user. Hope this makes sense.

@Timon,

I read your original question and had this typed out, but never posted so some of the responses may have already solved some issues, but since it looks like this is still in progress I’ll post anyway:

Here is how I would structure your data types:

Data Type

  • field label (field type)

Damage Request

  • Offers (list of Repair Offers)
  • Created by (this is built-in User)

Offer

  • Damage Request
  • Repair Shop (Repair Shop)
  1. User creates a new Damage Request.
  2. Repair Shop (or User) sees list of Damage Requests that they have not placed an offer on yet… “search for offer’s damage requests with constraint on search: repair Shop <> current user’s repair Shop”
  3. Repair shop creates an offer, let’s say from a button inside the cell of the repeating group displaying the list in #2. Button is clicked > create new offer: damage Request = current cell’s Request; repair shop = current user’s repair shop > make a change to current cell’s Damage request: list of offer add result of step 1
  4. On the other side, user has their list of Damage requests in an RG: search for Damage requests with constraint created by = current user
  5. Bonus: add a text element in the cell here to display # of offers for this cell’s request with “current cell’s list of offers :count”
  6. Click on a button to show another repeating group. This one is type offer, and the source is blank. To display the list of offers, create a workflow: when button is clicked > show repeating group 2 > display data in repeating group 2, data to display is current cell’s list of offers.

Hopefully this helps!

1 Like

I hope you don’t mind, but I made a quick change to your test app (added a constraint to your offers search). It should now be showing offers for the current user’s requests only. You should still carefully follow @romanmg’s recommendations though, to see where you may be able to improve or optimise your data structure and/or searches.

2 Likes

Will do! Thanks a lot @romanmg and @louisadekoya, as soon as I get off work I’ll look into it again! Thanks for the patience you guys are having, I am trying as much as possible to find my way through the manual and reference, but find it rather limited in some aspects. For example, you (@louisadekoya) mentioned the constraint ‘is in’ on which I couldn’t find any explicate documentation, in general your do a search inside of a do a search was something I hadn’t read about up until you mentioned it.

Anyway, some additional doubts I’m having:

Repairshop is a user type in this context?:

I didn’t really understand what you meant referring to step 1, could you elaborate on this?

Thanks in advance!

I assumed Repair Shop was a separate data type, but if it’s a User, then you can use the built-in “Created By” field to know which User created the offer.

“Result of Step X” gives you the value of whatever Thing is involved in that step. So In my example, your Step 1 action is “Create a new Offer”… in Step 2, you will reference that new offer you just created so that you can add it to the Damage Request’s “List of Offers” field. You’re really saying “Add the New Offer I just created in that step to this list”

1 Like

I just saw you made it work, really awesome!! Now what’s the logic behind the constrained is in? Is it to link the damage request value of the datatype ‘offer’ with the data type ‘damage-request’ in order to identify the user?

Yes, so the search is essentially saying find offers where the request (on the offer record) is in the list of requests made by the current user.

Thanks, I get it now!:smiley:

Been working on it all night and just can´t seem to get it done.

I managed in the end to finish every step except for step 6, in which the offers are displayed in the second repeating group, could you have a look what I´m doing wrong?

Furthermore I was wondering if it would be possible in your step 3, creating an offer by the repairshop, to leave the submit button outside of the repeating group (For my app I want to place a button called “details” to open a pop-up in which some values have to be filled in after which the repairshop submits their offer)?

I wanted to do this but wasn´t sure how I would then use the command "make a change to current cell´s damage since the button wouldn´t be in this current cell any longer.

I noticed that when creating lists in a value the thing isn´t replicated, only the value in the list cell is added everytime. What if I have multiple repair shops and one of them decides to delete a request which other repairshops have also received? Can I just delete the offer in the value without deleted the whole damage requests? Or is there something else I should be doing?

Bedtime for me, in case you could find some time still to respond to my questions I would really appreciate it. I realize I must be quite the slow learner but I just really want to get my head around how these things work.

Hey @Timon, I made some changes to your repeating groups. Let me know if this is what you were looking for, and if so, I’ll explain.

The easiest way to reference individual items from a RG’s list in a workflow is to trigger the workflow from the cell. You could use custom states to get around it, and if no button inside the cell is your preference, I can walk you through it.

You can definitely still open up a popup to add more details to the offer either way.

You wouldn’t want to let the repair shop delete requests. Instead, you should give them a list of possible requests to submit an offer on. If they don’t want to see it, you would “remove” that request from their list, not delete from database.

It’s late where I am too, but wanted to respond with something for now.