The ten biggest errors developers have with databases

The software world is constantly evolving with new, more fashionable, more elegant, more functional models. It's been a long time since IT products were just 'lousy' machines, each task performing the coder's coding function. Now, besides the ability to operate, people also care much about the beautiful style and interface but convenient. Saying that, there is nothing worth paying attention to, which has never been changed. Sometimes it is the bad habits of the administrator, especially in the use of databases. You can update a great AJAX Web interface, or the latest 'horrible' Windows user interface, but whatever you do, you can't ignore the database. No matter how developed the software is, how modern it is, how beautiful it is, the input and output data still needs to be done regularly with operations that have appeared decades ago. And, surprisingly, software developers still suffer from basic database errors like the period of Windows 95. Perhaps, most of us just learn how to use the database. not really studying it. Here are some of the biggest and most common errors when working with databases (databases).

Choose the wrong database

Not all databases are created the same. This means that before you do anything, make sure you have selected the correct database. There have been many cases where Access databases have been "down" because they have to download huge data sets, while they are just childish games with SQL Sever. Some annoying users also try to pay and install SQL Sever for the database with only a few hundred rows. On a large scale, the current market has 3 database layers: the first floor for personal computers (desktop floor) with embedded database, suitable for small jobs; the second floor is an 'Express' version of large players, with a capacity of several gigabytes of data; the third layer is the enterprise database layer such as SQL Sever, Oracle and DB2, which can control everything you put in. Before starting work, you need to assess the actual estimate of the total amount of data that will be stored in the future to select the appropriate product type.

Select too many databases

APIs such as ODBC, JDBC and OLE DB now tend to promote the database's independent concept. That is, you can write the source code of the application program and put any database into the data storage area. There have been many development teams stuck with database independence, writing layers to translate all SQL Sever commands to some of the lowest common forms of local languages ​​that all databases are aware of. That also means giving up some advanced functions in the database. This concept seems to say that some future clients may switch to Oracle or DB2 or FoxPro . So it is better to be prepared now. In contrast, when starting with a new product, choose the appropriate database storage and writing mechanism. If your product is good, people will install the database you describe. At that time, you will not be wasted hours on work supporting things that are never needed.

Do you understand your data?

If you can get one $ 6 for each customer, you can get a 6-digit account instead of 7 as it is, or if the case management office actually allows students to register without using To the identity card number and the ID column in the database must allow NULL, then many DBA administrators have been rich and untold. Database design cannot be carried out indiscriminately, ignoring all trade rules and regulations. You need to understand what type of user input data really belongs; How much to compress it to get the right column size; What are the principles that apply to it; What data type for each column; who has the right to update, etc.

Not always as simple as Excel

There is now a trend, especially in the opinion of small store managers, that any developer knows how to install a database. Honestly, it is not necessarily true. Does all developers know how to write source code on C # or install web services (Web Service). If so, we can all be database specialists. There are too many databases designed by people who have not even heard of basic terms, nor bother to improve their knowledge, understanding the different common forms of the database. I don't know how many times I have seen all the data being put into a big table, which leads to many unusual problems that occur when updating and executing data. If you are having this problem, learn more about database design. Do not explore everything about just the trial versions and errors.

The normal type of the third group is not the 'Holy Grail'.

In other words, sometimes a little bit of knowledge can become dangerous. I have seen some databases that have been normalized to the point of being stuck by 'goodwill' developers, who insist on putting everything in search tables. And a memorable session, in which 'yes' and 'no' are dropped into tblAnswers. This table can be referenced by the AnswerID foreign key from other tables. Yes, you need to know the principles of normalization, but also need to develop skills to understand when to stop, when to remove this activity for real practices.

It's great with hidden logic apps!

Storage procedures and constraints are excellent components. When there are multiple clients accessing the database at the same time, these procedures and constraints help ensure data consistency. At the same time they can also return to a black box containing hidden logic applications, which are normally not visible and cannot be viewed. There are many database codes that do not have the same design standards, testing, and review of the same source code as required. When you intend to put the source code into the database, take a moment to ask yourself if it really belongs to that place.

Who needs backups?

Who needs backups? It's you, the database administrators or the developers. Because of simplicity, your job is sure to store data in a Database. Maybe, at some point, for some reason like a hardware error, a hacker attacks, or simply because of the common errors that your database has experienced, lost or had to be reinstalled. Then you will definitely need backups. Backup plans (such as frequency of execution, backup type, etc.) should be implemented at the time from the beginning of the development cycle, not waiting until the completion of the process.

You need version control

Speaking of backups, in addition to changes in data, you also need to monitor changes to the database schema, proactively ready to recreate the database at any time. If you want to do the real work of a software builder, you need to extend version control in database design. You will not be able to recover 0.784.5 version of a software during debugging process for customers if the corresponding database cannot be created. If database developers are happily writing stored procedures, stopping the process of designing tables without leaving any trace in their work, you will encounter problems.

Use support tools

Modern databases often provide multiple buckets, allowing you to add data. They also have many support tools, making data management easier. As SQL Sever supports checking the start process of the server using the query. The database also provides a number of Wizards that tell you which indexes will perform queries more efficiently than the actual loading operations on the server. If you don't know which tools and utilities can be used in the database and how they can help you, read more about them.

Don't assume that everything is 'a nail' just because you have a big hammer in your hand

Databases today tend to include all data stored in one application. Many applications have been tested to build the metadata-oriented user interface. Then store metadata with user references in the same Database containing enterprise data. This is a good way to complicate life and execution skills. Some data actually belong to local files, not on the client-server database over the network. When storing data, you need to evaluate the different locations that can be placed (Database, registry, plain text file, XML file, .) and choose the appropriate location for each piece of data. Don't put it in the database automatically just because you have a connection. Today, the trend of using XML files appears more than relational databases, but the basic principle remains the same.