/

/

A CRM Database: The Tables & Fields Behind the UI

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 useful for a non-technical user or manager to know about it?

CRM Databases

Understanding the database part of CRM software helps with knowing how business requirements can be managed 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 and not have to respond to underlying changes in data constantly.

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.

CompanyTypeStatusStateWebsite
Apex IndustriesCustomerActiveCAwww.apexindustries.demo
Global Co.CustomerInactiveNYwww.globalco.demo
Innovate Corp.ProspectActiveTXwww.innovatecorp.demo
NextGen TechCustomerActiveCOwww.nextgentech.demo
Bright IdeasProspectInactiveFLwww.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.

CompanyFirst NameLast NameTitleEmailPhone
Apex IndustriesJohnSmithCEOjohn.smith@apexindustries.demo(555) 123-4567
Global Co.MariaRodriguezVP of Marketingmaria.rodriguez@globalco.demo(555) 234-5678
Innovate Corp.DavidChenCTOdavid.chen@innovatecorp.demo(555) 345-6789
NextGen TechEmilyPatelDirector of Financeemily.patel@nextgentech.demo(555) 456-7890
Bright IdeasMichaelNguyenSales Managermichael.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 NameDescription
CompaniesThe organizations your business interacts with.
ContactsThe people your business interacts with. This can include customers, prospects, or other stakeholders.
LeadsPotential customers who have expressed interest in your business’s products or services.
OpportunitiesPotential sales deals. This can include the deal value, expected close date, and other relevant sales pipeline information.
ActivitiesActions CRM users take with customers, such as emails, phone calls, text messages, and meetings.
CasesIssues and questions your customer service or customer support team needs to address.
ProductsThe products or services that your business sells. This can include the product or service name, description, cost, list price, and other relevant information.
CampaignsInformation about marketing campaigns that your business runs. This could include the campaign name, start and end dates, and other relevant information.
QuotesPrice quotes that your business provides to customers. This can include the quoted product or service, list price, and associated terms and conditions.
OrdersCan 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 UserFirst NameLast NameDateActivity TypeNotes
PeteGJohnSmith2024-02-01Online MeetingHad a productive Zoom demo with John. He expressed interest in our new product line and mentioned that he’s currently evaluating other solutions.
PeteGJohnSmith2024-02-03Outgoing EmailHi John,

Here is the additional information you requested about our product line. Let me know if you have questions.

Pete
PeteGJohnSmith2024-02-05In-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.
SaraRMariaRodriguez2024–02-07Outgoing
Call
Had a call with Maria to discuss her company’s marketing needs. She was receptive and asked for an initial proposal.
SaraRMariaRodriguez2024-02-10Incoming TextSara – 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 TypeDescription
Short TextA string of text with a limited length.
Long TextA string of text that can store hundreds or thousands of characters.
EmailEmail addresses of customers or leads.
PhoneTelephone numbers of customers or leads.
Date/TimeDates and times related to customer interactions or events.
CurrencyMonetary values, usually with a specified currency. Commonly used for tracking sales or purchases.
NumberNumerical values, either whole numbers or numbers with decimal precision. Used for tracking quantities or amounts.
Picklist/DropdownA 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 PicklistLike the Picklist/Dropdown field type, it allows multiple values to be selected from a predefined list.
CheckboxSimilar to the Picklist/Dropdown field type, it allows multiple values to be selected from a predefined list.
LookupStores 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 for adding 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.

CompanyAddressCityStatePart NumberSerial NumberLast Service Date
MedTech Innovations123 Main StreetSan FranciscoCAMTI-1001123452022-10-15
BioMed Devices Inc.456 Oak AvenueNew YorkNYBMD-2002678902022-08-20
HealthTech Solutions789 Elm StreetHoustonTXHTS-3003246802022-12-01
Smart Medical Devices101 Maple StreetMiamiFLSMD-4004135792022-06-05
FutureMed Corp555 Pine StreetDenverCOFMC-5005975312023-01-10

Another example is a content marketing asset management system we built out in CRM. Here’s part of the schema.

Content Marketing Asset 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.

For businesses, the ease of extracting data from the database is 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.

Your CRM Decision. Our Experience.

Project guidance from an independent consultant

Your CRM Project