What are the most important database design principles?

In my experience, the foundation of successful database design begins with clarity on its purpose and scope. By defining goals, data requirements, and stakeholders early on, you set clear boundaries and streamline development. This approach minimizes complexity and redundancy, ensuring your database meets its objectives efficiently. Let's build databases that align seamlessly with business needs and user expectations!

When designing any system, you always need to consider the data residency part, do we need to persist data related to users and system components? All these would be decided upon properly understanding the domain of the problem or the business you are trying to build a solution for. Once you got that, then you can start preparing for your database to understand who will be using it and what sort of data would be stored.

I think the most important consideration for design is the intent and purpose of your database. Although all Databases start with a well defined intent and purpose, it gets lost or muddied over time especially when the company experience high growth or needs fast paced deployments. I think spending the initial time to design your schemas in such a way that things can be easily moved out to separate DBs as needed to scale is super critical. Also not allowing for direct access to Databases for any part of your application (i.e) use an abstraction layer such as APIs , CDC Events for Replication is very important to avoid major rework when things aren't scaling. You essentially need someone to play good cop/bad cop when it comes to DB design.

Keep it small and simple. be greedy to save data and just store them when your business needs them. try to develop the tables and fields on purpose. I've confronted with many databases that have a abundance of tables or fields without any functionality over the time.

Database should be in at least 3nF. Data redundancy and replication should be at minimum. Tables should implement relational database with primary and foreign keys specified. Specify partition level range and hash for data warehousing and crud operations. Triggers to be used only when needed before insert after insert … Schedule transfer and maintenance operations in data jobs offline. Define cursors when needed

It is important to have a clear view of each model. First of all, I recommend that you do search samples like your business. It helps you which model have they used the most.

Actually, it is a trade-off and you can't stick to the rules just due to having normalized database but you fail at performance or make developers in trouble for saving records. Experience is adventurous in knowing these rules because you saw many issues and problems in your previous development.

In my experience when we applying normalization for our database can siginficantly reducing data duplication, but we hardly to manage or maintain data level used by apps

It sounds to be just a discipline but I think it is more than organizing. I do believe that being neat helps us to track issues quicker. do you really think that they are just names ? if you think, you should do search about prefix of sp_ and usp_ in sql server and you would wonder how it affect on performance!

There are many ways to have fun in tech, but fixing data is not one of them. Default Value Constraints are critical, especially while evolving or changing your schema. Adding new columns can lead to challenges in maintaining existing records. Default constraints populate new columns with values, preventing potential issues from null values. Using default values can improve performance. It reduces the need for additional checks or updates to handle null values in the database. They are simple but powerful.

When it comes to state persistence, there are only two concerns: optimize for write and read. For write, we don't want record locks. We don't want any locks in a globally distributed world. So, for write, an ordered, append-only state transition log seems to be the only logical option. Using this log as the sole source of truth, enables the opportunity to project it onto any read-optimized, materialised and reproducible view desired. Maybe you know this as ES/CQRS...