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?

Improve this question
8
  • 3
    See here for what a disaster EAV can be.
    – Vérace
    Sep 25, 2019 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, 2019 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, 2019 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, 2019 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, 2019 at 13:57

0

Reset to default

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.