in reply to Database Design Issues - OT

This is a religous war; and the way these are won is usually by killing everyone on the other side. Let's assume that is not an option.

If you have 270 products (rows?) and 10 categories, performance is not an issue, so a seperate table should be ok.

But with a seperate category table, you will have to build maintenance panels, etc. for each additional table. I've seen instances where the dba doesn't want to continually add lookup/validation tables. What we do is have one table with an additional column that is the field begin validated, and then we have one lookup table for any number of fields.

Another solution I have used is to have two entry fields: one text, one select. The select is populated with a "select distinct col from table", and the first option is "Existing values". The cgi code uses the value from the select if it has been changed, or from the text otherwise. At least this way the user can look at/choose existing values without worrying about typos.

Replies are listed 'Best First'.
Re: Database Design Issues - OT
by Abigail (Deacon) on Jul 13, 2001 at 16:11 UTC
    If you have 270 products (rows?) and 10 categories, performance is not an issue, so a seperate table should be ok.

    It's not just the amount of data that determines performance, it's also the number and kind of transactions. If all do is 10 simple selects a day, I agree, performance is not an issue. If you need to do 10,000 queries an hour, performance is an issue, even if you have only 270 products. Then not doing the join can certainly be noticeble.

    I've been a DBA in a previous job. My response to the original question is "the article contains not enough information to say whether the DBA is right or not". What (s)he says certainly does have merit. But it depends on a lot of things whether the performance gain is noticeble or not. How many selects? How many updates? How many concurrent users accessing the tables? How much cache? What else is going on? It requires an awful lot of knowledge, not only from the strategies of your database server but also from which transactions are going to be performed to say which will give the best performance. Perhaps only realistic testing can tell the difference. And then you still need to make the trade-off between performance and clean design. Note that the drawback (I could make a typo when inserting something) can be overcome in several ways. One could make an insert trigger that rolls back the transaction if an illegal category is inserted. Or one uses a list of constants in the code, or one makes a layer between application(s) and the database that does the actual creation of SQL - and never have applications touch the database directly. The layer could exists of some libraries (modules), but it could also exists of stored procedures. Or some combination.

    -- Abigail

[reply]
      Perhaps only realistic testing can tell the difference.

      What she said.

      Speaking as a DBA who doesn't spend all his time being a DBA, I love normalized data. I also understand why sometimes denormalizing is the right strategy, and that there are ways to maintain integrity while denormalizing.

      adamsj

      They laughed at Joan of Arc, but she went right ahead and built it. --Gracie Allen

[reply]

In Section Meditations