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. …

[image] Best Option for User Selection Lists (Single-Select or Multi-Select) Ask for Help 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 c…

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?

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 m…

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 mi…

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…

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.

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

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.

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.

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 da…

How to plan out your database

Ask for Help

Jeff_Hager December 15, 2023, 4:15pm 2

Topic		Replies	Views
Best Option for User Selection Lists (Single-Select or Multi-Select) Ask for Help	13	933	November 6, 2023
Choices: one single table or one table per choice component Ask for Help	6	472	November 24, 2022
Data structure help for invoicing, If/then/else queries and other things Ask for Help	9	46	December 27, 2024
App maintenance/Clutter clean-up Ask for Help	5	718	July 11, 2022
How do I have form components without writing? Ask for Help	18	315	June 20, 2023

How to plan out your database

Related topics