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:
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:
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:
Acme Corp
TechStart
Projects Table:
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:
Company Name (Text)
Industry (Single Select: Technology, Healthcare, Finance, etc.)
Website (Text)
Notes (Long Text)
Contacts (Linked to Contacts table - allows multiple)
2. Contacts Table
This stores individual people at each company.
Fields:
Full Name (Text or Full Name field)
Email (Text)
Phone Number (Phone Number field)
Job Title (Text)
Company (Linked to Companies table - single link)
Deals (Linked to Deals table - allows multiple)
3. Deals Table
This tracks sales opportunities.
Fields:
Deal Name (Text)
Value (Number - Decimal)
Status (Single Select: Prospecting, Proposal, Negotiation, Closed Won, Closed Lost)
Close Date (Date)
Contact (Linked to Contacts table - single link)
Company (Lookup from Contact → Company)
How the relationships work:
Companies ↔ Contacts: One company can have many contacts. Each contact belongs to one company.
Contacts ↔ Deals: One contact can have many deals. Each deal is associated with one primary contact.
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:
Task Name (Text)
Description (Long Text)
Status (Single Select: To Do, In Progress, Review, Done)
Priority (Single Select: Low, Medium, High, Urgent)
Due Date (Date)
Project (Linked to Projects table - single link)
Assigned To (Linked to Team Members table - single link)
Time Estimate (Duration field: hours)
Subtasks (Linked to Tasks table - allows multiple, links to same table)
3. Team Members Table
Fields:
Name (Text or Full Name field)
Email (Text)
Role (Single Select: Designer, Developer, Manager, etc.)
Projects (Linked to Projects table - allows multiple)
Tasks (Linked to Tasks table - allows multiple)
How the relationships work:
Projects ↔ Tasks: One project has many tasks. Each task belongs to one project.
Team Members ↔ Projects: Team members can work on multiple projects. Projects can have multiple team members.
Team Members ↔ Tasks: Team members can be assigned to multiple tasks. Each task is assigned to one person.
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:
Company Name (Text)
Primary Contact Name (Text or Full Name field)
Email (Text) - This will be used for login
Phone (Phone Number field)
Account Status (Single Select: Active, Inactive, Trial)
Projects (Linked to Projects table - allows multiple)
Invoices (Linked to Invoices table - allows multiple)
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:
Task Name (Text)
Description (Long Text)
Status (Single Select: To Do, In Progress, Done)
Due Date (Date)
Project (Linked to Projects table - single link)
Client (Lookup from Project → Client)
4. Files Table
Fields:
File Name (Text)
File (File/Upload field)
Description (Long Text)
Upload Date (Date - can be auto-filled)
Project (Linked to Projects table - single link)
Client (Lookup from Project → Client)
5. Invoices Table
Fields:
Invoice Number (Text)
Amount (Number - Decimal)
Status (Single Select: Draft, Sent, Paid, Overdue)
Issue Date (Date)
Due Date (Date)
Client (Linked to Clients table - single link)
Project (Linked to Projects table - single link)
How the relationships work:
Clients ↔ Projects: One client can have many projects. Each project belongs to one client.
Projects ↔ Tasks: One project has many tasks. Each task belongs to one project.
Projects ↔ Files: One project can have many files. Each file belongs to one project.
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:
Make your Clients table your User Table
Create a "Client" user role
Set record-level permissions so clients can only view/edit records where the Client field equals the logged-in user
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:
What are Tables? - More fundamentals about tables
Relationships - Deep dive into linking records
Rollup Fields - Calculate across linked records
Lookup Fields - Pull data from linked records
Field Types - All available field types
User Roles & Permissions - Control who sees what data
Last updated
Was this helpful?