Software Development

20 Database Design Best Practices

  1. Use well defined and consistent names for tables and columns (e.g. School, StudentCourse, CourseID …).
  2. Use singular for table names (i.e. use StudentCourse instead of StudentCourses). Table represents a collection of entities, there is no need for plural names.
  3. Don’t use spaces for table names. Otherwise you will have to use ‘{‘, ‘[‘, ‘“’ etc. characters to define tables (i.e. for accesing table Student Course you’ll write “Student Course”. StudentCourse is much better).
  4. Don’t use unnecessary prefixes or suffixes for table names (i.e. use School instead of TblSchool, SchoolTable etc.).
  5. Keep passwords as encrypted for security. Decrypt them in application when required.
  6. Use integer id fields for all tables. If id is not required for the time being, it may be required in the future (for association tables, indexing …).
  7. Choose columns with the integer data type (or its variants) for indexing. varchar column indexing will cause performance problems.
  8. Use bit fields for boolean values. Using integer or varchar is unnecessarily storage consuming. Also start those column names with “Is”.
  9. Provide authentication for database access. Don’t give admin role to each user.
  10. Avoid “select *” queries until it is really needed. Use “select [required_columns_list]” for better performance.
  11. Use an ORM (object relational mapping) framework (i.e. hibernate, iBatis …) if application code is big enough. Performance issues of ORM frameworks can be handled by detailed configuration parameters.
  12. Partition big and unused/rarely used tables/table parts to different physical storages for better query performance.
  13. For big, sensitive and mission critic database systems, use disaster recovery and security services like failover clustering, auto backups, replication etc.
  14. Use constraints (foreign key, check, not null …) for data integrity. Don’t give whole control to application code.
  15. Lack of database documentation is evil. Document your database design with ER schemas and instructions. Also write comment lines for your triggers, stored procedures and other scripts.
  16. Use indexes for frequently used queries on big tables. Analyser tools can be used to determine where indexes will be defined. For queries retrieving a range of rows, clustered indexes are usually better. For point queries, non-clustered indexes are usually better.
  17. Database server and the web server must be placed in different machines. This will provide more security (attackers can’t access data directly) and server CPU and memory performance will be better because of reduced request number and process usage.
  18. Image and blob data columns must not be defined in frequently queried tables because of performance issues. These data must be placed in separate tables and their pointer can be used in queried tables.
  19. Normalization must be used as required, 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.
  20. Spend time for database modeling and design as much as required. Otherwise saved(!) design time will cause (saved(!) design time) * 10/100/1000 maintenance and re-design time.

Reference: 20 Database Design Best Practices from our JCG partner Cagdas Basaraner at the CodeBuild blog.

Cagdas Basaraner

Cagdas Basaraner is a software engineer graduated from Hacettepe University Computer Engineering department (Turkey), having 5 years professional experience. He is working on JEE web technologies, and also a former developer of information systems using Microsoft technologies and Command & Control (C4I) systems with Java technologies.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

15 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
José Luís Sanchez
José Luís Sanchez
12 years ago

While i do agree on most points, i do not on point 2.

From a abstract point of view, a table is a collection of entities.

So, USERS will store a collection of USER entities, so it will have USERS.
The same applies to CUSTOMERS, ORDERS and so on. There will be ( hopefully ) more than 1 row of data, so PLURAL is a must. That’s why databases were created to, to store more than 1 row of data, otherwise, properties will be enough, IMHO.

Anyway, great post !

Richard GUITTER
Richard GUITTER
12 years ago

Another way to think about this, is to say that one row represent one User entity. So looking at an ER diagram you will say one User has 0..n Privilege.
Same thing for joint table which express the relation between one entity to another : UserPrivilege for example

PavelRudensky
PavelRudensky
12 years ago

Yes!!! Good rules…  Point 2 i use like José Luís Sanchez… I naming primary key columns like IDs… CourseID – foreign key on table Course.

麥當勞
12 years ago

I doubt point 4. If you have a big schema, the first question is whether an object a table, view, materialized view, synonym or otherwise something atypical. The content of database object is the next thing.

Laurent Simon
12 years ago
Reply to  麥當勞

Point 4 is a good practice because the today’s table can be a view tomorrow. Using  a table, a view, a materialized view or a synonym doesn’t matter for a program that does a “Select”. The technical prefixes restrict databases evolution more that they help. If i want to know if it’s a synonym, a table or something else, I look at the database dictionary. 

Pavel Tavoda
Pavel Tavoda
12 years ago

I disagree with 11. Go for persistent framework specially for small and middle sized projects. For big projects (e.g. more than 400 tables) be careful about planing and query optimization specially for big tables (like more than 3Mil rows).
Also some points don’t apply for all databases. You have to look for specific DB features.
Point 12 is senseless. If tables are unused than remove them, if rarely used it will bring no advantage to move them to different physical storage. Moving BIG tables and partition it on different physical storage will bring performance advantage. 

Laurent Simon
12 years ago
Reply to  Pavel Tavoda

Point 11 mainly depends on your development team strengths. For a mid range development team i think that point 11 is well formulated and, if i want to distinguish between big and small projects i would say the reverse: You can go by hand specially for small and middle sized projects but never for big ones. On small projects you can afford some architectural refactoring to correct defects. On big projects, you can’t afford it. Persistence frameworks gives an opportunity to correct lot of architectural programming defects without  breaking every thing and always allow you to fix critical queries by… Read more »

Neal
12 years ago

point 6 and 19 ..you say use surrogate keys in 6, but then back out in 19
Normalize well for posterity, imo.

Rodger
12 years ago

Hello, 

I’ve written a whole series on Database Design Mistakes To Avoid
http://rodgersnotes.wordpress.com/category/database-design-mistakes-to-avoid/

As for point 20, see:
http://rodgersnotes.wordpress.com/2010/09/14/database-design-mistakes-to-avoid/

Concerning triggers and performance:
http://rodgersnotes.wordpress.com/2010/09/18/referential-integrity-and-performance/

May I add another “Best Practice”.

# 1: Hire some experienced and serious database expertise who really knows what they are doing. No amount of java can compensate for a really bad DB put together by beginners.  

HTH

Laurent Simon
12 years ago

I disagree with point 10. Not using “select *” is not a matter of performance (or not only), it’s a matter of reliability. I would say:

 Never use “select *” queries, always use “select [required_columns_list]”.

“Select *” is unreliable because if a new column is inserted in the table, your program will not be able to handle the new results. 

EvilGuy
EvilGuy
11 years ago

Objection on Point 15

EP
EP
8 years ago

Password shouldn’t be encrypted. They should be hashed.

BB
BB
7 years ago
Reply to  EP

+1

Jueser
Jueser
7 years ago

Thank you very much for these tips it’s very useful for me.

Aleks Shamles
Aleks Shamles
1 year ago

Felicitats a tu també, amic meu. No has indicat la teva edat, però m’agradaria advertir-te del problema de la disfunció erèctil perquè almenys puguis evitar-la en el futur. Si voleu gaudir sempre de la vostra vida sexual i no tenir problemes d’erecció, aleshores la hauria de ser la vostra prioritat número u. Podeu fer clic aquí per obtenir més informació sobre el producte. Molta sort amb el teu casament.

Back to top button