With the amount of time being spent on databases, you can really start to see a difference in quality between those who know what they’re doing and those who have no idea.
You don’t want to be that guy. So here are five database pitfall you should work on avoiding.
Number One: Abuse of Primary Key Value
The worst mistake found in database development is having a developer who has absolutely no idea how the primary key should be used. The primary key value has nothing to do with the data nor does it have any use for application data. Primary keys must be randomly generated by the database and never be changed unless there are certain circumstances.Those circumstances are not appropriate to be covered here but if everyone wants a post on that I would be happy to put one together.
Number Two: Turning Everything & Anything Into Something
With organizing data, some mistakes developers make lies with object design. Developers have the tendency to turn every useless detail into something. Unless there is a special issue where the data has its own unique needs, there are some things that should or should not be split out. If the data is shared among multiple rows, one change will affect the other rows. If the data is shared among multiple rows, it should not affect the other rows. Adding complexity to your database can greatly increase the difficulty of working with the data base. Anytime you are adding complexity always ask if its worth the return.
Number Three: Overused Stored Procedure
While stored procedures are useful, most modern applications make them less necessary. More and more stores procedures are becoming an old method of database management. Stored procedures are now considered as a maintenance disaster as there is no easy way to audit which apps are using them for. This means that any major change will require you to write another procedure rather than changing the existing one. Make it easy on the person who will manage the database in the future and don’t overuse this.
Lack of a Primary Key
Developers who use the wrong type of data for primary keys will find out they may not be unique. This situation will play a role on the proliferation of stored procedures and views as subsequent developers have a hard time masking the database mistake.Just don’t do it. Now that I think of it, we do need a primary key post!
Hard Delete of Data
While hard delete of data can make sense, they are often less common than the times you will need to restore the database to a new server. That is why it is better to use soft deletes on applications as the ORM will automatically filter by default.
What are your thoughts on these common database pitfalls? Comment below and share your opinion with us!