Hi! We went to create a new column in one of our tables just now and received this:
We haven’t seen this before but recently moved from hosted to BL Pro on AWS. The table has 75 columns currently and I’ve found conflicting guidance in the forum as to the limit.
Can you advise?
Thanks!
James
@Victor_Cheung
Hi James,
Maximum size of a database record in the underlying database in Backendless is 65535 bytes.
Every column you create “eats” into that limit.
A general rule of thumb is a table should not have that many columns, usually it is a sign that the table hasn’t been properly normalized. In a well-normalized database, data is split into multiple related tables to reduce redundancy and improve data integrity. Breaking the table into smaller, more focused tables can help manage the data better and reduce update anomalies.
Regards,
Mark
This table’s field structure is governed by the X12 format used for insurance billing. There may be some opportunities to normalize but not a ton.
I think we default all fields to string length 250. If we adjust this to tune the columns to what is truly needed for each field would this reduce the size of each column and let us have more?
Yes, most definitely. It is the maximum size of each column (not the actual used space) that contributes to the limit.
Additionally consider using the JSON data type to consolidate some data into a single column.
Regards,
Mark
Perfect, thanks!
