Database design structure for CRM

Wappler General How To
#1

Hey!

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
    *lastName
    *Birthday
    *typeUser(Agent or Cliente. Reference field)
    *extra dara…

Table Agent:
*ID
*user_ID(Reference field)

Tabler Client
*ID
*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?

Cheers

#2

Hi,
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.

#3

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.

#4

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.

Cheers

#5

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.

Cheers

#6

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).

#7

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?

Cheers

#8

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,…

#9

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.

Thanks!

#10

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…

#11

Make sense, thanks for all the help!