Database Consolidation

Migrating from a multi-base world to a unified database, then syncing it with Noloco, offers many benefits. This guide aims to walk you through the process step by step on how to achieve that

One of the most frequent questions asked by new users of Noloco is whether to adopt a single-app or multi-app strategy.

Most of the time, this question is raised by those

1) bound by the constraints of living in a multiple spreadsheet world

2) Those who have created individual databases or spreadsheets as a mechanism to control data access for a subset of users. For example, creating an Airtable base or a Smartsuite Workspace/App for each Client.

However, by creating a single app connected to a single database, you can focus your energy on improving and iterating on one product rather than spreading your resources thin across multiple platforms. This is especially beneficial for smaller teams where focus and rapid iteration can make or break the success of an application.

In this guide, we walk you through the process of database consolidation, to setting up user roles and permissions in Noloco, giving you the power to control access to your data at scale.

The benefit of using Noloco is that it's dynamic. This means in many cases you can create one app, one design that updates dynamically depending on the logged-in user who is accessing it.

Step 1: Database Consolidation

1. Identify Databases: List all of the databases (i.e Airtable bases, Smartsuite Workspaces, Google Spreadsheets) you currently use.

If you are a Smartsuite User, that has created individual 'Apps' within your Workspace, where each App is shared with an individual client or partner for permission or visibility reasons, this is also relevant for you. See more info below, re: "What is a Database?"

2. Analyse Data: Examine the data schema and commonalities between databases.

The “schema” refers to the columns, column names, data types and rows stored in your database or spreadsheet. Columns in your data source will represent your ‘fields’ once connected to Noloco. The rows of data in your data source will be converted to records.

3. Create a New or Choose your Master Database: Based on the commonalities, create a new consolidated database or choose the main existing database to act as your master database that can hold all of your essential data. This will serve as your 'single source of truth.' Ensure the master database contains all of the columns that exist in your child databases.

What is a Database?

If you are using Airtable, this refers to your Airtable base.

If you are using Google Sheets, your entire worksheet (i.e spreadsheet) will be treated as your database and each tab in your sheet will be treated like a table in Noloco.

If you are using Smartsuite, a workspace will be treated as your database, with each 'App' within your workspace treated like a table in Noloco

4. Migrate Data: Copy the data from the multiple child databases and transfer it into your chosen master database. You could achieve this by exporting the data via CSV from the child databases you plan to stop using followed by importing this data into your selected master database.

Step 2: Create a table for Clients and link them with your existing data

1. Create A Client Table: Navigate to your consolidated data source or spreadsheet. Create a new table or tab and call it something like "Clients" for easy identification. Create fields (i.e columns) that will store essential client information such as First Name, Last Name, and Email address etc. Once complete add in some new records to your table to test with. Or alternatively, import all of your Client details if you have them stored somewhere.

2. Create Linked Tables Records: Navigate to the tables where Clients need to be associated e.g 'Orders' or 'Invoices', etc. Add a new column or field type that allows you to link the Client to each row in the spreadsheet or database. In Airtable or Smartsuite, you will create a new linked field. In Google Sheets, you will need to create a column that will store a unique identifier which references the specific row from the Client tab in your spreadsheet. In Airtable, you will have the ability to choose whether many clients or only one client can be associated with the table of records you're creating the link on.

3. Link Clients to Records: Navigate to each of the tables where Client data needs to be associated. Populate each record with a Client value.

Step 3: Connect the Consolidated Database to Noloco

1. Log in to Noloco: Open your Noloco Dashboard and create a new app or open the app you plan on connecting your consolidated database to

2. Connect Database: Connect your data during the new app creation process OR navigate to the Data Tab in your existing app where you can manage and add external database connections. From here, select ‘New Source’. Follow the prompts to link your consolidated database to Noloco.

Step 4: Create User Roles in Noloco

1. Navigate to 'Users’: Select the Users tab from the admin sidebar. Locate the button to the left of the Add User button represented by three vertical dots. Click this button and select Manage Roles

2. Create Roles: Click on Add a Role and define the any custom user roles you would like to create for your app Users (e.g., Client, Vendor, Freelancer, Manager).

3. Determine Role Settings: For each role, specify what kind of app access they should have—Internal Team Member, Access all Data, Modify App.

Step 5: Sync Users into Noloco

1. Navigate to 'User List Sync’: Locate the button represented with three vertical dots and select Sync your Users. A user list sync allows you to control which users can access your app by syncing a list from one of your tables in your connected database.

2. Choose Source Database: Select the table from your consolidated database that stores your app users as the source for the user list.

3. Configure Sync Settings: Make sure the correct fields for their Name, Email address, and the field criteria to assign the correct User Role is mapped. Press save.

Step 6: Enable Permission Rules for Each Table of Data

1. Navigate to Data Tab: Locate your synced tables from the external database in the Data tab

2. Add Permission Rule: For each table, navigate to the Permissions section and click on 'Enable Permissions' followed by 'Add New Rule.'

3. Set Criteria: In most cases, you will configure a rule to only show records to the logged in user where their User is associated in a linked field somewhere on the record. Thanks to the User list sync you have previously created, Noloco can access this information from your tables.

Step 7: Validate & Test

1. View the app as Different User Roles: Check to ensure that data visibility and permissions work as intended. From the build mode toggle, select the 👥icon and choose the User you want to view the app as.

2. Navigate through your views: Go through each of your app views from the sidebar to verify that the correct records display to the User based on their assigned role and the permissions you have enabled on each table for this role.

Conclusion

By following these steps, you'll move from a fragmented multi-database world to a centralized, well-managed system. Using Noloco's robust feature set, you can effectively manage user roles, permissions, and visibility settings from one dashboard, making your operations more efficient and secureT