How to design a relational database where users can create custom fields

by Patrick   Last Updated October 11, 2018 13:05 PM

Ive been looking around for a way to design a database for large amounts of leads (users) with custom fields.

Ive check this out (How would you design a user database with custom fields), but this solution would limit the amount of custom fields.

So far ive been designing it in three tables:

leads                    (ID, phone, email)
leads_fields             (ID, name, type, required)
leads_fields_content     (ID, fields_id, lead_id, content)

Users can create all the leads_fields they need, fx. 20 fields.

So when I have a lead, I would go trough and check in leads_fields_content for lead_id, get that collection and get the corresponding leads_fields that is referenced.

I see this working - However, one client wants to upload 300.000 leads from day one. So thats 300.000 rows in leads. Then lets say that there are 20 fields for each lead. That would then create 300.000*20 rows in leads_fields_content which is 6.000.000 rows. Thats only for one client.

My question: is this at all the correct way of designing this, taking into account the amount of time it would take to go trough 300.000 rows, and then 6.000.000 afterwards? And this would only get exponentially bigger.

Related Questions

Is-a of an aggregation - entity relationship model

Updated October 09, 2018 13:05 PM