What is best Database design guideline approach.

Today I will clarify about "What is good approach to design database in SQL server or oracle"Now days most developer are forget to follow these thing when design database for any type of application.

1.Naming convention

2.Data type 

3:-Indexing

4.Normalization

5.RDBMS concept 

6.Performance 

7.Replication

8.Constraints 

9.Clustering

10.Stored procedure

11.Comment line

Follow these step to create good database architecture :-

Always use comment line inside stored procedure,function,trigger in sql query.

You should use stored procedure instead of inline query for application performance .

Use Cascades, Triggers, and Constraints according requirment .

Always try to avoid overloading of fields name

Always use well defined and consistent names for tables and columns in any database (e.g. Employee, EmployeeName, EmployeeID ...).

Always  use singular word  for table names (i.e. use EmployeeName instead of EmployeeNames). Table represents a collection of entities, there is no need for plural names for table.

Don’t use spaces for SQL table names. Otherwise you will have to use ‘{‘, ‘[‘, ‘“’ etc. characters or sign  to define tables (i.e. for accessing table  Employee Name you'll write “Employee Name”. EmployeeName is much better).

Don’t use unnecessary prefixes or suffixes when you'll create table names (i.e. use Employee instead of tbl_Employee, Employeetable etc.).

Keep passwords as encrypted for security purpose . Decrypt them in application when required in SQL server database.

Always  use integer id fields for all tables in SQL database . If id is not required for the time being, it may be required in the future purpose  (for association tables, indexing ...).

Always choose columns with the integer data type (or its variants) for indexing. varchar column indexing will cause performance problems in SQL server database.

Always  use bit fields for boolean(true/false) values. Using integer or varchar is unnecessarily storage consuming. Also start those column names with “Is” (e.g. IsActive )

Always  provide authentication for database access. Don’t give admin role to each user in SQL server database.

Always  try to Avoid “select *” queries until it is really needed. Use "select [required_columns_list]" for better performance in SQL database. Always use select query with column name.(e.g. "Select Id,Name from Employee"). 

Always use an ORM (object relational mapping) framework (i.e. Nhibernate, iBatis ,Entityframework) if application code is big enough. Performance issues of ORM based frameworks can be handled by detailed configuration parameters.

For big, sensitive and mission critic database systems,Always  use disaster recovery and security services like failover clustering, auto backups, replication etc in any type of database.

Always use constraints (Primary key,foreign key, check, not null ...) for data integrity. Don’t give whole control to application code.you can 

Always use indexes for frequently used queries on big tables from database . Analyser tools can be used to determine where indexes will be defined using ETL process. For queries retrieving a range of rows, clustered indexes are usually better in SQL. For point queries, non-clustered indexes are usually better in SQL query.

According application level security database server and the web server must be placed in different machines. This will provide more security (attackers can’t access data directly on your server ) and server CPU and memory performance will be better because of reduced request number and process usage from application.

Image and blob data columns must not be defined in frequently queried tables because of performance issues in SQL database query. These data must be placed in separate tables and their pointer can be used in queried tables.

Normalization must be used as required,to reduce redundancy,  to optimize the performance. Under-normalization will cause excessive repetition of data, over-normalization will cause excessive joins across too many tables. Both of them will get worse performance in sql query.

You have just read an article that categorized by title Interview Question by title What is best Database design guideline approach.. You can bookmark this page with a URL https://bikeshsrivastava.blogspot.com/2016/07/part-26what-is-best-database-design.html. Thank You!

Author: Bikesh Srivastava - Thursday, July 7, 2016