As someone who has designed many product databases, let me give you a piece of advice: do not tie your database IDs to anything in the real world, and do not try to make them human friendly. You will hate yourself for it later when you discover that some new requirement breaks the scheme and renders IDs that look like they have meaning into something meaningless. For example, you will want to sell 5-gallon plastic containers of feul too, and those will end up with the ID 1000-72, which means nothing.

If you want to help people enter this stuff, the easiest approach is to let them pick from a list, possibly by selecting the baseitem first and then the item. If you are really stuck with manual command-line entry, just add "sku" or something as a unique key in your items table, and make it a well-chosen string like "5-gal-fuel".

The way to express the relatiosnship you're describing (one product type to many products) in a RDBMS is what I desccribed. What you have set up is what you would do if you wanted to make it possible for one item to be a part of multiple baseitems, i.e. this drum of feul is sold as part of the feul baseitem and also as part of the drums baseitem. That isn't what you want here. This may seem like an insignificant distinction, but designing your database to describe your data rules correctly really is very important. As a bonus, Class::DBI's built-in relationship methods would work for you then.

In reply to Re: Re: Re: Re: Re: Class::DBI multiple column primary and foreign keys by perrin
in thread Class::DBI multiple column primary and foreign keys by hangareighteen

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.