Forum Academy Marketplace Showcase Pricing Features

How would YOU structure the database in this case?

Need help Database
#1

This is a dilemma that i keep coming back to when structuring a database, here is the situation:

(thanks for taking the time to understand and answer my question, i tried to make it as clear as possible)

You have different types of things, types A, B, C, etc. each with its own set of attributes to keep track of, thing A doesn’t have the same set of fields as thing B for instance, and they all have its own page for entering editing & viewing.

BUT - here is the but - you still want to have all of those different types as a common list, in other words, you want entries from A B & C to be listed in a single list called D, Why? because all of those types still have something in common, for example the “Name” & “Image” fields, and you would want to display a list or have a search option for entries in A B & C together, but you dont want to have them in a single type, since they all require different attributes and User Interfaces for entering and editing.

Now, the way i do it, is have separate types A B & C, plus a type called D, and each of A B & C has its own set of fields, and D would have the “Name” and “Image” fields which is what makes them common, A B & C will have an additional field called “Name”, field type D, which will associate each entry in A B & C to a single entry in type D as a one-to-one relationship.

Before going to my question, this how i designed the “Add New” page for Type A B or C:

"Add New" page (for Type A): in addition to its own set of input fields, i add an input called 'Name", its value will be saved to the “Name” field in type A, which will automatically save it to Type D since the “Name” field in type A is an associated Field Type of D, i also add an Image input, its value will be saved to type D only.

The problem happens when designing the List/Search Page for D.

"Search Page" (for type D): you have a repeating group for type D to display all listings in type D with its Name and Images, now, when a user clicks on a listing, you want to send him to the details page with the “current cells thing”, BUT since the details page is of type either A B or C, and the current listing page is of type D, bubble wouldn’t let you send data with other types, how would you send over this data to the page?

Would really appreciate if anybody can help me out with this?

#2

Can you add an example of the kind of thing your trying to do… the a b c d thing makes my hair hurt… that and it dosent always communicate the “pitfalls”

#3

For example replace D with listings that users post on craigslist, and A B C replace with different categories of listings, for example A would be an appartment to sell, which would require fields such as “Address”, “# of Rooms”, B would be a car to sell listing, which requires its own set of fields, like “Model” etc.

But you still want all listing to show up on the same Search page, regardless the type of listing, and when a users clicks on a listing u want to take him to the details page which would be either an “Apartment” type, or a “Car” type.

The problem happens when designing the List/Search Page for type “All Listings”.

"Search Page" (for “All Listings”): you have a repeating group for type “All Listings” to display all listings in type “All Listings” with its Name and Images, now, when a user clicks on a listing, you want to send him to the details page with the “current cells thing”, BUT since the details page is of type either “Apartment” or “car”, and the current listing page is of type “All Listings”, bubble wouldn’t let you send data with other types, how would you send over this data to the page?

#4

And btw, as a programmer you shouldn’t be scared of A B C… only kidding…

1 Like
#5

Hi @cheskiefisch

I have some ideas: you could maybe add an other field in type D (let’s say a text which could be A, B or C) in order to know if the type linked to D is A, B or C, and then use a condition to send the right data related to this new field in type D.
So, for instance, when you create a new data A, you create like you said a new data D with the same name but you also will have to write the text “A” in the new text field D.

Here is an example:

Nevertheless, there could be a problem if for instance, 2 cars share the same name, so I thought you could store the data ID in an additional field in type D to be sure you target the right data when you do the search to display the page details (in this case, you will have to replace the constraint on the name by a constraint on the ID value).

#6

will check it out, but for me it seems there should be a more simpler solution, it a problem that every developer should bump into often.

#7

as a dba A B C frightens the hell out of me :wink:

lol

1 Like
#8

Hey there!

I hope you are doing well!

I was just reading your note. I absolutely adore A and B and C. They rock. Hey, the way that I’d structure a database in your situation depends on a few things. Are there only EVER going to be good ole’ AB&C? OR is your system going to eventually expand to include more things than that? Let me know and I’ll shoot you my thoughts :smiley:

I like the way you’re thinking about the D table.

I’d like the community get more into leveraging indexes and agnostic containers (tables with fields named amazing things like Field #1, Field #2 and Field #3)… There are tremendous advantages to thinking of databases in different ways :smiley:

So, cheski, are you going to grow your DB over time, or are ABC written in stone?

#9

:joy:

#10

Its not written in stone, i might add more types,

Can u talk more about that indexing thing? maybe it might help me and others, i’m always struggling to decide how to structure a database in certain situations, based on how i want to query and display the data down the road.

#11

If a posting will only ever have one “thing” (car apartment etc) associated with it I’d go wide. One table with all of the columns for the different types. Joining tables in bubble can be a pain, so why do it if you don’t need to.
, and if it’s not a one to many relationship you don’t gain much.

#12

agreed

#13

Hey man :smiley:

Have a look at this post.

On STRUGGLING TO DECIDE about your DB format… I think just Develop quickly, have fun playing in the editor which will also improve your fluency in the system… That way you can launch quickly, make mistakes quickly and change quickly. Remember, this platform is SUPER efficient in that we can implement changes super quickly. So I vote for just having an idea, make it happen bro and worry about optimizing in the future. Sketch, Launch, Optimize, repeat! :smiley:

On INDEXING: Have a boo at this post: Creating a data field dynamically
Indexing is THE BEST way to quickly improve the speed and efficiency of your DB’s performance.

On LISTS: I’d argue that leveraging LISTS is the second best step to optimize your performance… That is to say, your clients can be playing in your creation and interacting with your data quickly when changes are made to a STATE STORED LIST, rather than the clunky updating to a DATABASE and waiting for those values to come back to you. Of course you need to manage your data carefully, by divorcing, yet monitoring the flow of data… (ie. when do you push updates to your DB?).

Finally, I encourage you to just follow the idea that you’re having… ABC & D… make it, move on to creating other features… and when you discover that your solution is deficient in some way, drop me a line and I’d gladly SKYPE/HELP you optimize your shiznit :smiley:

Peace bro!

1 Like