A CRM database is a secure, centralized repository of customer information that stores, tracks, and helps analyze customer data and interactions.
Most business people are familiar with a CRM (Customer Relationship Management) system’s front end — the user interface, which consists mainly of lists and forms.
But what does the back end of a CRM system — the database — look like, and why is it helpful for a non-technical user or manager to know about it?
Understanding the database aspect of CRM software helps in managing business requirements with data.
Relational Databases
Much of modern civilization runs on databases. Most databases are relational.
The relational database concept originated in 1970 in a paper by E. F. Codd, who was with the IBM Research Laboratory in San Jose, California.
A relational data model meant that, for the first time, database users would have a consistent experience regardless of how the data was organized in a database.
This would later become critical for the large-scale adoption of enterprise systems such as CRM and ERP.
It would also mean that third-party developers could rely on a consistent API, eliminating the need to constantly respond to underlying changes in data.
CRM Tables
In a relational database, data are organized into rows and columns.
Each set of rows and columns is called a table. A CRM system, for example, stores information about an organization in a Company table. Each row in a Company table contains information about a customer, prospect, vendor, or another Company type.
| Company | Type | Status | State | Website |
|---|---|---|---|---|
| Apex Industries | Customer | Active | CA | www.apexindustries.demo |
| Global Co. | Customer | Inactive | NY | www.globalco.demo |
| Innovate Corp. | Prospect | Active | TX | www.innovatecorp.demo |
| NextGen Tech | Customer | Active | CO | www.nextgentech.demo |
| Bright Ideas | Prospect | Inactive | FL | www.brightideas.demo |
Other tables are related to the Company table. At a minimum, these typically include tables for related Contacts, Activities, Opportunities, and Cases (a.k.a. Tickets).
In a relational CRM database, a user sees company names when looking at contacts, but under the hood, this field is an ID that links the Contact table to the Company table.
| Company | First Name | Last Name | Title | Phone | |
|---|---|---|---|---|---|
| Apex Industries | John | Smith | CEO | john.smith@apexindustries.demo | (555) 123-4567 |
| Global Co. | Maria | Rodriguez | VP of Marketing | maria.rodriguez@globalco.demo | (555) 234-5678 |
| Innovate Corp. | David | Chen | CTO | david.chen@innovatecorp.demo | (555) 345-6789 |
| NextGen Tech | Emily | Patel | Director of Finance | emily.patel@nextgentech.demo | (555) 456-7890 |
| Bright Ideas | Michael | Nguyen | Sales Manager | michael.nguyen@brightideas.demo | (555) 567-8901 |
Different CRM vendors refer to database tables differently. Salesforce and HubSpot call them ‘objects.’ Microsoft refers to them as ‘entities.’
Here are some examples of CRM tables and their purpose.
| Table/Entity Name | Description |
|---|---|
| Companies | The organizations your business interacts with. |
| Contacts | The people your business interacts with. This can include customers, prospects, or other stakeholders. |
| Leads | Potential customers who have expressed interest in your business’s products or services. |
| Opportunities | Potential sales deals. This can include the deal value, expected close date, and other relevant sales pipeline information. |
| Activities | Actions CRM users take with customers, such as emails, phone calls, text messages, and meetings. |
| Cases | Issues and questions your customer service or customer support team needs to address. |
| Products | The products or services that your business sells. This can include the product or service name, description, cost, list price, and other relevant information. |
| Campaigns | Information about marketing campaigns that your business runs. This could include the campaign name, start and end dates, and other relevant information. |
| Quotes | Price quotes that your business provides to customers. This can include the quoted product or service, list price, and associated terms and conditions. |
| Orders | Can be generated from won Opportunities. Sometimes integrated with an invoicing system. |
The Activity or Task table can contain detailed notes about various Contact interactions, including email, phone calls, text messages, and online and in-person meetings.
| CRM User | First Name | Last Name | Date | Activity Type | Notes |
|---|---|---|---|---|---|
| PeteG | John | Smith | 2024-02-01 | Online Meeting | Had a productive Zoom demo with John. He expressed interest in our new product line and mentioned that he’s currently evaluating other solutions. |
| PeteG | John | Smith | 2024-02-03 | Outgoing Email | Hi John, Here is the additional information you requested about our product line. Let me know if you have questions. Pete |
| PeteG | John | Smith | 2024-02-05 | In-Person Meeting |
Had a meeting with John to give him a demo of our product. He was impressed with what he saw and is considering a purchase. |
| SaraR | Maria | Rodriguez | 2024–02-07 | Outgoing Call |
Had a call with Maria to discuss her company’s marketing needs. She was receptive and asked for an initial proposal. |
| SaraR | Maria | Rodriguez | 2024-02-10 | Incoming Text | Sara – Thanks for the quote. I have not spoken to my boss yet about your proposal, but he’ll return from his trip on Monday. |
CRM Field Types
Within each table, a CRM system has a set of default fields. Fields have different types — here is a partial list.
| Field Type | Description |
|---|---|
| Short Text | A string of text with a limited length. |
| Long Text | A string of text that can store hundreds or thousands of characters. |
| Email addresses of customers or leads. | |
| Phone | Telephone numbers of customers or leads. |
| Date/Time | Dates and times related to customer interactions or events. |
| Currency | Monetary values, usually with a specified currency. Commonly used for tracking sales or purchases. |
| Number | Numerical values, either whole numbers or numbers with decimal precision. Used for tracking quantities or amounts. |
| Picklist/Dropdown | A predefined list of values from which only one can be chosen. An example usage is for Account Status, with values such as “Prospect,” “Customer,” and “Vendor.” |
| Multi-Select Picklist | Like the Picklist/Dropdown field type, it allows multiple values to be selected from a predefined list. |
| Checkbox | Similar to the Picklist/Dropdown field type, it allows multiple values to be selected from a predefined list. |
| Lookup | Stores a reference to another record in the CRM, such as a related Contact or Opportunity. |
Adding custom tables & fields to CRM
When new CRM applications are introduced to the marketplace, they are typically rigid in design.
For example, an administrator can’t add custom tables.
A more mature CRM system allows admins to add custom tables, which means more business value.
The ability to create custom tables is part of what makes a CRM system a platform rather than just an application.
For example, a medical device manufacturer may want to track Customer Premises Equipment (CPE) in a custom table.
| Company | Address | City | State | Part Number | Serial Number | Last Service Date |
|---|---|---|---|---|---|---|
| MedTech Innovations | 123 Main Street | San Francisco | CA | MTI-1001 | 12345 | 2022-10-15 |
| BioMed Devices Inc. | 456 Oak Avenue | New York | NY | BMD-2002 | 67890 | 2022-08-20 |
| HealthTech Solutions | 789 Elm Street | Houston | TX | HTS-3003 | 24680 | 2022-12-01 |
| Smart Medical Devices | 101 Maple Street | Miami | FL | SMD-4004 | 13579 | 2022-06-05 |
| FutureMed Corp | 555 Pine Street | Denver | CO | FMC-5005 | 97531 | 2023-01-10 |
Another example is a content marketing asset management system we built out in CRM. Here’s part of the schema.
Almost all CRM systems, even newer ones, allow administrators to create custom fields of various types.
Custom fields can easily be added to standard and custom tables. However, fields can be overused and negatively affect user adoption.
An analysis of business requirements should always precede adding tables or fields. Otherwise, the database structure can be expanded and complicated without purpose.
Data Validation in Fields
Creating custom data validation rules is an essential feature of any database, including a CRM database.
Validation rules result in data consistency and reduce data errors.
A well-known validation rule that’s a default in most systems requires an email address to include an @ sign before the domain name and a period before the top-level domain (.com, .ai, etc.)
Multi-tenant Databases
When CRM first came along, each organization using CRM had its own CRM database running on a server in its office.
When Microsoft’s CRM application became available as a cloud-hosted web app, each customer had its own SQL Server database spun up in the cloud.
Salesforce introduced the concept of a multi-tenant CRM database in the cloud. Thousands of customers can use a shared database rather than a separate one for each customer (of course, the database is architected such that each customer only has access to its data).
Multitenancy is now the norm for cloud CRM databases.
Adding, Viewing, and Updating CRM Records
SQL (Structured Query Language) is a programming language for creating, reading, updating, and deleting data in relational databases like CRM.
SQL queries can be used to filter, sort, and aggregate data. CRM users don’t need to know SQL, but CRM developers do.
A filtered list view a CRM user can create using point-and-click tools uses a SQL query like this behind the scenes.
SELECT Company, Type, Status, Website
FROM Accounts
WHERE State = 'CA';Salesforce has a proprietary variant of SQL called SOQL (Salesforce Object Query Language).
Sometimes, SQL or SOQL queries are needed to extract records that span multiple tables, as point-and-click tools are not robust enough.
For example, let’s say a manager wanted a report of all customers who purchased the ‘Advanced Cyber Projection Bundle,’ sorted by date of purchase and with a calculation of the total amount of money spent.
That SQL query might look something like this.
SELECT Customer.customer_name, Order.order_date, SUM(Order_Products.quantity * Products.unit_price) as total_spent
FROM Customer
JOIN Order ON Customer.customer_id = Order.customer_id
JOIN Order_Products ON Order.order_id = Order_Products.order_id
JOIN Products ON Order_Products.product_id = Products.product_id
WHERE Products.product_name = 'Advanced Cyber Projection Bundle'
GROUP BY Customer.customer_name, Order.order_date
ORDER BY Order.order_date ASC;Types of Databases Used
CRM vendors use databases like Oracle, MS SQL Server, Azure SQL, and PostgreSQL.
Supabase is an open-source project that makes PostgreSQL easier to use. A simple CRM system can be created with only prompts using Bolt.new and Supabase.
For businesses, the ease of extracting data from the database is often more important than the type of database used.
A CRM vendor should provide a native or third-party solution for moving your data to a database you control.
A CRM database contains critical customer and prospect information.
This data allows companies to gain valuable insights into companies, people, and other relationships.
CRM customers can configure database tables, fields, and rules to address specialized business requirements.
