Best planning and naming practices for large database

Hello Glide experts!
I am trying to find out if you have any well-tested tips on planning and naming headers in sheets and glide database.

I have made an app that uses 15 sheets and 15-20 columns in each. It becomes challenging to remember and even read these names when one starts adding relations, templates, lookup columns, etc.
For instance, relation between product and manufacturer name could be ‘Name-Name Relation’ making it very difficult to understand and read in glide data. So, one could just name these ‘PName-MName’. The part of relation could be clarified by the relation logo (Digressing a bit here: Colored Logos, like the one for user-specific column, from Glide would helpful but I don’t want to request this as a feature from Glide team as they are already overburdened by such requests).Another naming convention I have found helpful is simply naming Row ID as Sheet’s Initial+ID.

What has your experience taught you? Have you made any diagrams or process flow figures? I want to find your experiences, suggestions, and naming conventions so that we can all make better apps.

@ThinhDinh, @Jeff_Hager, @Rosewebstudio, @Krivo, @mthakershi ld love to hear from you :slight_smile:

Hi,

1/ To follow what I do in the DataEditor, I entitled the columns with:

  • Relations: “(r_1) origin column < [tabname]” ; idem for (r_2) etc
  • Lookups, IfThenElse, Maths, Rollup etc: “(l_r1) target value”, “(i_r1) target value”, “(m_r1) target value”, “(rup_r1) target value” etc.
    This enables me to know exactly (when mousing-over the title) what relates to which sheets and then wich function uses which relation.

2/ To follow what comes from Glide vs GSheet in GSheet (ex. when raw data, pictures etc are directly entered in the DataEditor), I Glide originated columns with “(g_) title”

3/ To avoid deleting formula in GSheet, when it uses an array formula, I usually type them in the title using ={“column title”;arrayformula(formula)}

4/ To distinguish the type of data in GSheet, I use different backgrounds colors in the headline (ex. black for originated from Glide, dark blue for columns with formula, light blue for raw data from GSheet)

1 Like

I have watched enough of @Robert_Petitto 's videos to know he has a good answer for this!

1 Like

Thanks for responding @AyS_0908. Very nice of you to share.

Interesting approaches. A few thoughts & follow up questions:

  1. RELATIONS: How do you differentiate/remember relations on different Glide Data tabs. For example: r_1 on Products sheet could represent Products to Manufacturer relation whereas r_2 on Manufacturer sheet could represent Manufacturer to Product relation. When you read these column names across sheets/tabs, doesn’t it get confusing?

  2. Nice approach of simply adding a “g_” prefix to Glide originated columns.

  3. What’s the advantage of using “column title” in arrayformula vs manually changing column name with prefix like “A_”.

  4. Smart approach! I’ll be using it from now on.

  5. Any preferences on location of columns in Glide data. For instance: I have found it better to keep ID columns and relation columns on left whereas Gsheet raw data columns on right. Do you follow a better practice?

I typically use similar prefixes:

  • rel_emailToUser where email is whatever value is in the current sheet and User is the name of the “User” TAB to which I’m relating. I don’t need to say UserEmail because it’s implied in the “emailTo”.

Likewise, I’ll do lookup_emailFromUser where email is the value I’m looking up from the User tab. For context, I try to always place lookup columns to the right of relation columns.

There are times where I create both multiple and singular relations for the same data for different purposes. I’ll label those as rel_sing_emailToUser or rel_mult_emailToUser

2 Likes

Thanks for sharing @Robert_Petitto. Cool tricks!

Lookup columns right next to its relation makes sense.

Just curious: When does one need single and multiple relation between two columns on same sheet? Can you share an example?

A specific use case is escaping me at the moment, but the next time I use it I’ll tag you in a reply.

1 Like

Much appreciated!

@S_C Thanks for including me in an expert group :slight_smile: But I believe that the people who has already answered are a better source than me, actually. As a business analyst I know I should do some diagrams to line out the information/data model - but I haven’t actually got around to do it.

I do try to use a prefix lie rel_ when I relate to another sheet and use different colors in the google sheet columns depending on whether the column includes calculated or inputted values.

1 Like
  1. I need to think about it!
  2. Don’t get the point of the “A_”. The interest of the column title in arrayformula is to have all key functions in 1st row as often as possible.
  3. I prefer ID columns on the left

Another thing is about recurring ‘standard data’ (ex. yes/no ; mrs, mr … ; priority level…): I create a sheet ‘data list’, and in each sheet/column where this type of data is to be entered, I set-up a “data validation”. It enables to:

  • ensure that I always comply with the same word for any similar columns
  • have always the same sheet for some components (ex.choice component)
  • and be able to quickly identify any mis-typing/entry in case it doesn’t match
1 Like

Nothing much to add here as well, like Krivo, I usually have prefixes like rel_, lookup_, rollup_ etc when I use Glide-generated columns. For the relations, I usually have them with the format.

rel_[the column I want to match]_[the sheet I want to match it to]

e.g: rel_email_profiles

I usually name the related columns in different sheets with the same name so I can find them more easily, unless I have to do array columns. Another thing I usually do is not have the tab names too long so I can fit them in the naming for columns.

1 Like

I don’t have any hard set rules for naming columns in the google sheet. As long as it makes sense, then I name them whatever. As for glide only columns, I tend to prefix them.

  • rel- Relation
  • lkup- Lookup
  • rlup- Rollup
  • mth- Math
  • if- If/Then
  • tmp- Template
  • us- User Specific
  • etc.

When I do relations, I tend to name the relation the sheet I’m attaching to, so if I created a relation in Sheet1 to Sheet2, I would name the relation rel-Sheet2. Again there is some wiggle room on this depending on if I have multiple relations. Likewise with Lookups. Sometimes I will name the the column that I’m looking up (lkup-Column6), or depending on the complexity, I’ll name it lkup-Sheet2Column6. I do tend to reshuffle columns around so the relation is next to the column used for the relation and any lookups/rollups are next to the relation. I just try to keep related columns grouped together.

Sometimes I’ll use a multiple relation on the same sheet to link back to the same record. In this case I expect only a single match, but I use the multiple setting so I can use an inline list to display a profile picture, for example, via a tile or card view with the overlays. A single relation wouldn’t let you do that. In fact I really can’t think of a good use for a single relation to the same sheet, unless you were doing some sort of hierarchy where one row could lead to the next row. I rarely use single relations unless I need a single lookup value from another sheet. Another case for a multiple relation back to the same sheet would be to accumulate a total via the Rollup column of all related rows in the same sheet.

4 Likes