Bad real-world database schemas

Question

Our masters thesis project is creating a database schema analyzer. As a foundation to this, we are working on quantifying bad database design.

Our supervisor has tasked us with analyzing a real world schema, of our choosing, such that we can identify some/several design issues. These issues are to be used as a starting point in the schema analyzer.

Finding a good schema is a bit difficult because we do not want a schema which is well designed in all aspects, but a schema that is more "rare to medium".

We have already scheduled the following schemas for analysis: wikimedia, moodle and drupal. Not sure in which category each fit. It is not necessary that the schema is open source.

The database engine used is not important, though we would like to focus on SQL server, Posgresql and Oracle.

For now literature will be deferred, as this task is supposed to give us real world examples which can be used in the thesis. i.e. "Design X is perceived by us as bad design, which our analyzer identifies and suggests improvements to", instead of coming up with contrived examples.

I will update this post when we have some kind of a tool ready.

Answer 1

Check the Dell-dvd-store, you can use it for free.

The Dell DVD Store is an open source simulation of an online ecommerce site with implementations in Microsoft SQL Server, Oracle and MySQL along with driver programs and web applications

Bill Karwin has written a great book about bad designs: SQL antipatterns

Answer 2

I'm working on a project including a geographical information system. And in my opinion these designs are often "medium" to "rare".

Here are some examples:

1) Geonames.org

You can find the data and the schema here: http://download.geonames.org/export/dump/ (scroll down to the bottom of the page for the schema, it's in plain text on the site !)

It'd be interesting how this DB design performs with such a HUGE amount of data!

2) OpenGeoDB

This one is very popular in german-speaking countries (Germany, Austria, Switzerland) because it's a database containing nearly every city/town/village in the german speaking region with zip-code, name, hierarchy and coordinates.

This one comes with a .sql schema and the table fields are in english, so this shouldn't be a problem.

http://fa-technik.adfc.de/code/opengeodb/

The interesting thing in both examples is how they managed the hierarchy of entities like Country -> State -> County -> City -> Village etc.

PS: Maybe you could judge my DB design too ;) DB Schema of a Role Based Access Control

Answer 3

vBulletin has a really bad database schema.