Database Design Best Practices


I am pretty well versed with SQL Server, MySQL, Oracle etc but putting these Database products aside, is there a resource that will help me design relational databases well? Is there something like patterns or best practices for database design?

I have seen a few times that database is often not scalable; people have personal preferences with keeping columns like isChecked column which is boolean in nature but stored as Char(1) with values like 'Y' and 'N' instead of 0 and 1 which to me sounds better. Ways not to commit common mistakes while doing database design?

Links to books or articles will be highly appreciated.

Thanks in advance.

Best Solution

A few points:

  • Learn as much as you can about problem domain. You can't create good data model without knowing what you're designing for
  • Have good knowledge about data types provided by your database provider
  • How to properly use normalisation and design tables
  • Performance: when and how to apply indexes, how to write efficient queries etc.
  • When and how to use different DB objects like views, procedures, functions, triggers