Database Design for Custom Fields

Рет қаралды 28,579

Күн бұрын

Пікірлер: 63

@DatabaseStar 10 күн бұрын

Want to improve your database design skills? Get my Database Design project Guides here (diagrams, explanations, and SQL scripts): www.databasestar.com/dbdesign/?

@marna_li 3 жыл бұрын

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.

@DatabaseStar 3 жыл бұрын

Thanks for sharing. Sometimes designs can work at one point but later on they can be hard to work with.

@rikyriky966 3 жыл бұрын

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. :)

@DatabaseStar 3 жыл бұрын

Awesome, good to hear!

@bugloper Ай бұрын

Data integrity can be maintained by creating a value table for each datatype, storing attribute and value in different tables. But this makes query really complex but can be solved by introducing a flat table, some sort of read model, for single select query

@DatabaseStar Ай бұрын

Yeah I can see how that can work, thanks for sharing.

@jellehuibregtse9476 2 жыл бұрын

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

@DatabaseStar 2 жыл бұрын

No problem! Glad it was helpful.

@jhfdjdhfdgfghjzrztku 2 жыл бұрын

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

@DatabaseStar 2 жыл бұрын

Good point. Using JSON data for custom fields, in a NoSQL database, could be the way to go.

@mr.random8447 Жыл бұрын

I need ACID, but also need custom fields. What do I do? Have an id column in SQL that points to a document in MongoDB. Two databases?

@mmaaaxxxxx 3 жыл бұрын

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

@DatabaseStar 3 жыл бұрын

That's a good point, if there are many customers then the number of custom fields would increase a lot. This is something to consider.

@havenlin5633 3 жыл бұрын

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

@mr.random8447 Жыл бұрын

EAV bad, you chose the worse options. Best is Normalized

@DatabaseStar Жыл бұрын

Thanks for sharing!

@DatabaseStar Жыл бұрын

Yeah generally normalised is better

@FLAWLESSBEATS 11 ай бұрын

@@DatabaseStar What if you need to create custom attributes for a certain class in your application? Normalized option obligates you modify your app to create a new class and a new table on the database in order to have a new type of attribute, so in that case using sort of an EAV solution is better right?

@aubbusta 2 жыл бұрын

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

@DatabaseStar 2 жыл бұрын

That’s a good point!

@be_real_truth_matters Жыл бұрын

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...

@DatabaseStar Жыл бұрын

That's a good point, good to see it can help for those systems.

@taamcyat Жыл бұрын

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.

@DatabaseStar Жыл бұрын

Great tip! That approach sounds good and it's good to hear it works for you.

@CallMe_PsPsPs 3 жыл бұрын

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

@DatabaseStar Жыл бұрын

Thanks for sharing, good to know!

@alankalbfleisch3141 8 ай бұрын

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.

@DatabaseStar 8 ай бұрын

Good point! I think that can work as well.

@saran-ravi 2 жыл бұрын

Great video

@DatabaseStar 2 жыл бұрын

Thanks!

@kushagrajain2407 2 жыл бұрын

@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 :)

@DatabaseStar 2 жыл бұрын

Thanks for the feedback! Yeah I've heard that before about this video and some others, so I try to add examples into some of my recent videos. I don't have any posts that has examples unfortunately - but I can create one in the future.

@martinjones8237 2 жыл бұрын

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

@vivekbansal7207 2 жыл бұрын

primary key

@DatabaseStar 2 жыл бұрын

Ah, PK = Primary Key (the unique identifier for the row), and FK = Foreign Key (a reference to a Primary Key in another table)

@ADAMSIVES 3 жыл бұрын

Very helpful for me

@DatabaseStar Жыл бұрын

That’s great!

@ADAMSIVES 3 жыл бұрын

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

@DatabaseStar Жыл бұрын

Good to know! Yeah XML could work but I don't see it used very often.

@ZachariBarnes Жыл бұрын

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.

@DatabaseStar Жыл бұрын

What do you mean by "only a few support custom fields"? And what do you mean by "none of these support defining a custom field"? Each of them allow users to define custom fields, where the users can determine what information can be captured for a record. If there's a need to have a new column where each row has a value, then this would be more like adding a new column to a table, and would be done with an Alter Table statement.