In this video, you'll learn about designing your database to handle custom fields
A common requirement for applications is to be able to allow custom fields, or user defined
fields, to be captured by the application.
The database the application uses would need to handle this.
One common example of this type of application is a CRM or anything that stores contacts.
You've got some core fields about a person, such as first name and last name.
But some contacts have a work address, others have a mobile number, some have a company.
Some applications also allow you to add your own custom fields as well
For example, a new address type, or phone number type, or a particular type of date
that applies to that record only This seems handy to have in the application
But how do we design our database to handle this?
This is a common problem as it's not an easy one to solve.
You'll find many posts on Stack Overflow and Reddit about this.
In this video, I'll explain eight different solutions that can be applied.
Now, eight solutions might seem like a lot.
But they are just options for you.
You can look at these options and determine which one will work the best for your application
and database.
There is no one perfect solution for all situations, but there should be one here that will work
for you.
I'm also not going to say that this solution will work all of the time, or never to use
this solution.
I'll make some suggestions and recommendations, but it's your decision as you know the database
and application the best.
For each solution, we'll look at an example using a customer database, but it can also
be applied to a product database or any other design that may need custom fields.
I'll list the pros and cons for each solution as well.
Let's get into it.
The first solution is called Entity Attribute Value, or EAV.
It's called this because there is a single extra table that stores three columns: the
entity name, the attribute name, and the attribute's value.
The entity refers to a record in another table.
It could be a customer_id, which would be the field in the customer table it relates
to.
The attribute would be the title of the field you add.
So, if the user adds a field called "Business Phone" to a particular customer, then this
value would go in this field.
The value column is the value of the specified attribute for that entity.
If the user adds the "Business Phone" field for a customer, then this is where the phone
number would go.
Is the EAV a good design?
The short answer is no, it's not.
It should be avoided in almost all situations.
Why is that?
Why would you use the Entity Attribute Value design?
And why should it be avoided?
One advantage is that they take up less space for "sparse" columns, which are those attributes
that only apply to a small number of rows.
If you have 1,000 customers and only a few of them have this Business Phone number field,
then you don't need a new column just for a couple of values.
Another advantage is the flexibility.
Users have the ability to add whatever column and data they want, without having to change
the database schema to do that.
They can just insert a row.
It's easy for the developers to implement.
A final advantage is a simpler design.
Well, it may seem simpler, as there is only one extra table and a couple of columns.
However, it might look like a simpler design to an architect or on an ERD, but it has many
other issues.
So what are the cons or disadvantages of this design?
One disadvantage is that it performs pretty poorly when it has more data.
You need to write queries that access the table in a specific way, and when there is
a lot of data it can really slow down the query.
Another disadvantage, which is really tied to the first one, is that you can't effectively
index the columns.
Indexes work best when the data type and data follows a certain pattern and data type.
But if you want to index your attribute or value column, it won't be as effective because
it can contain all kinds of data.
If you want to write queries on an EAV table, they can get complex.
If you want to show the attributes and values for a record, you'll likely need to use a
subquery in the SELECT clause to get the data, and join to the outer query.
This isn't difficult, but it's a little more complex to do.
Using an EAV design also means you can't add data validation.
Relational databases work well as you can add data validation in the form of constraints
and data types.
Date data types can only contain dates, for example.
You can add check constraints to ensure values match a certain pattern or restriction.
You can't do any of that with an EAV design.
So, I would suggest avoiding the Entity Attribute Value design if you can.
There are several other solutions in this video, and hopefully one of them will work
for you.
If you do decide to use EAV, it's good to be aware of the disadvantages of it.
The second option for storing custom fields is something I call a modified Entity Attribute
Value.
It follows a similar design as the EAV.
You can see in the table there is an entity column and an attribute column, which are
used in the same as the EAV design.
The entity relates to the record in the other table, such as customer.
The attribute is the custom attribute that the user has defined.
The difference is that instead of one value column, there are three value columns: number
value, string value, and date value.
Each row will have a value in only one of these columns.
The reason for the three columns is that you can enforce data quality a lot easier by having
specific data types.
You can ensure dates are valid, numbers can be sorted, and so on.
The advantages of this approach is that it has additional data validation compared to
the EAV design.
Dates are valid, and numbers are valid.
You can sort numbers correctly and add them together.
Another advantage is that there is less need to perform additional functions to transform
the data.
You don't need to convert a string value to a date if the date is in a date column.
This can save time when returning the results.
It has the same type of flexibility as the entity attribute value design, allowing users
to customise the data they store.
You can enhance this to allow users to only select one of the data types to store.
This means, on a user interface, they could select either text, date, or number, which
can ensure only one of the columns is populated.
This design has several disadvantages though.
The first is the risk that multiple columns are populated, causing the data to be inconsistent.
If a row has both a string value and a number value populated, which one should be used?
We can add some logic to the application to prevent this, but it's still a small risk.
Another advantage is the poor performance.
This design doesn't really solve the performance issues of the EAV design.
Performance may be better, as indexes could work better on columns with specific data
types, but it's still not ideal.
You'll still need to write subqueries that could be complex to work with.
You also can't add the data validation you want.
While you can ensure a date is valid by storing it in a date column, you can't specify that
certain types of dates have a certain range, for example.
Same with numbers and text values.
So, while this is an improvement over the EAV design, it's also one I would avoid unless
it's a last resort.
The third solution is something called Single Table Inheritance, or just Single Table.
It's called Single Table Inheritance on a page on Martin Fowler's website.
It basically involves adding all of the columns you'll need to the main table.
You would need to think about what kind of data would need to be stored about the record
you're looking at.
This could end up being a lot of columns.
You could end up with 50 or 100 or 300 columns in a single table.
This design has its advantages and disadvantages.
One advantage is that all of the information for the record is stored in a single table.
This makes it easier to find the data and write queries.
You only select the columns you want, and can filter on the columns that you need to
filter on.
Another advantage is that it's easier to index.
You can create indexes on the columns that you frequently access or filter or sort on,
which can improve performance.
Queries on this design can perform pretty well, compared to other solutions in this
video such as Entity Attribute Value.
However, this depends on the volume of data and your overall query.
You won't need to write the same kind of subqueries that you need to do for the EAV design.
You can also add data validation to each of the columns if required.
Each column has its own specific data type, and you can add constraints such as Check
Constraints if needed.
One disadvantage is that there could be a lot of columns to work with.
Your table may end up having hundreds of columns, each representing a possible piece of information
to store about the record.
This may seem uncomfortable and it can be hard to convince others in the team if this
is the approach you want to use.
It can be hard to work with a table with hundreds of columns as well, depending on the IDE you
use and the kind of tasks your application does.
It also does not allow for true flexibility like other designs.
While you can have many columns in your table, there is no way to add custom fields to the
record.
You might have ten different phone number fields, which may seem like enough, but the
record needs to use one of those ten.
You can force a value into an existing column, but it's not as flexible as other designs.
This is one of the options I prefer, but it depends on the data you need to store.
Another solution option is another design from Martin Fowler's website, which he has
called Class Table Inheritance It's a design that has a table with a general
record type, such as customer.
You then have separate tables for each of the different types of customers you may need,
and each of them have different attributes.
It's similar to the previous solution of Single Table Inheritance, the one that has every
possible column, but it's broken up into separate tables.
You can relate each of the separate tables to the main table if you need to.
In this example, each of the different customer types can relate back to the main customer
table.
The advantage of this is that all of the columns you need are stored in the database, similar
to the Single Table Inheritance method.
You can write queries and filter on the data you need easier than using other methods.
Another advantage is that tables are smaller than the Single Table Inheritance method.
If having one table with 200 columns is not something you like, perhaps 4 tables each
with 40-50 columns could work.
You also get data validation and improved performance compared to other solutions.
You can use the inbuilt data validation that comes with using the right data types, create
indexes on columns for performance, and you can add constraints.
The disadvantage to this is that it's not truly flexible.
Users can't add their own custom columns, so will need to use an existing column to
put their data in.
Having multiple tables for different types of a record can also be confusing.
It can make queries harder to write and could mean more complex logic in your SQL and your
application.
So this is another solution to consider for your application.
The next solution for this is another one by Martin Fowler called Concrete Table Inheritance.
It's similar to the Class Table Inheritance, except with a small difference.
The difference is that there is no master or main table to store the core data for a
record.
Each record type has all of the attributes it needs.
In this example, there is no central customer table.
Each of the other tables has all of the data it needs, such as names.
Just like some of the other designs, it has good options for data validation, as you can
use the inbuilt validation for data types and can add constraints to tables.
It may also perform pretty well as you can index columns and not have to write subqueries
to get data like with the EAV design.
You also don't need to join to a central table to get the full picture of the record.
You can get all you need from a single table.
There are a few disadvantages to this.
Just like some of the other designs, it's not truly flexible.
While you can add heaps of columns to each table, it doesn't allow for the users to add
whatever columns they need.
It may also need extra logic added to your SQL and application to handle the different
record types.
Solution number 6 is one that uses separate attribute or lookup tables for each type of
data It's the most normalised of all of the solutions,
but it's also the least flexible.
This design can capture different types of attributes for a record.
However, you'll need to define them up front.
In this example, you can add different phone number types to a record.
Some of these types could be work, home, mobile, work mobile.
Each customer could have any or all of these types of phone numbers, and the records for
these are stored in a phone number table.
The types of phone numbers are stored in a separate table.
You would have different tables for each different type or group of attributes.
You may have a separate table for addresses, as a customer can have multiple addresses
with different types.
The advantage of this solution is that it uses the features of a database in the best
way.
It allows for relationships between different tables, capturing multiple attributes for
a record, and reduces redundancy.
You can use the inbuilt validation of the different data types, and add check constraints
if needed.
Having the data related to the original record, and a separate lookup table for types, can
ensure you have valid data.
The main disadvantage to this solution is that it's not very flexible.
It doesn't really offer the feature of custom fields at all.
Sure, you can add different types of attributes to your records, such as different types of
phone numbers for your contacts, but that's about all.
Solution number 7 is to use a JSON field to store this data.
You can define a JSON field in your table, and this field can be used to store whatever
attributes you want for your record.
It's flexible because the JSON field is essentially a text field that adheres to a certain format.
It's very flexible as you can define whatever attributes you want without changing the tables.
You can have as little or as many attributes for a record as you want, and they can all
be different from each other.
The JSON processing functions have gotten a lot better in recent versions of each vendor's
database.
I believe PostgreSQL was one of the first databases to add some helpful JSON functions
and other databases have followed, and they're pretty useful now.
You also only need one field, so it can help simplify your design.
With JSON fields it can be hard to enforce data validation, as it's essentially a formatted
text field.
There can be functions to help with adding and reading data, but it's more difficult
than other designs.
You'll be restricted to the JSON functions that are available in your database when working
with the data.
This might be OK, or it might not be, depending on which vendor you use and what you need
to do with the data.
It has the potential to get messy, as there is no structure or rules around the data.
This can cause issues with your application or performance if it's not handled.
The final solution option is what's called a dynamic schema.
This means that whenever a new field is needed by the user, your application or database
actually executes code to update the design of the tables.
This means that an Alter Table statement is run to add a column to a table.
It ensures the data is kept in columns that can have data validation, but the flexibility
of custom columns remains.
The advantage is that you keep the flexibility of adding whatever custom column is needed,
and the tables only get the columns that the users need.
The user can specify the column, its type, and then the application will run the necessary
code to update the table.
The main disadvantage is that you're essentially modifying the database on a live system which
can cause all kinds of issues.
There's a higher risk of these changes failing which can cause data to go missing or tables
to be locked.
Modifying the structure of a table from an application isn't really something a database
is designed to do.
Extra logic would be needed in the application to ensure this is done correctly.
This would include ensuring the field names adhere to the database naming standards, such
as lengths and reserved words.
Data types would need to be adhered to as well.
It can get quite complicated to develop and test this kind of feature.
So, those are the different solution options you have for allowing users to add custom
fields to a database.
Some of them work well, some of them don't, but you'll need to determine which one works
best for your situation.
If you learned something new from this video, make sure to subscribe to my channel.
If you want to learn more about database design and development, visit databasestar.com.
That’s where I share my best database-related content.
Which solution from this video is the one you'll likely use?
Was it the Entity Attribute Value method to offer high customisation, or the method of
adding all possible fields to a single table, or something else?
Thanks for watching.