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

In reply to Re: Database Design Issues - OT by Abigail
in thread Database Design Issues - OT by Ovid

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.