Database Design Best Practices [closed]

Question

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.

Answer 1

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

Answer 2

There are numerous database design patterns. They aren't often nicely formalized, so you may have to simply look at lots of database design.

See, for example, Fowler's books on design patterns. Also Nock's Book.

There are blogs, like database programmer.

There's an IEEE book, On Pattern-Based Database Design and Implementation.

The Google Search (link) turned up 24M hits.