MySQL Schema Advice: Unpredictable Field Additions

Question

A little overview of the problem.

Let's say I have a table named TableA with fixed properties, PropertyA, PropertyB, PropertyC. This has been enough for your own website needs but then you suddenly have clients that want custom fields on your site.

ClientA wants to add PropertyD and PropertyE. ClientB wants to add PropertyF and PropertyG.

The catch is these clients don't want each others fields. Now imagine if you get more clients, the solution of just adding nullable fields in TableA will be cumbersome and you will end up with a mess of a table. Or at least I assume that's the case feel free to correct me. Is it better if I just do that?

Now I thought of two solutions. I'm asking if there's a better way to do it since I'm not that confident with the trade offs and their future performance.

Proposed Solution #1

data_id is a not exactly a foreign key but it stores whatever corresponding client property is attached to a table A row. Using client_id as the only foreign key present on both the property table and table A.

It feels like it's an anti pattern of some sorts but I could imagine queries will be easy this way but it requires that the developer knows what property table it should pick from. I'm not sure if many tables is a bad thing.

Proposed Solution #2

I believe it's a bit more elegant and can easily add more fields as necessary. Not to mention these are the only tables I would need for everything else. Just to visualize. I will add the request properties in the properties table like so:

Properties
-------------
1 | PropertyD
2 | PropertyE
3 | PropertyF
4 | PropertyG

And whenever I save any data I would tag all properties whenever they are available like so. For this example I want to save a ClientA stored in the Clients table on id 1.

Property_Mapping
--------------------------------------------------------
property_id | table_a_id | property_value   | client_id
--------------------------------------------------------
1           | 1          | PROPERTY_D_VALUE | 1
2           | 1          | PROPERTY_E_VALUE | 1

There are obvious possible complexity of query on this one, I'd imagine but it's more a tradeoff. I intended client_id to be placed on property_mapping just in case clients want the same fields. Any advice?

Answer 1

You've discovered the Entity-Attribute-Value antipattern. It's a terrible idea for a relational database. It makes your queries far more complex, and it takes 4-10x the storage space.

I covered some pros and cons of several alternatives in an old answer on Stack Overflow:

• How to design a product table for many kinds of product where each product has many parameters

And in a presentation:

• Extensible Data Modeling with MySQL

As an example of the trouble EAV causes, consider how you would respond if one of your clients says that PropertyD must be mandatory (i.e. the equivalent of NOT NULL) and PropertyE must be UNIQUE. Meanwhile, the other client says that PropertyG should be restricted to a finite set of values, so you should either use an ENUM data type, or use a foreign key to a table of allowed values.

But you can't implement any of these constraints using your Properties table, because all the values of all the properties are stored in the same column.

You lose features of relational databases when you use this antipattern, such as data types and constraints.