Author: Dominika Florczykowska
Dominika is a Python software developer. She graduated from the Warsaw University of Technology with a degree in computer science. At Vertabelo, she creates content as a technical writer. In her free time, she runs, practices yoga, and learns foreign languages.
How to Work With Historical Data: Log Data Changes
It is important to log information about changes in a database. We should track who introduced the change, when it happened, and what was done. This way, even if the operational data does not exist anymore (for example, because the record has been deleted), we can still recover a trace of the user's activity.
How Should the Logs Be Stored? First of all, logs should be stored in a separate table (or tables).
What Is a Foreign Key?
What is a foreign key constraint? Why is it important in relational databases? Find out all about foreign keys in this article.
A foreign key is a concept that is often used in relational databases. It is a way to create a link between two different tables. A foreign key is a field that refers to another table‘s primary key. Look at the example below: each player is a member of one team.
How to Work With Historical Data: Use Separate Tables for Active and Historical Data
What do you do if you need to keep both current and historical versions of your database records? If there are a lot of them, storing everything in one table may cause performance problems and complicate logic, especially if most of the operations are performed on the active data.
You can solve this problem by storing the current and historical versions in separate tables. In our example, the current and historical account plans were put in two different tables: current_account_plan and account_plan_history.
How to Work With Historical Data: Use a Flag to Mark Active Records
Often, there are objects in databases that should not be removed even though they are no longer used. Imagine a store database that includes the following tables:
What happens if the store decides a certain product type is no longer sold? Do you delete it from the database? That is probably not a good idea, as other objects, such as historical orders or invoices, may still be linked to this product type.
Use the Shortest CHAR Length to Accommodate All Values
A CHAR column has a fixed size. If the string value is shorter than the column’s stated size, it will be padded with blank spaces until it meets that size. Thus, table size can be significantly reduced by decreasing the declared CHAR length.
The CHAR data type should be used when all the values have the same length. Otherwise, it is better to choose VARCHAR.
Let’s use a simple shop diagram as an example:
Use Nullable Columns Sparingly
Nullable columns in a database should be used with caution. When a table contains a lot of NULL values, they are difficult to work with: you have to remember to account for NULL values when writing queries and writing code (in many programming languages, they will result in a NullPointerException).
If many columns in a table are nullable, sometimes it is a good idea to move these optional fields into a separate table and leave only a nullable link to the new table.
Always Define a Primary Key for Each Table
In a relational database, each table should have a primary key (PK). A primary key has multiple advantages for a table, including a:
UNIQUE constraint A primary key adds a UNIQUE constraint to a column. This ensures that the data in that column is not duplicated. If an object with the same primary key value is already present in the table, we should update the object instead of creating another one.
Don’t Store Images and Media Files in a Database
Storing binary files in a relational database is not very efficient. The process of saving and retrieving the files from a database is generally much slower than using file storage. Also, it is a good idea to keep your database as small as possible. If the database is huge, backups become very large and difficult to manage.
Instead of keeping large files in the database, we recommend putting them on disk or in a file storage service (such as S3 in AWS).
Use the DECIMAL Data Type for Money Values
Picking the right type for a column is important, but the best choice is not always obvious. A common beginner mistake is to use a floating point type to store financial values.
It is very important not to use floating point types (such as REAL, DOUBLE PRECISION, etc.) to store financial data. Calculations performed on floating point types can be inaccurate, which is unacceptable when working with business-related data. If you’re dealing with money, approximations are not sufficient!
How to Get Help in Vertabelo
Have you ever wondered what to do if you need help with Vertabelo? Find the available options here.
The first place to look for help is the Vertabelo documentation. You can find it at https://www.vertabelo.com/documentation. It provides detailed information on managing your documents, logical data modeling, physical data modeling, and SQL scripts. There’s also a FAQ section on our site to help you quickly get answers to common questions.
If you cannot find a solution in the FAQs or the documentation, visit our support site at https://support.
