2

A client has recently expressed a wish to assign their market segmentation labels to their customers, so that they can sort by market segmentation in their dashboards ("show me all the VIP customers that fulfill a certain criteria", etc). Good idea. I'd like to offer this to all my clients.

The only trouble is that "their" customers aren't exclusive to them. They're also customers of their competitors, who happen to also be clients (it's SaaS, so everyone is using the same DB). Alice and Bob might both be "VIP" customers at BusinessOne, but Alice is a "Seg1" and Bob is a "Seg2" at BusinessTwo (where the guy who did the PCA there has less imagination).

Should I be creating a Segmentation table with foreign keys pointing to the relevant Customer and Client entries? Or is there some other best practice way of doing this?

Improve this question

2 Answers 2

Reset to default
2

I understand that you have a database single database which you use for all your clients:

It's SaaS, so everyone is using the same DB

I understand also that the customer data is shared in the database:

The only trouble is that "their" customers aren't exclusive to them. They're also customers of their competitors, who happen to also be clients

This seems to be weird to me, because:

  • in a very competitive market, finding the customers is a very sensitive topic. So it would be very strange that one client could see customers that do not belong to him, simply because someone has entered them in the database.
  • A part from very basic address data and copany registration, very quickly the customer data is in general specific to one client (i.e. purchaser in charge, customer account number, etc...).

So in my view, you should already in your model have an N to N relationship between customer and client (i.e.every customer can be used in several clients, and every clients manage several customers).

enter image description here

As you know, an N to N relationship is implemented with an association table (primary key: client id+customer id). Then you would put the segmentation data in this table. This would allow a customer to be VIP for one client and category Z for the other.

enter image description here

If each client can define his own segments, you could list these in a distinct configuration table (primary key: Client + CategoryId).

Improve this answer
3
  • No client can see raw customer data, and can only make an aggregate query on clients for which they can supply the unique key (proving they have this client in their own client database). Consequently, the relation between client and customer is not stored (the previous business logic discourages this), but your N to N association + segmentation data makes me wonder if I should do it...
    – Escher
    Apr 23, 2016 at 21:52
  • What software did you use to create those diagrams?
    – user664833
    Apr 24, 2016 at 20:19
  • @user664833 I used Papyrus on Eclipse, to create quickly a class diagram. But for DB design there are more convenient tools. Some can generate you the SQL schema out of the model (e.g. automatically add the client and customer id into ClientCustomer)
    – Christophe
    Apr 24, 2016 at 20:29
1

customer 1-m segmentation m-1 client

This design allows the customer to be associated with any number of clients and vise versa.

However, it means that each client has a different set of segmentation records. That can seem wasteful of space but not critically so.

The most significant thing about it is that each client's set of segmentations will vary independently. That means work done to add segmentations to one clients customers will not automatically be done for other clients.

Duplicating segmentations for clients is a very simple update to the database. It just doesn't happen right away or for free. Adding segmentations for everyone is simple. Deleting them for one client and not having them re-added automatically is a pain. Done this way they really want to be independant. Making them consistent over time will not be trivial.

An alternative:

customer 1-m segmentation
customer 1-m existing_relationship m-1 client

This way the customer segmentation has nothing to do with your clients. No client customisation options here, other than the work they motivate you to do for every client.

The existing_relationship table is only needed if clients need something other than all of your customers.

Improve this answer
4
  • What is "delete a segmentation label for one client and not re-add automatically"? I don't understand what you mean by that sentence - can you give an example? Edit - do you think that I create the segmentation labels? FYI these are created by the CRM team for each client, and pertain to their specific business only.
    – Escher
    Apr 23, 2016 at 21:28
  • If customer A doesn't want a segmentation label but other customers do it's simple to copy them all and then delete the label customer A doesn't want. However, when new labels are created and customer A wants to be updated with their copy of them they'll also get everything you deleted. You can work around that but it's a pain.
    – candied_orange
    Apr 23, 2016 at 21:32
  • I still don't get it - every client has different segmentation business logic that they decide themselves (this is the way market segmentation works; there's no universal segmentation label, hence Bob being VIP and Seg1). Why would I seek to duplicate segmentation labels?
    – Escher
    Apr 23, 2016 at 21:40
  • If you don't ever copy segmentations from client to client then it's perfectly fine to stick with the first design. It means they vary independently. If it's the clients who are doing the work to create the segmentations (not you) this is the obvious thing to do.
    – candied_orange
    Apr 23, 2016 at 21:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.