What is the most effective approach for managing lists used for user selection? Is it better to have a single table where the different option types are listed in separate fields or columns, or should multiple tables be used, one for each type? What are the advantages of each option?
As I continue to develop the project, I find that I’m incorporating a combination of both options as I occasionally reconsider my initial decision.
I have a table that serves as a choices table. Each column is a separate choice unrelated to the other columns. This way saves on rows. The only thing to be careful about is that I can’t just delete a row to remove a choice, because it might delete other choice values in the same row. So, maintenance of a table like that might require a little more finesse to prevent mistakes, but I think I would still prefer that over individual tables for each type of choice. I manually maintain the table through the data editor or the google sheet.
I think the only advantage of separate tables is that it would be easier to add and remove rows without affecting anything else, but then you have several more tables to deal with and now rows since each value would have is own row.
Another advantage is that if you ever need to do aggregation or reporting on the list items, then you can do most of the work in the same table. This can get horribly messy if you try to do it in a generic choices table. Especially once you start trying to do it for multiple lists.
For the above reasons, these days I tend to lean towards separate tables. I still have a generic lookup/choices table, but I usually think pretty carefully about what I put in there. If I think there’s a fair chance I might need to do any sort of reporting on a list, then I’ll whack it in its own table. I generally don’t care too much about row count in these cases. They’re usually fairly short lists, and I think having everything a bit more organised is worth the extra hit.
This.
Great point for large lists
I see value in keeping small lists that don’t change much, if ever, like;
Home, Work, … or
Active, Inactive, … etc
For fear of deleting anything, I have set up these tables to include the different types in the same table but in different columns against records that are not currently being used for other types. Then when referencing any type, I just filter out blanks.
Just not sure if this makes sense from a scalability perspective.
This here is what I’m always thinking about, and unfortunately, it’s not always easy to ascertain early in the planning or development stages.
Same. I seldom have doubts about keeping tables separate when they have distinct, specialized data, are extensive, or demand modifications and reporting over time.
My dilemma usually revolves around the straightforward, standard, and small lists, like “Active/Inactive” or “Home/Work.” I’m curious if you also prefer to keep these simple lists separate.
Hello Nathalie.
Like Jeff, I used to have one single Choices table and each column was a very separate set of choices. That is akin to having multiple tables in one, because the columns are totally unrelated. I stopped doing this for a few reasons:
As I’ve improved using Glide, though this wasn’t the case before, I’ve started to need multiple columns for each choice: a descriptive name, a RowID as its primary key, a human-readable alternate key (I’m still on the fence with this one), sometimes an image or hero icon for the visual. So now I have a minimum of 3 columns per choice. I started feeling uneasy about grouping sets of columns for choices that were totally unrelated.
I’ve never figured out how to generate multiple RowID columns in a table, so then I’d be using the same RowIDs for totally unrelated elements and that just feels oh so wrong.
It’s never happened, but if I would then need to relate one choice to another, I wouldn’t be able to because of the issue with RowIDs (I use those for relations).
For myself, I’m very interested in developing clean building techniques, even if that is at the cost of rows and updates. Darren shared with me a video about database normalization, and I keep this in mind all the time when setting up tables, it helps me a lot. Irrespective of Glide, I feel that separating tables for choices follows database normalization, and conversely, putting all choices in one single table does not (especially because of the RowIDs, see 2. above).
Reporting is another good point from Darren and Bob, though I don’t think I’ve come across this. Yet another good reason though to keep choices in separate tables.
I’m not saying that storing choices each in their own table is better, I’m just saying for now for me I prefer it. Until I change my mind.
Ah. Well that is exactly the type of question I ask myself. For very simple choices such active/inactive, currently I put them in a General Lookup/Choices table. For the past few weeks I’ve been struggling to decide if I should have two separated General Lookup and General Choices tables, or a single one, and how these tables might related to my General Settings table which I use as the data source for most tabs.
I’m still pondering all of this, so I find this topic you created very useful, thank you.
Just to further clarify my use case, I have around 15 different types of choices in my choices table. It would be impractical for me to split them out into 15 separate tables. Yes, some of them do involve multiple different columns, so in total I might have 30 columns. It works well for me but may not for everyone.
I don’t do any kinds of aggregation based on my choices, and most of them are pretty simple and static.
@Darren_Murphy and @nathanaelb have shared some very good points for using separate tables. In rare cases, I do use separate tables for my list of choices, but it really depends on the situation. If the list of choices are a little more dynamic and can change often, then I might be inclined to use a separate table.
In all honesty, if it comes down to it and you need to move choices from a shared table to their own table, you should be able to use the Find All Uses feature to find everywhere a column is used and swap it out for another one.
THanks for sharing @nathanaelb
I’m thinking of changing my ways as well, for most of the same reasons you mentioned. By incorporating the use of grouping (“/”), my choice table could look something like this
In this example, we have two categories for emails: PERSONAL and BUSINESS. Additionally, for phone numbers and addresses, we could use the same list option and have HOME and WORK as categories. However, all three can potentially share the same image.
Or would it be best to have four tables, each specific to a single use?
This is a real-life example of a project that I’m currently working on. It involves a data migration, and it’s important to me that I minimize the adjustments required by end-users while maintaining the existing structure and scalability.
@Jeff_Hager - You’re entirely correct. There are use cases for each, and it’s never too late to switch. For me, I lean towards a single table for simplistic choices that are probably never going to change and leave the dedicated table for lists that will have varying degrees of changes and might need archiving choices.
We probably share the same DNA. 
For your own sake I hope for you we do not. I will go to bed thinking about Glide stuff, and the next morning my partner will ask why I look so tired. All I can tell her is “I was thinking.”
Same! I wake up sometimes feeling like I’ve put in a full day’s work before my feet touch the ground. My brain never shuts down, which is excellent at times because I’ve solved many complex problems during those ‘working sleep’ states.
I find your table interesting.
In the PhoneType group, you skipped two rows so that the Mobile/Landline choices would have their own RowIDs? I had never thought of that.
Just to understand your screenshot:
If these are 2 different choices, for my own sanity and to get more sleep at night, I think I would split things up in two tables: PhoneType and PersonalOrBusiness. This is where I’m at currently in my Glide journey. But I’m not saying this is the right way.
