Linked Questions

Popular Questions

MySQL Schema Advice: Unpredictable Field Additions

Asked by At

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 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 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:

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_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?

Related Questions