Database design of managing clients for multiple businesses

Question

I have been racking my brain on this for a while. Here's my situation.

I am building a website that will provide scheduling services for businesses. These businesses provide a service to their clients. I would like for each of these businesses to manage their own clients, so they should be able to create clients at will. The businesses may have the same clients. I'm fine with having a clients table with a client_id and business_id column that relate the client to that business. This way each business can manage their own client, I'm fine with duplicates like that. Columns I'm thinking of are email, first_name, last_name, address blah blah all the basic contact info. However, email can't be required as in this industry there could be potential for clients to not have an email address. The businesses should be able to book a client for services.

I would also like the clients to be able to book services with a business through my website. If they book through the website that is the best source of truth for me so I would like to capture that data use this to build a table of customers for me. If they do book through my site they should be able to see a history of services booked. On the flip side, if a business books their client through the back end application, and 3 months down the road the client decides to book through my website on the front end, I would like to recognize that and allow both the client and the business to see past and present bookings.

What I have tried

This allows businesses to create clients at will and manage their own clients. If they provide an email address for the client I create a customer in the customer table for me. Then when a customer decides to register for the website I create the user in the users table and link it to the customer, which can be linked to multiple client records in the clients table since a business could potentially have the same client. But this way at least I have a unique customer record, and the customer can manage their own contact information which will be stored in the customer table, and the business can manage their own version of their client.

This seems to work pretty well, a problem arises when I go to bill the client. I am going to use stripe to process credit card payments on behalf of the business. Where do I store this stripe data that is related to the customer/client? Common sense says to store it in the customers table, however what if the business doesn't create an email for the client when booking a service?

I hope this make some sense to someone. Has anyone ever ran into an issue of managing clients for multiple businesses? Where those clients are also your customers of your website. It's like a single record (client/customer) belongs to three different entities (business, website and the client themselves). Me being the website, I don't need to manage the client/customers data, whatever they give me I'll take. But the business may want to manage it themselves.

Just looking for some ideas of what others may do in this situation.

Answer 1

A client should be able to give info on a business-by-business basis. That includes to you as a business. Your business (with them) just happens to be giving them access to other businesses.

(Note that you cannot know whether a client of another business is the same person as a client of yours unless they sign up with you while logged in to one of your businesses or sign up with a business while logged in as a registered user with you. Ditto for any two businesses you support.)