# 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](https://guides.noloco.io/data/collections/field-types/date), [Number](https://guides.noloco.io/data/collections/field-types/number-integer), [Email](https://guides.noloco.io/data/collections/field-types/text), 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](https://guides.noloco.io/data/collections/formulas) and [Rollup fields](https://guides.noloco.io/data/collections/rollups) 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](https://guides.noloco.io/data/collections/field-types/single-option-select) fields ensure consistent categories, no more "High", "high", and "HIGH" variations).
* **Powerful filtering and permissions**: Show different users different data based on [permissions](https://guides.noloco.io/users-and-permissions/user-roles-and-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   | <john@acme.com>  | Website Redesign | In Progress    | 2024-03-15       |
| Acme Corp   | <john@acme.com>  | Mobile App       | Planning       | 2024-06-01       |
| TechStart   | <sarah@tech.com> | Logo Design      | Completed      | 2024-01-20       |

Notice how "Acme Corp" and "<john@acme.com>" 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            |
| --------- | ---------------- |
| Acme Corp | <john@acme.com>  |
| TechStart | <sarah@tech.com> |

**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:**

* **Company Name** ([Text](https://guides.noloco.io/data/collections/field-types/text))
* **Industry** ([Single Select](https://guides.noloco.io/data/collections/field-types/single-option-select): Technology, Healthcare, Finance, etc.)
* **Website** ([Text](https://guides.noloco.io/data/collections/field-types/text))
* **Notes** ([Long Text](https://guides.noloco.io/data/collections/field-types/text))
* **Contacts** ([Linked](https://guides.noloco.io/data/collections/relationships) to Contacts table - allows multiple)

#### 2. **Contacts** Table

This stores individual people at each company.

**Fields:**

* **Full Name** ([Text](https://guides.noloco.io/data/collections/field-types/text) or [Full Name field](https://guides.noloco.io/data/collections/field-types/full-name))
* **Email** ([Text](https://guides.noloco.io/data/collections/field-types/text))
* **Phone Number** ([Phone Number field](https://guides.noloco.io/data/collections/field-types/phone-number))
* **Job Title** ([Text](https://guides.noloco.io/data/collections/field-types/text))
* **Company** ([Linked](https://guides.noloco.io/data/collections/relationships) to Companies table - single link)
* **Deals** ([Linked](https://guides.noloco.io/data/collections/relationships) to Deals table - allows multiple)

#### 3. **Deals** Table

This tracks sales opportunities.

**Fields:**

* **Deal Name** ([Text](https://guides.noloco.io/data/collections/field-types/text))
* **Value** ([Number - Decimal](https://guides.noloco.io/data/collections/field-types/number-decimal))
* **Status** ([Single Select](https://guides.noloco.io/data/collections/field-types/single-option-select): Prospecting, Proposal, Negotiation, Closed Won, Closed Lost)
* **Close Date** ([Date](https://guides.noloco.io/data/collections/field-types/date))
* **Contact** ([Linked](https://guides.noloco.io/data/collections/relationships) to Contacts table - single link)
* **Company** ([Lookup](https://guides.noloco.io/data/collections/lookup-fields) from Contact → Company)

### 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](https://guides.noloco.io/data/collections/lookup-fields), 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](https://guides.noloco.io/data/collections/rollups) 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](https://guides.noloco.io/data/collections/field-types/text))
* **Description** ([Long Text](https://guides.noloco.io/data/collections/field-types/text))
* **Status** ([Single Select](https://guides.noloco.io/data/collections/field-types/single-option-select): Planning, Active, On Hold, Completed)
* **Start Date** ([Date](https://guides.noloco.io/data/collections/field-types/date))
* **End Date** ([Date](https://guides.noloco.io/data/collections/field-types/date))
* **Project Manager** ([Linked](https://guides.noloco.io/data/collections/relationships) to Team Members table - single link)
* **Tasks** ([Linked](https://guides.noloco.io/data/collections/relationships) to Tasks table - allows multiple)

#### 2. **Tasks** Table

**Fields:**

* **Task Name** ([Text](https://guides.noloco.io/data/collections/field-types/text))
* **Description** ([Long Text](https://guides.noloco.io/data/collections/field-types/text))
* **Status** ([Single Select](https://guides.noloco.io/data/collections/field-types/single-option-select): To Do, In Progress, Review, Done)
* **Priority** ([Single Select](https://guides.noloco.io/data/collections/field-types/single-option-select): Low, Medium, High, Urgent)
* **Due Date** ([Date](https://guides.noloco.io/data/collections/field-types/date))
* **Project** ([Linked](https://guides.noloco.io/data/collections/relationships) to Projects table - single link)
* **Assigned To** ([Linked](https://guides.noloco.io/data/collections/relationships) to Team Members table - single link)
* **Time Estimate** ([Duration field](https://guides.noloco.io/data/collections/field-types/duration): hours)
* **Subtasks** ([Linked](https://guides.noloco.io/data/collections/relationships) to Tasks table - allows multiple, links to same table)

#### 3. **Team Members** Table

**Fields:**

* **Name** ([Text](https://guides.noloco.io/data/collections/field-types/text) or [Full Name field](https://guides.noloco.io/data/collections/field-types/full-name))
* **Email** ([Text](https://guides.noloco.io/data/collections/field-types/text))
* **Role** ([Single Select](https://guides.noloco.io/data/collections/field-types/single-option-select): Designer, Developer, Manager, etc.)
* **Projects** ([Linked](https://guides.noloco.io/data/collections/relationships) to Projects table - allows multiple)
* **Tasks** ([Linked](https://guides.noloco.io/data/collections/relationships) to Tasks table - allows multiple)

### 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**](https://guides.noloco.io/data/collections/rollups) on Projects: Count how many tasks are "Done" vs total tasks
* [**Rollup field**](https://guides.noloco.io/data/collections/rollups) on Projects: Sum up time estimates to see total project hours
* [**Rollup field**](https://guides.noloco.io/data/collections/rollups) on Team Members: Count how many tasks are assigned to each person
* [**Formula field**](https://guides.noloco.io/data/collections/formulas) 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](https://guides.noloco.io/pages/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](https://guides.noloco.io/data/collections/field-types/text))
* **Primary Contact Name** ([Text](https://guides.noloco.io/data/collections/field-types/text) or [Full Name field](https://guides.noloco.io/data/collections/field-types/full-name))
* **Email** ([Text](https://guides.noloco.io/data/collections/field-types/text)) - This will be used for login
* **Phone** ([Phone Number field](https://guides.noloco.io/data/collections/field-types/phone-number))
* **Account Status** ([Single Select](https://guides.noloco.io/data/collections/field-types/single-option-select): Active, Inactive, Trial)
* **Projects** ([Linked](https://guides.noloco.io/data/collections/relationships) to Projects table - allows multiple)
* **Invoices** ([Linked](https://guides.noloco.io/data/collections/relationships) to Invoices table - allows multiple)

#### 2. **Projects** Table

**Fields:**

* **Project Name** ([Text](https://guides.noloco.io/data/collections/field-types/text))
* **Description** ([Long Text](https://guides.noloco.io/data/collections/field-types/text))
* **Status** ([Single Select](https://guides.noloco.io/data/collections/field-types/single-option-select): Not Started, In Progress, Review, Completed)
* **Start Date** ([Date](https://guides.noloco.io/data/collections/field-types/date))
* **Deadline** ([Date](https://guides.noloco.io/data/collections/field-types/date))
* **Client** ([Linked](https://guides.noloco.io/data/collections/relationships) to Clients table - single link)
* **Files** ([Linked](https://guides.noloco.io/data/collections/relationships) to Files table - allows multiple)
* **Tasks** ([Linked](https://guides.noloco.io/data/collections/relationships) to Tasks table - allows multiple)

#### 3. **Tasks** Table

**Fields:**

* **Task Name** ([Text](https://guides.noloco.io/data/collections/field-types/text))
* **Description** ([Long Text](https://guides.noloco.io/data/collections/field-types/text))
* **Status** ([Single Select](https://guides.noloco.io/data/collections/field-types/single-option-select): To Do, In Progress, Done)
* **Due Date** ([Date](https://guides.noloco.io/data/collections/field-types/date))
* **Project** ([Linked](https://guides.noloco.io/data/collections/relationships) to Projects table - single link)
* **Client** ([Lookup](https://guides.noloco.io/data/collections/lookup-fields) from Project → Client)

#### 4. **Files** Table

**Fields:**

* **File Name** ([Text](https://guides.noloco.io/data/collections/field-types/text))
* **File** ([File/Upload field](https://guides.noloco.io/data/collections/field-types/file-upload))
* **Description** ([Long Text](https://guides.noloco.io/data/collections/field-types/text))
* **Upload Date** ([Date](https://guides.noloco.io/data/collections/field-types/date) - can be auto-filled)
* **Project** ([Linked](https://guides.noloco.io/data/collections/relationships) to Projects table - single link)
* **Client** ([Lookup](https://guides.noloco.io/data/collections/lookup-fields) from Project → Client)

#### 5. **Invoices** Table

**Fields:**

* **Invoice Number** ([Text](https://guides.noloco.io/data/collections/field-types/text))
* **Amount** ([Number - Decimal](https://guides.noloco.io/data/collections/field-types/number-decimal))
* **Status** ([Single Select](https://guides.noloco.io/data/collections/field-types/single-option-select): Draft, Sent, Paid, Overdue)
* **Issue Date** ([Date](https://guides.noloco.io/data/collections/field-types/date))
* **Due Date** ([Date](https://guides.noloco.io/data/collections/field-types/date))
* **Client** ([Linked](https://guides.noloco.io/data/collections/relationships) to Clients table - single link)
* **Project** ([Linked](https://guides.noloco.io/data/collections/relationships) to Projects table - single link)

### 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](https://guides.noloco.io/users-and-permissions/user-roles-and-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](https://guides.noloco.io/users-and-permissions/user-table)
2. Create a "Client" user role
3. Set [record-level permissions](https://guides.noloco.io/users-and-permissions/user-roles-and-permissions/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](https://guides.noloco.io/data/collections/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](https://guides.noloco.io/data/collections/rollups).

### 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](https://guides.noloco.io/data/collections/formulas).

***

## 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](https://guides.noloco.io/data/collections) 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?](https://guides.noloco.io/data/data-overview/what-are-tables) - More fundamentals about tables
* [Relationships](https://guides.noloco.io/data/collections/relationships) - Deep dive into linking records
* [Rollup Fields](https://guides.noloco.io/data/collections/rollups) - Calculate across linked records
* [Lookup Fields](https://guides.noloco.io/data/collections/lookup-fields) - Pull data from linked records
* [Field Types](https://guides.noloco.io/data/collections/field-types) - All available field types
* [User Roles & Permissions](https://guides.noloco.io/users-and-permissions/user-roles-and-permissions) - Control who sees what data