Slash Boxes

All the Perl that's Practical to Extract and Report

More | Login | Reply
Loading... please wait.
  • 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:


    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.

    • 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