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.

New contributor
Patrick is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • 2
    Given its scale, I think the design will depend heavily on how the information is being retrieved. – bitsoflogic Oct 11 at 12:46
  • 1
    Can you give us some examples of these "custom" fields? Also, what industry is your target market? By your use of the term "leads" are you talking about "sales leads?" – Greg Burghardt Oct 11 at 12:56
  • 3
    It sounds like users came to you with a solution, rather than a problem. The mere fact you said "gender" and "age" are custom fields leads me to believe you need to do some more research so you can properly identify abstractions that can be encapsulated as tables. I would request a list of custom attributes as examples to follow before deciding "custom fields" is even the right solution. Identify the abstractions first, then capture the outliers in an Entity-Attribute-Value table. – Greg Burghardt Oct 11 at 14:02
  • 4
    @Patrick: I'm sorry if I insulted you. I'm just asking questions to make sure I know the full gravity of the problem. – Greg Burghardt Oct 11 at 14:33
  • 5
    @Patrick Don't be upset that people question your premises. We get many questions asking us how to build what amounts to an Inner Platform, so it's become customary to question any mention of implementing custom fields. – Kilian Foth Oct 11 at 14:51
up vote 5 down vote accepted

If your customers want to be completely free to define their own custom fields, your approach in general is fine. However, for the given numbers, you need to take some precautions to keep the system performant and scalable.

  • I guess when there are 300.000 leads with 20 fields, there is a high chance of having lots of those content entries empty for the non-required fields. In the suggested design, you can simply leave out those records from the leads_fields_content table, no need to create a record with an empty content for those. So if there is only a small percentage of the fields filled with content, you get only that small percentage of records in the leads_fields_content, not necessarily 6 millions. Your design already allows to store sparse tables efficiently!

  • Proper indexing should be obligatory. However, this is way easier for fields which are known at design time. So if you know some standard fields which are typically required for any customer and don't really need to be customizable (like a lead's name), it would probably be a good idea to make it a fixed part of the lead table instead. Then you can create a specific index for those fields.

  • If each of your customers has his own "custom schema", it is clear they want their data 100% separated from other customers. For example, you will never get any requirements for searching over the content of different customers. So it will probably be best to separate the data of each customer physically. How you accomplish this depends on the DBMS you are using: separate tables per customer, tables in different schemas, different table spaces, same table in different "logical" databases on one server, or completely different database instances per customer, maybe on different machines - it all depends what kind of DB system you are using, how many customers you have and how scalable you need the system to be.

There really isn't a way to have custom fields in a relational database while still being relational. A NoSQL database may be a better option to fit your needs. If it has to be stored in a relational database I would store all custom fields in a JSON/XML column, and only have columns for fields that are useful for searching or common enough to be on most/all records (eg email, phone, name). This way you keep the growth rate of your database somewhat reasonable as you don't have to store what is likely millions of nulls for each custom field on each record. Ideally you could have a "basic search" that doesn't look in the custom field column, and an "advanced search" that is much slower that looks at custom fields. It would also be helpful to have a table that maintains the name and types of custom fields that a record may have to at least attempt to cut down on the number of fields that are basically the same information expressed slightly differently.

You will also want to have separate tables or databases for each client. While this helps limit the size of tables as well, there are other more important reasons to do this. Client 1 isn't going to be happy that they can't update their leads because client 2 is inserting 300k leads. Client 2 is also going to be very upset if client 1 updates, reads, or deletes any of their leads because of a bug or client 1 being compromised. If client 1 grows their database significantly you can more easily move a database to a separate server to maintain performance for all clients, than scaling a database across multiple servers.

  • Since you can go as far as to offer complete access to a database, it's possible to allow custom fields that are relational. – bitsoflogic Oct 11 at 16:35
  • @bitsoflogic that's not really true, sure you could add a column or another table with a foreign key, but that is just impersonating a relational structure. New columns aren't going to be meaningful to every row, and new tables aren't meaningful without the context of the main table. – Ryathal Oct 11 at 18:07
  • It's not uncommon in relational databases for NULLs to be used in cases where columns don't have data for every row. It could still logically be meaningful. But what prevents the new tables from being associated with the "main table"? – bitsoflogic Oct 11 at 18:18
  • The design suggested by the OP does not require to store "millions of NULLs", quite the opposite. – Doc Brown Oct 11 at 19:07

Your Answer

Patrick is a new contributor. Be nice, and check out our Code of Conduct.
 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.