Database structure and relationship between tables

stephanegroud · 2024-11-10T15:04:33.355Z

Hello! I am new to Bubble. I love it!

I am now starting to work on more complicated databases and I am struggling to find a good way to link tables the right way.

I have a table of events. The only unique field to reference an entry in this table of events is the unique ID. But if you use a combination of 3 of the fields in that same table, you get a unique entry. How can I retrieve an entry in this table without using unique ID which is not very understandable but instead a combination of these 3 fields? Is that desirable? Can a concatenation of these 3 fields create a 4th field in the database that is unique and autogenerated and that I can use instead of the unique ID?

This would allow me to easily refer to an event from this first table (without using unique ID) into a second table that records registrations, bringing together participant data (a third table) and event data (from my first table).

I hope this makes sense. Thanks in advance for your help

AndyMountHood · 2024-11-10T16:06:07.138Z

In Bubble, the usual way to link data types is to put one inside the other as one of its fields. You’re actually using the ID but normally you wouldn’t see it. For example, imagine you have a data type called Merchant and one called Product. Inside Product, you would make Merchant one of its fields. Bubble handles all the linking in the background. You don’t have to think about ID’s and keys.

georgecollier · 2024-11-10T16:13:16.056Z

Event

event name (text)
event date (date)
etc

Registration

Event (Event)
User (User)
date registered (date)
etc

To show Current User’s registrations for a particular event:

Do a search for Registrations where User = Current User and Event = (The event)

To show all registrations for a particular event:

Do a search for Registrations where Event = (The event)

stephanegroud · 2024-11-10T16:53:18.569Z

Thanks George. I am trying to avoid having an event name but instead make an event unique based on 3 different fields (year, country, distance). The same distance can be done in different countries in different years and so is not unique. But the combination of a year, country and distance will make a unique event. To ensure I don’t have to unnecessarily manually maintain a field that would regroup all 3 info, I am looking for a way to concatenate these 3 values into a 4th field that can become the link between event and registration. Does that make sense?

stephanegroud · 2024-11-10T16:54:29.963Z

Thanks Andy. I understand what you mean but my requirement means that I don’t have a 1-2-1 relationship. Please see my reply to George below. Maybe I am making this more complicated than it needs to be

AndyMountHood · 2024-11-11T03:50:32.027Z

stephanegroud:

To ensure I don’t have to unnecessarily manually maintain a field that would regroup all 3 info, I am looking for a way to concatenate these 3 values into a 4th field that can become the link between event and registration.

Your plan sounds good to me.

For one thing, you need to make sure that the combination is unique when a new event is added to the database. You can prevent the same event from being entered into the database twice accidentally. Before a user (or admin?) is allowed to enter a new event into the database, the three can be concatinated into a custom state, then the concatenated string can be searched in the database to see if it’s already there in the fourth field. If so, you can show the existing event rather than allowing the user to add a new one. When adding an event, you would add a new thing (entry) with all three fields plus the concatenated field. I’m sure it’s not the only way to do it, but it sounds like a simple way to me. The tricky part might be typos, so use dropdown lists (from option sets or separate data types) instead of letting users type anything in those three fields.

You certainly could use the 4th field to link the two data types by adding the field to both, if you wish. But it might not be necessary. Once you’ve identified the event to connect the registration to, Bubble will probably use the hidden ID field to connect them behind the scenes anyway–if you connected the tables in the way I described.

I’m guessing that :append is the operator you seek in order to concatenate the fields together.

(I apologize if I’m repeating myself. I’m feeling worn out after a day of chores.)

manual.bubble.io

Operators and comparisons | Bubble Docs

Aggregating, manipulating and comparing data.

stephanegroud · 2024-11-11T09:32:16.011Z

Thanks Andy. Works like a charm. And append is the right command. Many thanks for taking the time! I love Bubble and its community