How to plan out your database

Ask for Help
1

Hey all!

Hope everyone is doing well?

I’m driving myself a little bit crazy with second-guessing about how to architect where the data belongs in my app.

The overall project is quite large (ERP/CRM/Audit Control, Customer Portal etc), but it’s the only way I can get what I want for my business. I’ve split it up into a couple of apps at the moment so I don’t get too complicated.

As a business we do contract bottling. That’s when we bottle for other people’s brand. There’s a lot of little bits of information. I’m trying to figure out how people decide when something should be a table of its own versus an option in a ‘choices’ table.

For clarity (and I think I got this from Robert?), I have a ‘choices’ table, where each column contains a set of choices that the user can pick from. Sometimes a column might be a simple ‘yes’ or ‘no’, and other times the data in the column might be a smidge more - for example a list of different pack type - glass bottles, pet bottles, Bag in Box etc. It’s essentially a transpolated table - instead of being ‘sorted’ by rows it’s being sorted by columns.

Now, the question is when should I use this choices table for data v when should the table be standalone? I’m trying to give myself a ‘hard’ rule to follow as I have a bit of a blind-spot in this area. Instinctively I think ‘well, when the data has lots of information that should be in separate columns’. So, let’s look at the example of a box. It has W, D, H (width, depth, height), weight, single/double wall, branded or unbranded, crash construction or top/bottom tape. I can put that into columns. But then I look at it and think 'but that will be one unique box that will only be identified by other an association with a SKU/Customer or its description. It is a standalone entity in its own right. There is no data to manipulate. I could just have a form which has the questions to ask, then template the answers to a string and stick it in the ‘Secondary Packaging’ column of my ‘Choices’ table.

In this exact example I know that I’d use the dims for pallet layout, so this isn’t perfect as I know that I actually do need to have a table for it, but you get the idea.

So what rules do you have for this kind of thing? I’m worried that my natural instinct of discrete data separation makes me want everything to be in its own table, but then I’ll have probably 2 or 3 dozen tables for ‘primary source’ data, then of calculation tables as well - for example a SKU would consist of a BOM, which would consist of parts etc…

It has the potential to be a table-monster, and I don’t know what rules are normal, and I don’t want to rearchitect it for the third time because I make some kind of foolish mistake.

Thanks in advance for the assist!

2
1 Like
3

Thanks for that Jeff. An interesting read, and I’m glad I’m not the only one who ponders such things all the time!

But to give this a ‘hard’ rule (a lot of the post was soft stuff), then “If you may want to delete data then tables are the way to go”

Is there something else that I’ve missed?

4

In my opinion that’s the biggest rule. If data is static I find it better to just throw everything in one table for simplicity. If data is dynamic, then it’s probably better that it has its own table.

I think it largely comes down to personal preference and each particular situation. There’s a million ways to do the same thing, and a million factors to determine which method is the best. I think it’s impossible to narrow in on one set of standards and best practices that work for everyone. Many of us in the forum have different ways of doing the same thing.

Personally what I do is start with what makes sense to me at the moment. I’ve changed design and database layouts midway countless times when developing apps. I usually just start with a small section of the app and let everything organically fall into place as I build. If I discover better methods or determine better structure as I’m building, then I may go back and make improvements to things I’ve already built. I let the app tell me how it should be built. I don’t spend a lot of time preplanning the design of my apps because there is a good chance that I will change my mind midway through. That’s not the case for everyone and others will preplan everything on paper before they start building. In my opinion requirements will always change and roadblocks will always present themselves, so you have to adapt and make changes. It’s inevitable.

To become locked in on a hard set of design and structure rules means that you can predict what your app will be like months to years in the future. That’s something that is impossible to predict. Apps will continuously evolve as new needs and scenarios arise.

5 Likes
5

This topic is so important and tricky to me that I had to go take a warm shower to clear my head. I too struggle and think about how to structure tables.

A few thoughts in no particular order:

  • Darren taught me database normalization. This has helped me tremendously with database design. Now, I might be making the assumption that tables in Glide need to be normalized, and this assumption might be false. I do try to make sure that my tables in Glide are at least in third normal form. And they probably aren’t. At the very least I try to be aware and attentive to database normalization, I feel it cannot hurt.
  • If really you need a hard rule and if you apply database normalization, then each choice table would need to be a separate table. A table housing unrelated choices would not be normalized.
  • Oscar of the agency V88 has also recommended I create nested tables for choices instead of grouping choices in a general choices table. We didn’t go into detail, but this was his general recommendation when I told him I used to put all of my choices in the same table.
  • As a general rule of thumb, if choices will have a series of attributes (columns), will be the basis of reporting, or will relate to another object via a relation, then the choices need their own table.
  • If the choices are totally generic, for instance yes/no, then I am willing to consider putting such choices in a global lookup/choices table.
  • If we had the possibility to create multiple columns with row IDs, then I would feel more comfortable housing unrelated choices in the same table.
  • Basically, whenever I need to create choices, I default to housing them in their own nested table, and only if the choices are particularly generic, then I consider putting them in a global lookup/choices table (and even then I prefer not to).
  • I know this might sound unusual because I’m not a developer and have never really coded, but I try to imagine what a traditional developer would do if they were coding (not in Glide but with classic code). In some cases I can imagine that the developer will create what I would call local variables for generic choices which leads me to think the choices don’t need their own dedicated table, I put them in a global choices table which we could call a choices dump (they are not too important). In other cases, I can imagine the developer will consider the variables deserve their own dedicated table because they represent non-generic complex objects that will play a role in the application (many attributes, relations, reporting), and in that case I too will give the choices their own table.

I’m not a developer by trade, Glide is the closest thing I’ve done which resembles development, so I’ll be paying close attention to what the real experts and talents here in the forum say.

None of this is from me, all of this is basically a repetition of what I’ve learnt here in the forum and from discussions, because the forum, discussions and building is how I learn Glide.

3 Likes
6

jeff_hager pointed me towards this thread as I was asking similar questions.

I’ve optioned for a choices table, but will break out into separate opt_xxxxx tables for any complex options. I’m already considering restructuring these though so that I link based on row ID rather than the content of the options.

For example, My choices table was
Item, Group, Sort Order
example:
Blue, item-colour,10
Red,item-colour,20
Green,item-colour,30
Small,item-item,10
Large,item-size,20
etc

I’m now thinking I should link everything based on row ID to prevent issues with duplicates and also ensure that links remain if I suddenly decided that “Red” should really be called “Maroon”.

Feels a little unwieldy to do this in the data editor, but once the tool is complete and I am editing in the UI it feels like a big advantage and more inline of how I would build in code.

Are others linking on row IDs?

7

Yes, using Row ID to create links is a good practice. You could change spelling of a color for example which would break any relations, but a Row ID will never change.

8

It’s a shame it’s a bit cumbersome in the UI.
image

This makes it fiddly - especially as there doesn’t seem to be a way to have data open on one screen and layout on another. Seems worth persevering to do it right from the start though

9

Not sure I follow. Users should never see the ID. If you use a choice component for example, you can write the ID, but display the choices as normal text.

10

I meant in terms of me building it out. So, setting those defaults as an example - I need a copy of the table stored locally to look up the IDs. Short term pain though and not a biggy.

11

I see exactly what you’re referring to: you rely on an immutable row ID as the primary key of your row (typically for relations) and then in the layout/action editor using that row ID for conditions, filtering, default values can be cumbersome because you need to switch back and forth between the data, layout, and action editors.

What I’ve done to address this. I haven’t done it often but it’s an idea:

RowID AlternateKey Name
abcde-12345 bldg1 Building 1

The alternate key is a memorable and human-readable key, usually based on the name, that will never change.

I tried doing this only once. The risk is that the values in the AlternateKey could be changed, no matter how unlikely, and I prefer avoiding that risk.

12

That’s an idea. I suspect i will just roll with it for now. I will keep this in mind though in case it turns into an issue. Thanks

1 Like
13

@sumgii or @nathanaelb - would either of you mind explaining this issue to me please?

I’m just not following. I use a RowID in every table I’ve ever created, and I also generally use RowIDs to create relations where it makes sense to do so. But I can’t say I’ve ever hard to “hard-code” a RowID value anywhere - ever. And I’m having difficulty imagining a situation where I would need to.

Educate me please?

14

Hmm… maybe I’m doing something wrong then. I’ve only been using Glide a few days and I’ve done this a lot.

Example: I have a choices table for “task urgency” with options for low, medium and high. This is related to a tasks table through its row ID.
I create an add task form with a choices field for urgency. I want this to default to medium.

Because the UI to set the default shows the RowIDs rather than including human-readable fields, my method is:

  1. Create the choice field with no default
  2. Switch to data
  3. Look up required row and copy ID
  4. Switch back to layout
  5. Edit choices field and paste ID

I suppose it’s mostly cumbersome because of the combination of long rowIDs and not being able to have data and layout in view at the same time. If I were building in something traditional I’d likely have the database on one screen and the code on the other and the IDs would be short sequential numbers.

It’s not a big issue. Just a minor annoyance

1 Like
15

ah, I see.

I have to admit that I’ve never encountered this particular use case before, but I do see what you mean.
I think in this situation - given that the choices are quite simple and unlikely to change - I probably wouldn’t bother with using the RowIDs. One thing to keep in mind with this approach is that anywhere you need the human readable values in other tables, you’ll need a relation & lookup. So taking a more “normalised” approach does come at a cost.

I think it comes down to making a judgement call for each scenario. For example, I would never try to build relations based on volatile values such as User Names or Company Names, but for something simple (and static) like Task Urgency or Task Status, I think it’s fine.

I guess the other thing you could do is something like @nathanaelb suggested - that is, assign a number to each of your Task Urgency values (eg. 1,2,3) and use that. This would make them easier to work with and give you the flexibility to change the labels if you ever need to, without breaking things.

2 Likes