Database Examples for Beginners

If you're coming from Excel or Google Sheets, thinking about databases might feel a bit different at first. In a spreadsheet, you might put everything in one big sheet with lots of columns. But with a database, you split your information into multiple tables and connect them together using relationships (also called linked records or foreign keys).

Why use a database instead of a spreadsheet?

Databases offer several key advantages over spreadsheets:

  • Typed columns prevent errors: Each field has a specific type (Date, Number, Email, etc.), so you can't accidentally enter "abc" in a number field or an invalid date format.

  • Linked records eliminate duplication: Instead of copying customer details across hundreds of rows, you store them once and link to them. Update once, see the change everywhere.

  • Consistent formulas: Formula fields and Rollup fields automatically calculate across all records. No more copying formulas down and accidentally breaking them.

  • Better data validation: Set rules for what data can be entered (e.g., Single Select fields ensure consistent categories, no more "High", "high", and "HIGH" variations).

  • Powerful filtering and permissions: Show different users different data based on permissions without creating separate copies of your data.

  • Scalability: Databases handle thousands of records efficiently, while large spreadsheets become slow and difficult to manage.

This guide will walk you through three common examples to help you understand how to structure your data in Noloco.

Why split data into multiple tables?

In Excel, you might have a sheet that looks like this:

Client Name
Client Email
Project Name
Project Status
Project Deadline

Acme Corp

Website Redesign

In Progress

2024-03-15

Acme Corp

Mobile App

Planning

2024-06-01

TechStart

Logo Design

Completed

2024-01-20

Notice how "Acme Corp" and "[email protected]" are repeated? If John changes his email, you'd have to update it in multiple rows. That's where databases shine—you can split this into two tables and link them.

The database way:

Clients Table:

Name
Email

Projects Table:

Project Name
Status
Deadline
Client (linked)

Website Redesign

In Progress

2024-03-15

→ Acme Corp

Mobile App

Planning

2024-06-01

→ Acme Corp

Logo Design

Completed

2024-01-20

→ TechStart

Now John's email exists in just one place. If it changes, you update it once and all related projects automatically reflect the change.


Example 1: CRM (Customer Relationship Management)

A CRM helps you track companies, contacts, and sales deals. Here's how to structure it:

Tables you'll need:

1. Companies Table

This is where you store information about each company you work with.

Fields:

2. Contacts Table

This stores individual people at each company.

Fields:

3. Deals Table

This tracks sales opportunities.

Fields:

How the relationships work:

  1. Companies ↔ Contacts: One company can have many contacts. Each contact belongs to one company.

  2. Contacts ↔ Deals: One contact can have many deals. Each deal is associated with one primary contact.

  3. Companies ← Deals: Using a Lookup field, you can automatically show which company a deal belongs to by looking through the contact.

Why this structure works:

  • If a contact changes companies, you update one field

  • You can see all contacts at a company instantly

  • You can see all deals for a specific contact or company

  • You can use Rollup fields to calculate total deal value per company


Example 2: Project Management Tool

A project management tool helps teams track projects, tasks, and who's working on what.

Tables you'll need:

1. Projects Table

Fields:

  • Project Name (Text)

  • Description (Long Text)

  • Status (Single Select: Planning, Active, On Hold, Completed)

  • Start Date (Date)

  • End Date (Date)

  • Project Manager (Linked to Team Members table - single link)

  • Tasks (Linked to Tasks table - allows multiple)

2. Tasks Table

Fields:

3. Team Members Table

Fields:

How the relationships work:

  1. Projects ↔ Tasks: One project has many tasks. Each task belongs to one project.

  2. Team Members ↔ Projects: Team members can work on multiple projects. Projects can have multiple team members.

  3. Team Members ↔ Tasks: Team members can be assigned to multiple tasks. Each task is assigned to one person.

  4. Tasks ↔ Tasks (Self-linking): Tasks can have subtasks, creating a parent-child relationship within the same table.

Advanced features you can add:

  • Rollup field on Projects: Count how many tasks are "Done" vs total tasks

  • Rollup field on Projects: Sum up time estimates to see total project hours

  • Rollup field on Team Members: Count how many tasks are assigned to each person

  • Formula field on Projects: Calculate project duration (End Date - Start Date)

The Excel vs Database comparison:

Excel approach (problematic):

  • One row per task with all project and team member details repeated

  • If a project name changes, you update dozens of rows

  • Hard to see all tasks for a specific project or team member

Database approach (better):

  • Project details stored once in Projects table

  • Tasks link back to their project

  • Change project name once, all linked tasks reflect the change

  • Use views to see all tasks for a project, or all tasks for a team member


Example 3: Client Portal

A client portal lets clients log in to see their own projects and files, but nothing from other clients.

Tables you'll need:

1. Clients Table

Fields:

2. Projects Table

Fields:

  • Project Name (Text)

  • Description (Long Text)

  • Status (Single Select: Not Started, In Progress, Review, Completed)

  • Start Date (Date)

  • Deadline (Date)

  • Client (Linked to Clients table - single link)

  • Files (Linked to Files table - allows multiple)

  • Tasks (Linked to Tasks table - allows multiple)

3. Tasks Table

Fields:

4. Files Table

Fields:

5. Invoices Table

Fields:

How the relationships work:

  1. Clients ↔ Projects: One client can have many projects. Each project belongs to one client.

  2. Projects ↔ Tasks: One project has many tasks. Each task belongs to one project.

  3. Projects ↔ Files: One project can have many files. Each file belongs to one project.

  4. Clients ↔ Invoices: One client can have many invoices. Each invoice belongs to one client.

The key insight: Lookup fields for permissions

Notice how Tasks and Files tables have a Client field that's a Lookup? This is crucial for security:

  • The Client field on Tasks looks up through Project → Client

  • The Client field on Files looks up through Project → Client

This means when you set up permissions where clients can only see records where "Client = Logged in User," they automatically see:

  • Their own projects

  • All tasks in those projects

  • All files in those projects

  • Their invoices

You don't have to manually link every task and file to the client—the lookup does it automatically!

Setting up client portal permissions:

  1. Make your Clients table your User Table

  2. Create a "Client" user role

  3. Set record-level permissions so clients can only view/edit records where the Client field equals the logged-in user

  4. Clients log in with their email and only see their own data


Key Concepts to Remember

1. Linked Records (Relationships)

Think of links as connections between tables. Instead of copying the same information across multiple rows, you create it once and link to it.

Types of relationships:

  • One-to-Many: One company has many contacts (but each contact has one company)

  • Many-to-Many: Team members work on many projects, and projects have many team members

Learn more about Relationships.

2. Lookup Fields

Lookup fields pull information from a linked record. For example:

  • A Task is linked to a Project

  • The Project is linked to a Client

  • You can add a Lookup on the Task that shows the Client name by "looking through" the Project link

This is like Excel's VLOOKUP, but it updates automatically!

3. Rollup Fields

Rollup fields calculate something across multiple linked records. For example:

  • Count how many tasks are in a project

  • Sum the value of all deals for a company

  • Calculate the average of all invoice amounts for a client

Learn more about Rollup fields.

4. Formula Fields

Formula fields let you calculate values within a single record, like:

  • Days until a deadline (Deadline - Today)

  • Full name (First Name + " " + Last Name)

  • Project duration (End Date - Start Date)

Learn more about Formula fields.


Tips for Beginners

Start simple

Don't try to build everything at once. Start with 2-3 core tables and add more as you need them.

Plan on paper first

Sketch out your tables and how they connect before building in Noloco. Ask yourself:

  • What are the main "things" I need to track? (These become tables)

  • How do these things relate to each other? (These become relationships)

  • What information do I need about each thing? (These become fields)

Think about "has many" relationships

If you find yourself saying "One X has many Y," that's probably a relationship:

  • One company HAS MANY contacts

  • One project HAS MANY tasks

  • One client HAS MANY projects

Use Noloco Tables when starting out

If you're new to databases, start with Noloco Tables rather than connecting external sources. It's easier to learn the concepts, and you can always migrate later.

Test with real data

Once you've built your structure, add some real data (or realistic test data) and see if it makes sense. It's easier to restructure early than after you've entered hundreds of records.


Next Steps

Now that you understand how to structure your data, check out these guides:

Last updated

Was this helpful?