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
Client entries? Or is there some other best practice way of doing this?