Database Design for Custom Fields 

In my project, I added a CustomFieldDefinitions table and a CustomFields table, for the Orders table. My idea was to get a clean structure. It was proven to be bad when I tried to implement filtering of Orders and thus wanted CustomFields to be included. I used Entity Framework and the idea was compile a structured query string arg into a query. But you cannot flatten all custom fields per Order to columns that the ORM can handle. My next attempt will be with a JSON field.

JSON support in most major DBMSes has gone a long way, but if your data is highly dynamic to begin with, you are probably bringing a knife to a gun fight and should consider a NoSQL database for your application

I prefer 3NF Normalized Tables (6th in the video) and the JSON (7th) for my large scale app’s database design. Thanks for the video. :)

For #3 the video missed the most important con, which is in multi-tenancy, each customer, with a click in the product, would change the basic database schema. That shuts down the solution right there. (if your situation is not multi-tenancy, then you probably don't even need to worry about custom fields) edit: and it's thousands or tens of thousands of columns, not 50-200

Thanks for this video, I really like your clear voice and concise explanations.

I would prefer #1 EAV, and #7 JSON for alternative, thanks for sharing.

I think the Modified EAV could be modified even more to be a bit more useful. Instead of a single customer_attributes table, why not three tables for the different data types (customer_string_attribute, customer_date_attribute, customer_number_attribute). This allows for better data validation compared to EAV and removes the risk of multiple columns being populated. It does add more complexity when trying to query all attributes.

Working with norlamized (#6) all time. Json sometimes too (#7) but now I have to switch my articles to EAV.

Magento cms indexing mechanism solves entity attribute value performance...that can also be applied to worpdress to solve performance issue...I think nosql can be used with sql to indexing the large public data, for eg: ecommerce product data & confidential records like order, stocks can be stored in sql for better secured storing...

Json is the best solution for a dynamic fields even big companies use it on their api

I've had much success with EAV using: * (1) MSSQL and the Variant type so that you only have 1 value table(or column). * (2) use strongly typed and optimized hand written SQL stored procedures(middle-tiered ORM generated SQL is not always a good idea for reporting queries). * (3) Create a dynamic-PIVOT operation stored procedure to generate a pivoted 'flat' table for reporting. Legally inject parameters(list of required attributes) from the reporting user interrace into the dynamic(sql) PIVOT stored procedure to generate the pivoted flat table. I used COUNT and /or MAX as the PIVOT aggregate function. * (4) Use optimized indices(indexes). You actually can index the tables easily. Also you don't really need to cast or convert values since the very nature of the query would limit the types (attributes) required and also that you are using the Variant data type that is directly sent straight up to the user interface reporting visualization elements. These alone solved my issues. I seeded the model(the value table) with millions of records and the performance is amazingly fast! As a bonus, you can further 'cube' the generated pivoted flat table to generate further summaries and statistics before presentation.

I didn't think of the JSON (or potentially XML??? - is that a thing too?) version until you said it....

@DatabaseStar This is a bit difficult to understand without any table examples of how it looks like. Is there any blogpost where you have explained it with an example. thanks in advance :)

Only a few of these options support custom fields and none of these support defining a custom field(column) (which would be different for each tenant) to which each row would have a value.

Great video

Very helpful for me

Nice video! Anybody know what "PK" and "RK" refers to in these diagrams? This is the only thing I'm missing!

Dynamic Design looks 😬 due to risk of columns change on live server. need strong validation for this type. I prefer JSON, EAV structure.

Or just use no-sql DB

While I enjoyed the video I am none the wiser as to which is best or better maybe a list of don't do to best option might be good

Normalisation

👍👍

i think 1st solution was much better

I think you oversimplified the EAV by A LOT.