Database design structure for CRM


I am making a king of crm for Real Estate.

There will be two types of users: Agents and Clients.

This is the design I have in mind:

Table User:

  • ID
  • name
    *typeUser(Agent or Cliente. Reference field)
    *extra dara…

Table Agent:
*user_ID(Reference field)

Tabler Client
*user_ID(Reference field)

This looks ok?

How can I do that in the agent user there would be a agent who created the event and other join?


The User table looks fine. What is the purpose of the Agent and Client tables?

I’d personally make one Type table (instead of two separate) with 2 columns:
user_ID (foreign key)
type (agent or client)
I don’t understand your question so maybe I could be of better help if you rephrased it.

CRM DB design has been done thousands of times. I’d recommend a Google search, even Google image search for examples. It’s pretty straight forward once you get your head around it - but equally important to get it right to ensure scalability and performance.

Thanks for the fast answer.

I thought the same. But after spending 2 days learning about database design i saw that people separate almost everything. Because that improves the speed and performance.

The agents will make a list of houses to visit. And the cliente, the visitor, can see that houses on his side. A example can be a uber app. There is a restaurant side and the buyer side.

The last part is when a agent is working with more agents on the same client. Something like Asana.


I know, I have been 2 days only learning about database. The problem, everything is related when you code the database.

Than I saw that Wappler does a lot of things for you. For example, in normal database, you will create a new table for special relation. In Wappler does it automatic. That is way I ask.


Indeed, separation is good practice.
But some info can be grouped into categories, because they belong to the same type of information.
Here is a more accurate suggestion / example:

  1. user table (ID, email, phone, DOB…) = all general info that pertains to all users, both Agent and Client.
  2. userType table (userType_ID, user_ID, Agent, Client, Admin, Affiliate,…) = pertains to profile definition of all users.
  3. agentListing table (agentListing_ID, user_ID* or userType_ID*, house or apt, location, size,…) = pertains to description of all house listings.
  4. clientLikes table (clientLikes_ID, user_ID* or userType_ID*, agentListing_ID*) = pertains to all houses that have been liked by client.
  5. clientRequest table (clientRequest_ID, user_ID* or userType_ID*, agentListing_ID*, clientQuestion [text], requestDate,…) = pertains to all listings that are the subject of a client’s request.

This is just an example of how you could organize the information in your DB. The info is separated by family of category.
(note: the asterix are foreign keys - what you call reference keys. They link the data to the same data in the original table and prevent wrong input / data mismatch).

Wow! Make sense the category organization.

I have few questions.

  1. In user table should be a userType_ID reference field or only must be the reference in the userType?

  2. When a user table has country field or agentlisting has houseType. Country and housetype(will have other long list). they have to be in separate in other table and do a reference?

  3. What happen when a agent has some extra info than a client?


No need: the userType_ID which is a Primary Key in the userType table is related to the user_ID in the same userType table. user_ID in that table is a Foreign Key because it references the Primary Key user_ID in the user table.

The country and house fields can be a simple “select” list displayed in a dropdown menu. Really no need for a separate table for country list. Your country list will most likely be generated by an API source which you fetch online. The houseType (which I called agentListing) deserves a dedicated table indeed. Note sure I answer your question well here…

That extra info for agents can be the subject of a dedicated table indeed, which you could name agentDetails with this content: agentDetails_ID, user_ID or userType_ID, agent detail1, detail2, detail3,…

Everything is very clear @Fred_K :slight_smile:
Last thing, sorry for that many questions.

How the app is for a worldwide. How would be the best to manage that data? Example, The house is in
Spain, Catalunya, Barcelona, Eixample, Dreta Eixample.

That is a lot if data I focus worldwide. I checked for an API, but I think I will do it manually, country by country.


You need to find an API source for that because this is a lot of data: 190+ countries, each with something like 20+ provinces or regions, each with hundreds of cities. Check maybe Bing maps APIs, there must be a list of countries, broken down by regions, etc…

Make sense, thanks for all the help!