The fifth custom field is a table

A custom field is the cheapest change a CRM administrator can make, and that is its problem. Someone needs to track a vehicle registration on a contact, so they add a field. Then a make, a model, a colour, an insurance expiry. Five fields later the contact record is quietly carrying a second entity, with its own lifecycle, no identity, and no way to have two of them. The CRM I built at CustomGlide shares one contact and account model across pipelines, campaigns and ticketing, so every field decision is felt three times, and the rule this post describes is the one I use to decide when a field is a field and when it is a table in disguise.

The ceiling is the trouble

Vendors sell the field limit as a feature, and the limits are generous enough that almost nobody hits them. Zoho CRM's custom field limits run from 10 per module on Standard to 155 on Professional and 300 on Enterprise. HubSpot allows 1,000 custom properties per object on paid tiers, with a much smaller allowance on the free one. The generosity is the point of the sales pitch and the source of the rot: a schema does not fail when it fills, it fails long before, when the fields stop meaning what their names say and nobody can tell which of the three hundred are still used.

The co-fill test

The rule is a measurement, and it runs quarterly on the CRM as a field census. For every pair of custom fields on a record type, compute how often they are populated together and empty together across all records. Two fields that are always filled together and always blank together are not two attributes of the record. They are two attributes of something else that the record sometimes has. When a group of such fields grows to five, that is the point to stop, name the something else, and give it a table.

The count of five is a threshold, not a law, and its logic is that below it the cost of a separate table exceeds the cost of a few nullable columns, and above it the reverse. Two co-filled fields are a pair of nullable columns and nobody minds. Five is a registration, a make, a model, a colour and an expiry, and by then someone has asked for a second vehicle, which the flat design cannot represent, and someone else has asked which contacts have a vehicle, which the flat design answers only by checking whether one of the five happens to be non-null.

The census is a single query per pair, or one pass over the records that counts, for each pair, the four cases. The matrix that comes out has dense blocks wherever a hidden entity lives, and the blocks are visible at a glance, which is why I keep it as a picture rather than a threshold on a number.

Running the census

The mechanics are small. For a record type with n custom fields there are n times n minus one, over two, pairs, which for fifty fields is 1,225 and for three hundred is under forty-five thousand, both trivial for one pass over the table. For each record and each pair, note whether both fields are populated, neither is, or exactly one is. The co-fill score for a pair is the share of records in the first two cases, both or neither, out of all records where at least one of the two fields is populated anywhere in the data, which stops a pair of fields that are simply both rare from scoring high because they are both blank on everyone.

Two refinements matter in practice. The first is to exclude fields that are populated on nearly every record, because a field like owner co-fills with everything and tells you nothing. The second is to treat the matrix as a graph and look for cliques rather than pairs: five fields that each co-fill with the other four are the signal, and a field that co-fills with one other but not the rest is just a correlated attribute. The output is a short list of candidate groups with their member fields, ordered by size and score, and it is that list, not the matrix, that goes to the people who own the model.

Two objections

The first objection is that the CRM already has custom objects, so why not use those. The answer is that a custom object with custom fields is the same postponement one level up: it has an identity and a lifecycle, which is genuinely better, and it has the same ceiling and the same rot inside it, because nothing stops the vehicle object from growing its own block of fields that are really a policy. The census runs on custom objects too, and the rule is the same.

The second is that a JSON column would let each record carry whatever it needs without any of this. It would, and a JSON column is the flat design with the column names removed: no types, no constraints, no way to say that a contact has two vehicles except by putting an array inside, and no way to query expiries next month without a path expression that every report has to know. It is the right tool for data that is genuinely different on every record and the wrong one for five fields that are the same on every record that has them.

What the table gives back

Promoting the block to a table is the change people postpone because it sounds like a migration, and it is smaller than it sounds. The five fields become columns on a new table with its own id and a foreign key to the contact. A view that joins the two reproduces the flat record for anything that depended on it. The existing values are copied across in one statement. What the table gives back is everything the flat design could not do: a contact with two vehicles, a vehicle that moves between contacts, a query for expiries next month that does not have to know which of five columns to check, and a lifecycle, so that a vehicle can be added, sold and removed without the contact's history changing.

Why vendors cannot do this for you

A general-purpose CRM cannot promote fields to tables on your behalf, because it does not know what the entity is, and its custom-object features, which exist, are the same postponement one level up: an object whose attributes are custom fields, with the same ceiling and the same rot. What it can do is offer enough fields that the decision never has to be made, and that is the feature. The result is the schema I meet on most migrations: hundreds of fields, of which a third are blank on every record, a third are two or three entities interleaved, and a third are the contact.

In the CRM I built, the census runs on a schedule and its output goes to the people who own the data model, with the dense blocks named. The decision to promote is still a human one, because the human knows that the block is a vehicle or a policy or a site visit. What the census removes is the discovery cost, which is the part that used to take a consultant a week and a spreadsheet.

The rule

Count fields by whether they move together, not by how many there are. Two co-filled fields are columns. Five are a table wearing columns' clothes, and the fifth one is the moment to name it. The ceiling the vendor gave you is not a budget. It is the amount of postponement they are willing to sell.