use Perl

• normalization (Score:4, Informative)

  by gav (2710) on 2003.06.10 13:47 (#20980) Homepage Journal

  It's worth pointing out that sometimes you can take normalization too far and make something that looks good on paper, but is a pain to worth with.

  For example, I ported an e-commerce site from JSP/MS-SQL to Perl/TT/MySQL and the schema was really nasty. Instead of creating a product table that looked something like:

  product(id, price, name, title, short_description, long_description)

  They split things into multiple tables in case they had to add extra fields in the future:

  product(id, price, name)

  text(product_id, key, data)

  I ended up wrapping this mess, and others (they had pricing tables with similar stucture), so that it looked like one table.

  I've also de-normalized tables in the past. One example was for a client that wanted to group products together in sets. Something like this:

  product(id,...)

  group(id, name)

  product_group(group_id, product_id)

  This was hard for the users to understand and things like having to have "default" groups was awkward (of course, a decent interface would have solved these problems but time and money was tight). A simple change made them happy:

  product(id, name, ..., group_name)

  The users were perfectly happy being told to type in a group name and keep it unique to make grouping work.

  It's a bit of a long and conveluted point, but I'm trying to say that you can't learn to design databases from reading a book. You need to get your hands dirty and find out what works from both a user and programmer's viewpoint. This is why good DBAs are rare.

  Reply to This

  • Re:normalization (Score:1)

    by runrig (3385)

    product(id, name, ..., group_name)

    That's fine, you could even have a table of valid group names as primary keys, and make the group in the product table a foreign key to the group table's primary key.

    As for taking normalization too far, I once experienced a table with:

    year(year_id, year_num)

    The year column was a just a serial id, and the year number was the valid years you could search for. So the product table was something like:

    product(product_id, ..., year_id)

    Searching for products from 1965 to 196