Gallery
Spreadsheet versus Database
Share
Explore
Gallery
Spreadsheet versus Database
Spreadsheets vs. Databases

icon picker
Databases - A closer look

What's a database, and why should I care?
Nina Kastenauer
The Power of a Database
Questionnaire- Is a database right for me?
A database is a collection of data that is organized so the information within can be easily accessed later.
What that means, basically, is that databases give your information structure. And adding structure to complex work makes any process more transparent and easier to manage.

A database provides structure.

A database unlocks the ability to easily store rich information types, connect important details, and uncover insights that were previously impossible to find in an unwieldy, unstructured spreadsheet.
Your work is changing all the time. Processes are updated, team members change, new types of information needs to be tracked.
In a database, you define which data type can be entered in each field. This means that if a user attempts to enter a ZIP code in a Date field, they will be alerted to their mistake. Simultaneously, you can that some fields are required, thereby making sure that a user won’t forget to submit crucial information.
A centralized database, accessible by everyone on your team, can eliminate all of the time-consuming inquiries any one person has to field every day: “What’s our current progress towards the target? When’s the due date for Project X again? And where can I find the most recent design sketches we sent to client Y?” Everyone can retrieve this information themselves and no one person has to play “information goalkeeper” any longer.

A database relates each piece of data to each other.

Trying to make sense of your data across 16 separate spreadsheets, which can’t easily talk to one another, slows down even the most innovative teams.
In contrast, databases are built on relationships between information, people, and processes (hint: that’s why they’re called “relational” databases).
This allows you to see connections and derive insights you otherwise would have missed.
Because a database can house many different types of data with complex relationships to other types of data, it can also find relationships between these different data types. This offers a business a much more robust and insightful understanding of their information.
Additionally, and crucially, this also means that if you update a due date or value in one place, it’ll automatically update everywhere else. That means less manual process and more trust in your team’s data.

Database Design Principles

Now that you know about all the benefits of a database, it is very likely that you’ll want to rush right into setting up your database.
It does, however, pay to spend some time in advance to contemplate in detail how you should best set up your relational database. Getting the foundation right will save you a lot of headaches in the long run.
Fortunately, there are several design principles you can follow that will help you build better databases.
A well-designed database enforces these two key elements:
Data integrity
Business Logic

Data integrity

Data integrity refers to the overall accuracy, completeness, and consistency of the data in your database.
Data integrity includes three specific technical aspects of a relational database’s structure:
Data integrity on a Table-Level (aka Entity integrity) ensures that a table has no duplicate records, and that the values of the table’s primary keys are all unique and not null.
Data integrity on a Column-Level (aka Domain integrity) ensures that the purpose of every field is clear and identifiable, and that the values in each field are valid, consistent, and accurate.
Data integrity on a relationship-Level (aka Referential integrity) ensures that the relationships between pairs of tables are sound, so that the records in the tables are synchronized whenever data is entered into, updated in, or deleted from either table.

Business Logic

Every company does its work a little differently, and as such, each organization has its own unique requirements for its data, also known as business logic. Some examples might be: a SaaS company requires that all customer data be stored in an encrypted format, a creative agency company requires that all of its assets be stored with a corporate watermark, a online learning platform requires that each employee completes three courses per month for their own education, etc.
Oftentimes, these business rules are not transparent and easily available. Instead, it is the responsibility of the designer of the database to establish these rules through a process of interacting with all stakeholders, asking the right questions and reaching the right conclusions.
For now, suffice it to say that users of a database need to be able to trust that the database is built with the business logic in mind.

Database Design process

Why shall this data be captured?
Define the database’s purpose
What is the current process?
that is, how are users currently collecting, presenting and using data for this workflow?
What are the data’s key parameters?
Based on Step 2, collect what the data’s key parameter’s are (ie the tables you’ll need), such as projects, tasks, etc. Additionally, what are the attributes you’ll need for each table, such as project name, project owner, project due date, etc.
Which data remains largely static, which is dynamic?
Split these up on a spectrum, ranging from “data that changes the least” to “data that changes the most”.
How is the data inter-related?
Establish relationships between tables.
What are the user-specific business rules?
Establish and encode business rules (such as: every team member can only have 3 active projects at any one time).
Check your work
is the database working as it should? A fancy term for this is “database normalization”
To find out, you run three tests:
Update
Do users need to update the same information in multiple places?
If so, it’s back to the design board. Could this be solved with a view or a lookup? Does the data hierarchy have to be revised to make sure that any updates only have to occur in one place?
Insert
Can users insert information without causing an error? Try to purposefully enter “incorrect” information (eg wrong data format, data entry that goes against the defined business logic, etc). Does this result in an error message as planned for? Can the error message be better clarified, so that the user will now how to resolve the issue (eg, use conditional formatting to highlight a blank cell that shall not remain blank)?
Delete
Does it cause an error if a user tries to delete something? Could this be solved with a lookup column? Would it help to replace the delete-action with an archive-action (where rows that are not currently needed are moved to a separate table that contains all archived items, instead of deleting these for good).
⁠

Gallery
Share
 
Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.