Designing a database may seem very simple but in reality is a complex thing. Some mistakes that you make can harm your project, increase costs and consume a lot of resources/storage space on hardware. Of course, the right data catalog software is needed to support this. Here are 5 mistakes that are often made when designing databases.
5. Dependencies on middleware
One of the most common errors often encountered is storing information in a database that cannot be used if there is no middleware or worse- a front-end. This is like a story that is told only half or part of the story.
Imagine a scenario where your history table says someone was “status 1” last week, and it was only yesterday that the same person became “status 12”. “What does status 1 mean? What does status 12 mean? “–DB analysts will definitely be wondering. And it will not get a clue because the information is in the middleware layer.
Whenever you define a column status number, it is a good idea to design a support table to explain what the status means. This will help you in doing data analysis.
4. The storage protocol is created individually–and the data is entered into the BLOB field
Programmers seem to love the BLOB field. They build their own protocol in their middleware and put any XML, any JSON in it. Applications run very smoothly, and all data becomes useless in every context other than their own application.
Ann Winblad once said: “Data is the new oil”. Every database designer should think about it too. Once you place data in a BLOB field with a custom protocol, you sacrifice all the database’s ability to process the existing data- filtering, pivoting, indexing and so on. So, avoid that. Unless you have some very specific cases and you would never want the existing data to be indexed. It is better for you to design all the required columns and save the data according to the columns created. And then, business glossary tool is also required to completed the steps.
3. Ignoring Timezone
Managing the time zone in the DATE and DATETIME fields can be a serious problem in the system. The system should always present the user with the correct date and time, especially in their own time zone.
For example, the expiration time of a special offer (the most important feature in any online store) should be understood by all users in the same way. If you just say “promotion ends on December 24th”, they will assume the promotion ends at midnight December 24th in their own time zone. If you mean “Christmas Eve” in your own time zone, you should say “December 24, 23.59 UTC” (or whatever your time zone is). For some users, the date will be “December 24 19.59”, for others it will be “December 25 4.49”. Be warned, users must see the promotion date in their own time zone.
In systems that have multi-timezones, fields of type DATE should not be used. This field must always be of type TIMESTAMP.
2. Column width is too short
If the field in the application is plain-text in its GUI (user can enter only unformatted comments) then that means the field can store up to 1000 characters of text. And that way no errors occur.
But if the field in the application allows multiple formats, such as bbcode or HTML. Then the exact number of characters entered by the user cannot be known. If they include simple comments like:
Then there will be 24 characters entered. Whereas in the GUI, the user only sees 17 characters entered.
So if the user is able to format the comment using some kind of WYSIWYG editor, it could potentially throw an error. When the user enters characters in a format that exceeds the maximum comment length (1000 characters in raw HTML) then the number of characters visible in the GUI may still be far below 1000.
When setting text field boundaries, you should always keep in mind about text encoding. Type varchar(100) means 100 characters in PostgreSQL but 100 bytes in Oracle! . For information, Dataedo is best alternative to support your business.
1. Misuse of data types
Every programmer knows integer, string, float and others. Well, let’s say everyone knows up about data types. However, databases usually offer several variants of the data type. So, the #1 thing on the list of errors you should avoid, is data type abuse.
Before designing any database, you should ask yourself what the options are. For example, the database you are using may offer the type INTERGER, but you should use TINYINT to store it. Date and time columns, floating point and decimal numbers. Some databases even support arrays and spatial data! So, whatever database you choose make no mistake about defining the right data types for each column.