Scott W. Ambler has set up a great listing of basic database smells. It might be good to determine how it may be remedied, what database refactorings does apply like a remedy and when any smells are missing in the list.

For "guidelines" when responding, have a look at question on programming smells should you please.

I have done a few presentations in the MySQL Conference entitled "SQL Antipatterns."

Here's an array of the subjects:

  • Obligatory ID pseudokey when none is needed
  • Comma-separated lists inside a VARCHAR
  • Splitting a characteristic into multiple posts, e.g. salesJanuary, salesFebruary, salesMarch, etc.
  • Splitting a table into many tables monthly, per product, per customer, etc.
  • Entity-Attribute-Value (EAV)
  • Polymorphic associations
  • ENUM, or utilizing a CHECK constraint only for some values
  • Using FLOAT for the money
  • Utilizing a special value to indicate no value, rather than using NULL
  • "LIKE '%keyword%'" (a genuine full-text internet search engine could be better)
  • "SELECT *" or "INSERT INTO tablename VALUES (...)" (implicit posts)
  • SQL Injection (interpolating unvalidated application variables into SQL query strings)

In my opinion

  • Insufficient unique constraints
  • Insufficient Foreign Key constraints
  • Incorrect data type (like varchar or number for datetime)
  • Posts with null values for many rows
  • Insufficient check constraints
  • Insufficient Normalization (in 80% situation this really is good to complete)
  • Heck I've even seen DB without any index on primary key

And if the DB can be used by just one application and coded in a contemporary programming language (Java, C#, Ruby etc) then complex business rules in saved procs, in my experience, is really a smell.

Keep in mind that refactoring of the database will go for both (normalizing and denormalizing)

Also take a look at this little article.

And this summary article.

I am inclined to discover that a proliferation of "flags" is really a smell. In case your table has: IsThis, IsThat, and IsTheOther you might want to consider table inheritance to define the information inside your model.

Frequently what begins seeping in to the schema is other data that's only valid whenever a certain flag is placed. Which means you finish track of:

IsThis, ThisData, IsThat, ThatNumber, ThatCountry, IsTheOther, 
TheOtherAccount, TheOtherName, TheOtherZipCode, TheOtherPhone

Which is, IMO, what that initial "flag smell" has a tendency to result in used :-P

Insufficient enforcement of information integrity in the database level is my number 1. And even when the information integrity is enforced in the application level (shudder), then really enforce it. I should not be trying getting several emails to transmit an e-mail to and also have one of these be 'Talk towards the secretary using the large boobs' to choose a not-random example.

Using a fake data element to prevent getting nulls is really a large smell too. This produces much more problmes of computer solves and makes your computer data chaos. And what goes on when you really need to become a real value for your column rather than the NULL substitute. Null means unknown, which means is will not be anything except NULL. I perform a large amount of imports of information using their company companies' databases so when we obtain these items, how can we understand what the company rule would be to mean this is actually null data?