Slash Boxes
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
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

  • What people need to learn is called "data modeling" or "data architecture". I've worked as a DA many times, and not many people understood why I insisted on doing things particular ways. (Analogous to people questioning why you program things a certain way in Perl: why write tests? why use strict? they just get in the way.)

    Once you learn data modeling, it helps to improve your programming in general, because it also applies to data structures in your code. Have you ever looked at a multi-level hash in P

  • I wonder if the problem is made worse because of the number of people who have moved from 'toy' products e.g. Access into real design without formal training. I was recently looking at a product where the UI is ok but the underlying design is a mess - there is only one table with the different types of row indicated by various combinations of column values. You can almost smell the underlying spreadsheet that it was historically based on. It's produced by a one-man company and so I guess that explains a lot