I’m currently working on a project to create an employee directory that encompasses around 100 different organizations. Each organization has its own set of employees, and while most have a single address, a few have multiple locations (up to six addresses).
I’m contemplating the structure of this directory and would appreciate your insights. Here’s my current approach:
Separate Address Table: I’m considering creating a new table specifically for addresses, which would include columns for the organization name and multiple address fields (address 1, address 2, address 3, etc.). This table would then be linked to each corresponding organization.
However, I have a couple of concerns and am open to suggestions:
Alternative Methods: Is there a more efficient way to structure this, perhaps similar to Glide’s multiple images column feature? I’m looking for a method that is both user-friendly and scalable.
Handling Blank Addresses: Since most organizations will use only one address field, I’m worried about the visibility of blank address fields in the app. Is there a way to elegantly handle or hide these unused fields to maintain a clean and professional appearance?
I’m eager to hear your thoughts or examples of similar projects. Any advice on best practices or innovative solutions would be greatly appreciated.
I have structured this database for you. I think this would be the best and easiest approach for you to make a directory like that.
I have created this in a way where you can create multiple organizations. Each organization can have multiple addresses. You can have multiple employees connected to each address.
Do you know for a fact that you could never have more than 6 addresses for an organization, or is it possible that it could be more? I think my general rule is that if you know the max number you could ever have, then you could probably get away with having multiple addresses in the same row as long as it’s not a ridiculous number. If the max number is unknown, then a separate table with an address per row is the way to go.
There’s probably other consideration to, such as additional fields. Is your address broken down into separate parts, or do you type the whole thing into the cell? Do you have additional phone and email fields per location? If you are duplicating a group of columns 6 or more times, then a separate table is probably better.
If you place all addresses into the same organization row, then you would have to set up 6+ components to show each address. A lot of times, empty columns will be automatically hidden, but you also have the option to set visibility conditions on components.
If you have a separate table, then you set things up once and you are done. There are a few ways to display the address. The easiest would be to use a custom collection, which would allow you to display the addresses however you want and repeat for each additional address.
This seems great! In this case, the address would be manually added for each employee? For example, if an organization was logged in, how would they choose an adress for an employee would it just be manually inputed?
As for the employee table, I would never store a piece of information more than once such as an address. It’s best to use and store keys, such as Row IDs. That way you should have an Organization table with a row for each organization and a Row ID column for each organization, an Address table with a column that contains the organization Row ID and a column for the address, and finally an employee table with a column for the organization Row ID, a column for the Address Row ID, and then columns for the employee info.
You can use Relations to link everything up using the stored ID’s, and if you need any of that other information, such as the organization and address displayed on the employee record, you can add Lookup columns to pull data from each relation into the employee table.
Using and storing ID’s means you can change an organization name or address without breaking relations because the ID’s will never change, and it will automatically update what is shown in each Lookup column.
Ah, this makes sense. When I tried it and connected relations, however, there was no way to paste the corresponding ID into the relation tag like I would for a named relation. I think I might be too new at this for IDs.
You store the ID in a basic text column. Then the relation uses that basic text column to establish the connection. Relations do not store values. They only create the link by matching a column value in one table to a column value in another table. Based on what you are showing, you don’t have any columns to store an ID, so I’m not sure what you used to create the relation columns.
