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.