What would be the best db design for user generated (custom) fields

byrnejamie

Hi,

I'm creating an app for product management, where users can create products, these products have attributes that are created and defined on the front end by users.

I'm trying to work out the design of a database where users can create custom fields on the front end.

My confusion is how best to design for different field types, the custom field could be any of the following types text, integer, decimal, date, url, media.

Does anyone have any suggestions on the best way to design of the attributes table to support the different user-defined fields?

Thanks in advance.

Ray Deck

I'd consider making a JSON field on the table that can store the kind of heterogenous data you want. This would be easier to work with, but less searchable. The variety of the data probably means it's not too searchable anyway. This is the "document model" approach.

The more "relationship" approach would be to make another table that is N:1 with the products, so that three custom attributes would make three rows in this "attributes" table that each have a table reference to the original products table. The trick is that the information itself is in multiple shapes. For integrity, your data field could be JSON to handle the many possible formats. But if the reason for the relationship is query-ability, you might want to might store it in a standard string format.

So multiple considerations, and multiple right answers. Sounds like a neat, complicated project!

We work on the hardest parts of no-code(and esp Xano) projects during our daily State Change Pro office hours.