First off I don't think this is off topic at all. Consider that we all solve real world problems (normally for money) and we use Perl to make it more fun. Its this kind of thing that crops up all the time.

I hate to say it but I think that the only case for denormalisation comes once you have a fully normalised database - in other words at the end during the performance test phase. Not earlier.

Whenever I do any denormalisation, I tend to replicate IDs to simplify my joins to a smaller number tables (and have my code pull the IDs across) or I build a new table based on the type of data I am using (normally I use this just for reporting).

I have to agree with the other posts relating, mainly, to scalability. You don't know what will happen to the code and db once it leaves your grasp - but when it fails it will fall to you to fix it.

As an aside - I always build category <-> product relationships along the following:

CATEGORY
~~~~~~~~
ID
name
parent_ID


PRODUCT
~~~~~~~
ID
name
.....


PRODUCT_CATEGORY_LINK
~~~~~~~~~~~~~~~~~~~~~
category_ID
product_ID


What do you think? If you have a flat list of categories - all your 'parent_ID' entries will be 0 (default them to 0 in your DB design).

I'm not saying this is the best way to do it but when people have argue with me (during various projects) I have managed to argue this structure well enough to keep it :)

Good luck :)

In reply to Re: Database Design Issues - OT by simon.proctor
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.