Hello friends. As we are in process of exploring good database architecture and concepts so it is very important to look into few Database Design Principles of a good database architecture. These database design principles are basis for any good database and should be kept in mind always while designing database systems.
Before going into principle part, let’s first discuss about what is information. Information is an valuable organizational asset and according to its value and scope, it must be organized, stored, secured, and should be readily available in a usable format for daily operations and analysis by individuals, groups, and processes, both today and in the future.
Everything is an information which is valuable to someone and can be used in productive or destructive ways. As there could be many dangerous impact of information if it is in wrong hands so it is very important to organize the information in secure way.
Looking into all above key attributes of information, let’s discuss now good information management system.
There are six main objectives which must be fulfilled effectively by a good database.
- Data Integrity
Let’s discuss a little in detail.
Any information which we are storing in any organization should be meaningful for that organization. If we are storing those factors which are actually not fit with organization’s requirement then this is just waste of resources.
Primary objective of any information system should be to meet organization requirements. Following are few points to consider while going to start an architecture.
- Properly get details about requirements.
- See how information can be fit with requirement.
- Trace requirement matrix to capture mapping of information architecture and requirements.
- Organize it simple.
- Decide upon format of data so that could be easily converted to meaningful representation.
As we know that everyday new business requirements come up and every day there is a need to change or enhance information system to capture new requirements. So information design should be extensible so that it can adopt new requirements without much efforts or without major breaking changes.
If your initial design is too much complex or unorganized then it may create trouble for you to adopt new things effectively.
Following are few points to consider when thinking of extensibility.
- Normalization and correct handling of optional data.
- Generalization of entities when designing the schema.
- Data-driven designs that not only model the data but also enable the organization to store the behavioral patterns or flow which can be hooked up in different stages of information processing.
- A well-defined abstraction layer that decouples the database from all client access, including client apps, middle tiers, ETL, and reports.
- Extensibility is also closely related to simplicity. Complexity breeds complexity. A simple solution is easy to understand and adopt, and ultimately, easy to adjust later.
Now at this point we understand that information is very much important for any organization. Based on the historic information, every organization makes different strategies, decisions for growth. One small mistake in data can lead to major issues with any organization’s key decision and hence a big risk for growth.
When we are designing a good information system then we must keep in mind about integrity, correctness of data. Our system should be smart enough to handle incorrect, missing data attributes and based on that it should either take corrective actions or straightaway reject the data. Incorrect data should not be present in system or at least should not exposed to individuals creating misunderstanding.
Data integrity can be of many types.
Involves the structure (primary key and its attributes) of the entity. If the primary key is unique and all attributes are scalar and fully dependent on the primary key, then the integrity of the entity is good. In the physical schema, the table’s primary key enforces entity integrity.
It defines that data should be of correct type and we should handle optional data in correct way. We should apply Nullability to those attributes which are optional for organization. We can define proper data types for different attributes based on organization’s requirement so that correct format data should present in system.
This defines if any entity is dependent on another one then parent entity should be there in the system and should be uniquely identifiable. We can do this by implementing foreign keys.
This defines that transaction should have its ACID properties. Any transaction should be atomic, consistent, durable and isolated. The quality of a database product is measured by its transactions’ adherence to the ACID properties:
Atomic — all or nothing
Consistent — the database begins and ends the transaction in a consistent state
Isolated — one transaction does not affect another transaction
Durable — once committed always committed
User defined integrity
There are few business rules which we cannot validate just by primary keys, foreign keys etc. There has to be some mechanism so that we can validate complex rules for integrity. We can implement these rules in following ways:
- Check Constraints
- Triggers & Stored Procedures
- Queries to identify incorrect data and handle in correct way.
As we know that information should be readily available as requested. Performance of the system should be up to the mark. As data in increasing day by day so at some time there will be impact on performance if database design is poor or we’ll not take any actions to improve performance.
Following could be few strategies which we can implement when there is need as data increases.
- A well-designed schema with normalization and generalization
- A sound indexing strategy, including careful selection of clustered and nonclustered
- Tight, fast transactions that reduce locking and blocking
- Partitioning, which is useful for advanced scalability
The availability of information refers to the information’s accessibility when required regarding uptime, locations, and the availability of the data for future analysis. Disaster recovery, redundancy, archiving, and network delivery all affect availability.
Following are the factors which impact availability:
- Quality, redundant hardware
- SQL Server’s high-availability features
- Proper DBA procedures regarding data backup and backup storage
- Disaster recovery planning
For any organizational asset, the level of security must be secured depending on its value and sensitivity. Sometime organizations has suffered a lot because of data leaks which results in loss of faith and tends to business risk. So security is one of the most important aspect of good database design.
We can enhance Security by the following:
- Physical security and restricted access of the data center
- Defensively coding against SQL injection
- Appropriate operating system security
- Reducing the surface area of SQL Server to only those services and features required
- Identifying and documenting ownership of the data
- Granting access according to the principle of least privilege, which is the concept that users should have only the minimum access rights required to perform necessary functions within the database
- Cryptography — data encryption of live databases, backups, and data warehouses
- Metadata and data audit trails documenting the source and veracity of the data, including updates
Based on above principles, one should start designing databases and architectures.