Help needed for database schema of a CRM
Hi fellow indie hackers!
I am working on my SaaS CRM & Invoicing app and I have reached a poin where I need the help of this community.
The app I am building has 2 components which I don't know how to connect properly. The first is the order management component, and the second is the CRM component of the app.
How does it work
You connect your online store (be it Shopify, WooCoommerce etc) to my app. And all the orders you receive through your webshop, are sent to my app and you see them in a dashboard. From there, you just follow your business procedures: generate invoice, generate a shipping and so on. When an order is sent from your webshop to the app, the customer is saved in the database of my app (in the CRM component, to call it like this).
A webshop can have two types of customers: Companies and Individuals. And the issue is that an Individual customer can also place an order for him but the same customer can also place an order for the company (he is working for or he owns).
For example, I am a freelancer. I am buying some clothes/sunglasses etc for personal use from Amazon. I don't need an invoice on the name of my company, because these products are for personal use. But if I buy a laptop for work from Amazon, I will need an invoice on my company's name (with company data, VAT number and so on).
Also the store owner can manually add customers in the CRM, both individual customers and companies, in case he receives an order by email or phone. He creates the customer, creates the order and so on...
The way I thought about setting the database schema is going with the Supertype-Subtype approach. I have a customers tabel (supertype) with the columns: id, type (person or business), created_at. And two other tabels businesses and persons (subtypes) with columns: customer_id, email and phone. The businesses tabel also has company identification columns, like company_name, vat_number (unique for each business), street, city, country which are used for billing. The business doesn't change the billing details. While the person tabel has first_name, last_name, date_of_birth.
So, when a customer places an order and wants the invoice on the name of the company, I register him both as a company and as a person. And then I associate the person with the company, in the pivot tabel business_person. So the Company and the Person will be in a relation of many-to-many. And the orders tabel will contain a column customer_id and a tabel business_id (business_id is nullable).
This way a customer can place online orders on behalf of his company, but also for his personal use/interess. If he places an order on behalf of his company, the order is invoiced with the correct billing address.
Having an extra column business_id in the order's tabel, I have the possibility to differentiate between the orders placed by an user in the name of a company and those placed on his name.
How would you approach this situation? What are your thoughts? Is there any way I can improve the schema?
It seems like the key element here is to have the ability to link your order to the proper "entity" when generating the invoice. That means that you need a way to point to different types of entities in your order table.
I think your approach would work, though to me the supertype-subtype approach seems like it might make the code more complex down the line. Because you would have to fetch the supertype to then figure out which subtype to fetch the real data from. There isn't going to be a simple query you can build to do it in one shot and will have to create multiple queries.
Personally I would probably just stick to something that is representative of real-life:
CREATE TABLE Customers (id INT PRIMARY KEY AUTOINCREMENT, name VARCHAR(255))CREATE TABLE Companies (id INT PRIMARY KEY AUTOINCREMENT, name VARCHAR(255))CREATE TABLE Employees(id INT PRIMARY KEY AUTOINCREMENT, CustomerId INT NOT NULL, CompanyId INT NOT NULL, FOREIGN KEY(CustomerId) REFERENCES Customers(id), FOREIGN KEY(CompanyId) REFERENCES Companies(id))CREATE TABLE Orders (id INT PRIMARY KEY AUTOINCREMENT, CustomerId INT NOT NULL, CompanyId INT, FOREIGN KEY(CustomerId) REFERENCES Customers(id), FOREIGN KEY(CompanyId) REFERENCES Companies(id))CompanyId would be NULLABLE in this case and only set when the order is pertinent to the business.
This design would allow you to easily find all orders for a particular company with a simple where clause, and the same for a person.
It also allows for a person to be ordering for multiple businesses.
Hi @poitch,
Thanks for your suggestion! After writing my question over here and simulated few use cases, I arrived at the same conclusion. To include both customer_id and business_id in the Orders table, with business_id being optional. Because the orders are always placed by a person, even if they will be invoiced to a business.
I still need to think if there will be any real need to use the Supertype / Subtype approach, or I can ditch it. Because, if I use the Supertype Customers and Subtypes Persons and Businesses, I can have the table Addresses in a relationship of one to many with the Customers table (one Customer has many Addresses).
With Customers and Businesses tables (no Supertype/Subtype), I need to either have two tables, Address_Customer and Address_Business, or a single table Addresses, with an additional column, address_type with possible values customer and business, to identify. Or, probably, to set customer_id and business_id columns, similar to the Orders table, where both are optional and I can fill just the required value. If it's a business address, I set the business_id with the appropiate value.
I'm still learning relational databases so don't place much stock in this answer but hey, might help rule out what not to do (hope I've at least understood the problem correctly!)
My first thoughts in keeping with your supertype, subtype structure...
Order(OrderID, CustomerID, date_ordered, .... )
OrderBusinessContact (OrderID, CustomerID, ContactID)
So the UI and business logic will handle the right billing address if they have specified as a business order. Rather than having a field in orders for businessID that might often be null, would it not be better to have a table that captures the contact person relating to each business order? So in the above, ContactID is a foreign key reference to the CustomerID of the person who ordered on behalf of the company? Ensures you can capture the contact person for each order where a company might have multiple people ordering for them
Hi @LukeB
Thanks for your answer! The OrderBusinessContact table would be a little overkill, because is an extra pivot table which stores only the OrderId, CustomerId and ContactId. The pivot tables are useful in many-to-many relation. But in my case, is a one-to-many relation (one customer can have many orders). Although this might look like an order can belong to both a customer and a business, in real life an order is placed by a person, not a company. The company is used for billing and shipping. And I am placing an optional business_id column inside the Order table so I can query all orders belonging (related) to a certain business. If it was a B2B app, where I would sell only to businesses, I could query the orders related to a certain business through the person who placed that order, using a left join query. Because a person would belong only to one business. But in my case a person can belong (or not) to a business.
Thanks for the detailed reply, I realise it helps me more as a student than you at this point! I do understand the cardinality, I guess I was just trying to solve for the fact that you had established a business is a type of customer. I see now the table I was suggesting is fairly well redundant considering the type of querying you'll need to do. Sounds like you've got it nailed down now, best of luck with the rest of development!