Successful Database Design depends upon thorough and accurate data analysis. We describe data analysis as the understanding and definition of 1) the business need for the data, 2) where the data originates, 3) where it gets modified, its data security requirements, 4) the validity checks that can be used to scrutinize the data and insure its accuracy and 5) the auditability of its changes.
Once the analysis has been completed the database design process will fully populate the entity relationship model. This will require:
Definition of the individual attributes. Each attribute will need to be described as to data types, size and format. For each attribute it is important to determine if there are a fixed or variable number of instances. Variable numbers of attributes require construction of child tables with “one to n” relationships. If it is important to record changes over time additional child tables will be required to record that values at the time points. Database enforced validations and constraints will need to be determined as will user based security needs.
Definition of the relations. Virtually all business or process tracking database systems use the relational (as opposed to the hierarchical or network) database model. Key to database design is therefore definition of the relations between tables and the keys that support those relations. At the same time data integrity needs to be determined and implemented including the important consideration of cascading deletes.
Definition of the views. Views are particularly useful for implementing security where certain users should have restricted access to the data. Views are also implemented for standard reporting requirements.
Business rule decisions. A decision as to where to implement the business rules will also impact the database design. Business rules can be implemented in the application code, within the database or within a middle (connecting) layer. The proper implementation will be determined by the scope of the application and whether or not the business rules are to be consistently implemented across all program module and user areas.
Stored Procedure Definition. Whenever possible, all database access should be accomplished via stored procedures. This practice increases security, reduces redundancy within the application and provides for maximum speed from database.
Index Creation. Indexes are created to not only increase the speed of the data retrieval but to also reduce the load on the database server. They permit the database to return records matching specified criteria without examining the full content of each record. Indexes need to be specified as allowing unique values or not.
Replication and distribution construction. Replication is a complex set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, wireless connections, or the Internet.
Backup and maintenance plan construction. SQL Server has a thorough set of services for backup and maintenance of the database. These include Checking Database Integrity, Shrinking the Database, Rebuilding and Reorganizing Indexes, Updating Statistics and Backing Up the Database.
Each of the steps above are required for a complete and thorough Database Design.