DB Schema ideas for allowing users to record custom data fields?
Hey all! I'm wondering if anyone has experience in allowing my users to record custom data for some entities in the system?
For instance, when my users enter a product record, there may be fields that don't exist that they want to use like a date field for "Special Deal Begins" or a boolean field or "discontinued line of products" etc. . .
I've thought of putting lots of generic fields in the database and allowing the client to rename the label on the screen, and track the data that way. Also, I've thought of having a "ProductCustomData" table. I'm just so undecided on what's the best way to go.
Also, I want to allow them to filter on some of these fields for reporting purposes if desired.
Thanks for your input!
If you use a database with JSON support (like Postgres), you can store these extra fields in a single JSON object and query it when necessary.
If you can't use JSON, then the second option (another table with all custom tags) looks way cleaner and easier to query, but you'll end up with more rows, and I'm not sure about the performance.