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 :
- Performance hit
- Varchar(n) for everything
- No foreign key constraints etc.
Is there a 3rd solution?