What are some of the most common database design mistakes and how can you avoid them?

I've said it before and I'm saying it again here... The biggest mistake most companies make is NOT getting a "hybrid" DBA that has good system, design/modeling, AND database programming skills involved before the first requirement is written. As the infamous "Red" Adair is quoted as saying, "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur". I'll also warn that a "Professional" isn't necessarily someone with a lot of followers or a bazillion blog articles. "Choose wisely".

One prevalent database design mistake is not planning ahead. Rushing into designing tables and relationships without considering future needs can lead to problems. To avoid this, envision the future growth of your application. Will new features require additional data? Anticipate changes and design tables and fields accordingly. This proactive approach ensures your database can evolve smoothly as your project expands, preventing the need for major restructuring down the line.

I have been in this for almost 40 years as a front-end and database developer and architect. It is important to look beyond what is needed today and to apply an ERD (or the like) to the data both on the OLTP and OLAP sides of things.

Not knowing the amount of data you will be having for next few years and the purpose of your database. You need to consider the design to meet the demands from business and also the hardware/software it will be using. Echo of below need to consider - who is going to use that data and how often it will be updated/inserted/restrieved from the database. How much data is expected in future( e.g. next 5 years)

Some of the issues stem out of the users think they know what best for their data, but have no idea how that might fit in to a database. So we have to manage their expectations and sometimes we have to be tough to say this is how it's needs to be, otherwise it will not work.

A common oversight in database design is underutilizing indexes. Similar to a book's index, database indexes expedite data retrieval by pinpointing specific information swiftly. Without indexes, queries may scan entire tables, resulting in sluggish performance. To optimize: - Strategic Implementation: Apply indexes to frequently queried columns like primary keys and search criteria for enhanced speed. - Balanced Approach: Avoid excessive indexing to prevent bloating database size and slowing down write operations.

This is exactly my approach to data and many times it makes sense. I've learned that there are also use cases at which enforcing constraints is percieved as a disadvantage. The data owners need to have the data stored despite they are inconsistent arguing that storing inconsistencies is a better option than not storing the data at all. And I add: such approch should be always accompanied by automated processes that find these inconsistentcies and (probably) manual ones that correct them

This is where a company needs to define where the business rules are stored. Do the rules come from the business/application side, or from the database side? With these decisions it needs to be kept in mind about technology changes, etc.

The majority of the common mistakes listed and a lot of those not listed can be almost totally eliminated by just one thing that many people simply brush aside in the name of Knuth's infamous parable about how "Pre-optimization is the root of all evil"... They don't design the database first. Even when some do, they still "don't sweat the small stuff". As a result, most databases are slowly but surely dying from the "SQL Death by a thousand cuts". People need to understand that "milliseconds always matter" and stop sacrificing what is right to "meet a schedule". Ironically, if they did everything right to begin with, it would become a self-perpetuating habit that people would become naturals at and schedules would be met.

No hacer un diseño y estudio antes de crear las tablas es un error muy común, te puedes acostumbrar a crear tablas y guardar datos sin cuestionarte más allá, si ignoras las 1era formal normal, y luego la 2da, e incluso la 3ra. En la creación de tablas en una base de datos relacional, estas ignorando las reglas básicas para que el modelo te funcione. Hay que dedicar mucho tiempo y revisar muy bien para no crear redundancia o que se te quede un dato importante fuera.

- Prévoir un archivage des données au cours du temps si le logiciel doit tourner plusieurs années. - Faire des tests de performances avec un data set assez important qui peut représenter le volume de données après plusieurs années de fonctionnement.