1

Suppose that you are developing an application that could be used by multiple clients.

You have to store "basic details" of an employee of a company (your client) into your database.

The first 15 fields are going to be common among all the employees across all the clients.

But there is a possibility that Microsoft (your client) has employee basic details to have 15 + 3 fields.

Your client Apple could have basic details with 15+5 fields. The additional fields may or may not be common among the clients.

Would you use EAV for this scenario?

Or would you create maximum number of columns to suffice the needs of both your clients. In which case the columns that are not used by individual client will remain null. Of course this calls for the need to have alter and add more columns if you sell your application to third customer.

The drawbacks that I see are :

  1. Complexity
  2. Performance hit
  3. Varchar(n) for everything
  4. No foreign key constraints etc.

Is there a 3rd solution?

MDCCL
  • 8,520
  • 3
  • 30
  • 61
SamuraiJack
  • 111
  • 1
  • 3
    See here for what a disaster EAV can be. – Vérace Sep 25 '19 at 17:54
  • 1
    @Vérace I know it is not the ideal choice. But given the scenario, how would you deal with it without EAV? If you have to keep adding columns for each client. And if you at some point have say 5-8 clients. You might end up with 20-30 column in a table that are empty most of the time. It's harder to maintain too right? – SamuraiJack Sep 25 '19 at 18:01
  • I've run into a similar situation once, and used the "one table per client" approach. It's hardly ideal, as now you have to manage the applications/processes which create and maintain those tables. It is still complex, but it does not have the foreign key/constraint problem as you have your 15 consistent fields, or the data typing issues as the EAV model. In your case, if you only have a very few clients (with only a handful of extra columns) perhaps you might consider Sparse columns? – Jacob H Sep 25 '19 at 18:41
  • 2
    There are no infinite attributes for an employee. Talk to your potential customers beforehand and find out exactly what attributes they keep, It is part of the data universe discovery effort, and should be done long before any schema decisions are made. See what other products on the market offer. Use the super-set of attributes discovered, and if down the line someone needs an additional attribute, add it to the next release. Trying to achieve ultimate flexibility is going to cost you orders of magnitude more than whatever benefit it may have. – SQLRaptor Sep 25 '19 at 19:47
  • @JacobH I was contemplating using sparse column but I think we can not use table types to send datatable to database in one go if you are using sparse columns – SamuraiJack Sep 26 '19 at 13:57
  • @SQLRaptor You are right – SamuraiJack Sep 26 '19 at 13:58
  • As for "Would you use EAV for this scenario?", no, I would perform an in-depth analysis of the informational requirements of the whole business domain (as suggested in a comment above) and, maybe, would build something similar to the design detailed in this answer. – MDCCL Sep 26 '19 at 16:49
  • How do clients go about figuring out that additional attributes are needed and, more importantly, how do they figure out which screen(s) need these attributes, what validations to apply, etc? If you're building a large HR system that has dozens of screens where you expect each customer to spend several months during implementation writing code to make customizations, that's different than if you've got a simple bug tracker where you're expecting an admin to add a couple one-off fields during a 30 minute configuration step at installation. – Justin Cave Sep 26 '19 at 17:49

0 Answers0