Microsoft SQL Server Database Design Principles – (Part 1)

Author: Basit A. Farooq

Database design is one of the most important tasks in the systems development life cycle (SDLC), also referred to as application development life-cycle (ADLC). That’s because, the databases are essential for all businesses, and good design is crucial for any business critical high performance application. Poor database design results in wasting time throughout the development and often leads to unusual database that is unfit for purpose.

These are the topics we’ll be covering in this article series:

· Database design process and considerations

· Table design process, which includes identifying entities and attributes, creating relationship between entities, and ensuring data integrity

· Data normalization basics

· SQL Server Storage Engine and database architecture

· Importance of choosing appropriate data type

Database design

The database design process consists of a number of steps. The general aim of a database design process is to develop an efficient, high quality database that meets the needs and demands of the application and business stakeholders. Once you have a solid design, you can build the database quickly. In most organizations, database architects and database administrators (DBAs) are responsible for designing a database. Their responsibility is to understand the business and operational requirements of an organization, model the database based on those requirements, and establishing who and how the database will be used. They simply take the lead on the database design project and are responsible for the management and control of the overall database design process.

The database design process can usually be broken down into six phases, as listed below:

· Requirement collection and analysis phase

· Conceptual design phase

· Logical design phase

· Physical design phase

· Implementation and loading phase

· Testing and evaluation phase

These phases of design do not have clear boundaries and are not strictly linear. In addition, the design phases may overlap, and you will often find that due to the limitations of the real world, you may have to revisit a previous design phase and rework some of your initial assumptions.

Requirement collection and analysis phase

In this phase, you interview the prospective users, gather their requirements, and discuss their expectations from the new database application. Your objective in this phase is to gather as much information as possible from potential users and then documenting those requirements. This phase results in a concise set of user and functional requirements, which should be detailed and complete. Functional requirements typically include user operations to be applied to the database, information flow, type of operation, the frequency of transactions and data updates. You can document functional requirements using diagrams such as sequence diagrams, data flow diagrams (DFDs), scenarios, etc.

Moreover, you also conduct an analysis of the current operating environment, whether it’s manual, file processing system or an old DBMS system, and interact with users extensively to analyze the nature of the business to be supported and to justify the need for data and databases. Requirement collection and analysis phase can take a significant amount of time; however, it plays a vital role in the success of the new database application. The outcome of this phase is the document that contains the user’s specifications, which is then used as the basis for the design of the new database application.

Conceptual design phase

Your goal during the conceptual design phase is to develop the conceptual schema of the database, which is then used to ensure that all user requirements are met and do not conflict. In this step, you select appropriate data model and then translate the requirements arising from the preceding phase into the conceptual database schema by applying the concepts of chosen data model, which does not depend on RDBMS. The most general data model used in this phase is the entity-relationship (ER) model, which is usually used to represent the conceptual database design. Conceptual schema includes the concise description of the user’s data requirements, including the detailed description of the entity types, relationships, and constraints.

Conceptual design phase does not include implementation details. Thus, end users can easily understand them, and hence, can be used as a communication tool. During this phase, you are not concerned about how the solution is implemented. In conceptual design phase, you only make general design decisions that may or may not hold when you start looking at the technologies available, and project budget. The information you gather during the conceptual design phase is critical to the success of your database design.

Logical design phase

In logical design phase, you start mapping entity-relationship (ER) model into a relational schema. During this phase, you translate the high-level conceptual schema implementation data model of the selected RDBMS constructs. The data model chosen will be representing the company and its operations. From there, a framework of how to provide a solution based on the data model solution is developed. In this phase, you also determine the best way to represent the data, the services required by the solution and how to implement these services. The data model of logical design will be a more detailed framework than that one developed during the conceptual design phase. This phase provides specific guidelines, which you can use to create the physical database design.

You make little, if any, physical implementation work at this point, although you may want to do a limited prototyping to see if the solution meets the user expectation.

Physical design phase

During the physical design phase, you make decisions about the database environment (database server), application development environment, database files organisation, physical database objects, etc. Physical design phase is a very technical stage of the database design process. The result of this phase is a physical design specification that is used to build and deploy your database solution.

Implementation and loading phase

During this phase, you implement the proposed database is implemented. The phase includes activities such as, the creation of the database, compilation and execution of DDL statements to create the database schema and database files, manually or automatically load the data into a new database system from a previous system, and finally configure database and application security.

Testing and evaluation phase

In this phase, you perform testing of your database solution to tune it for performance, integrity, concurrent access, and security restrictions. Typically this is done in parallel with application programming phase. If the test fails, you take several actions such as adjusting performance based on a reference manual, physical design modification, logical design modification, upgrade or change the SQL Server software and database server hardware.

Database Design Life Cycle Recap

The following figure briefly illustrates the database design process:

Table Design

As mentioned earlier, you complete table and data design activities during the conceptual and logical design phases of database design. During the conceptual design phase, you identify specific data needs and determine how to present the data in the database solution, which is based on the information you collected in the requirement gathering phase. You then use the information from the conceptual design phase in logical design phase to design and organize your data structure. In logical design phase, you also identify the requirements for database objects to store and organize the data.

Often, one of the most time-consuming and important tasks in physical design phase is the table design. During the physical design phase, you identify:

· Entities and attributes

· Relationships between entities

Tables

We use tables to store and organize data in the database. A table contains columns and rows. For example, below is an example of how a Customer table might look. Each row in the Customer table represents individual customer. The column contains information describing the data for the individual customer. Each column has a data type, which identifies a format in which data is stored in that column. Some data types can have a fixed length, meaning there size does not depend on the data stored in it. You also have variable length data types, meaning their length change to fit the data they possess.

Entities

Entities are business objects that your database contains, and are used to logically separate the data in the database. Entities List is used to determine the tables, which is necessary to create, as part of the physical design phase. You create a separate table in the database for each entity (such as customers, employees, orders and payroll). Entities are characterized by attributes. For example, you declare each individual attribute of an entity (such as individual Custormer, or individual Order, or individual Employee , or a individual Payroll record) as a row in the table.

Attributes

An attribute is a property of an entity. For example, the employee entity has attributes such as employee id, first name, last name, birthday, social security number, address, country, etc. Some attributes are unique values. For example, each customer in a customer table has a unique customer number. Attributes are used to organize specific data within the entity.

Continue to part-2…