Best Parctice for Database design or Modeling

Best Practice for Database design or Modeling



1. Use singular name for DB objects.
2. Don't use any space or special character or any reserve word in DB object.
3. Don't use any prefix like tbl / sp.
4. if we need to store password or any critical information in tables then it should be encrypted.
5. Create B-tree organizational structure instead of heap.
6. Try to clustered or non-clustered index on integer column. Index on varchar column may be causes of performance hit.
7. Double check before choosing datatype for a column like for flag use bit (0/1), for pre-defined list of column value use smallint, use date instead of datetime if possible.
8. Avoid to use sql_varient datatype that is equivalent of nvarchar(8000) for outside the world.
9. Create proper indexes that should be rebuild and re-created timely.
10. Use Includes keyword during Non-clustered index creation to prevent Key Lookup operator.
11. Define primary key and foreign keys in database tables.
12. Define partitions for large tables.
13. Define different-2 filegroups, we can arrange tables there based on frequently accessed, frequently joined, on there SCD Types, based on table partitions.
14. Put extra effort to decide fill factor value, in case if we have more update statements then fill factor value should be high.
15. Statistics should be set as auto update.
16. Separate Disk for temp db, mdf files, log files and backup files
17. proper set up for database security via schema, views, logins and roles
16. Set MaxDoop property based on no of CPUs for parllel processing.
17. Arrange tables at-least upto BCNF normalization form.
18. Create maintenance plans as well as backup plans for unexpected failure.
19. use filetables instead of file system to store document or picture file.