this is quite an off topic subject as it is in fact just a database design question, but i'm sure there sure are some DBMS specialists among us that maybe could and would illuminate me :)

i'm working on a 'virtual catalog' project that requires building some kind of 'directory tree' inside a DB. There are stored 'categories' and 'items'. Each 'item' has a number of attributes, different for each category.
here is an illustration that should explain it all: 

base/
   persons/
      kids/
        jim        #   name, surname, birth, mom_name, pap_name, 
        tim        #   sister_name, brother_name, ....
        john       #
      grown_ups/   
        bob        #   name, surname, birth, salary, company,
        rob        #   phone_no, fax_no, email, homepage, ...
   books/
      manuals/
        linux        # pages_no, authors, edition, cover_photo,
        math         # editure, pub_year, ....
        geography    #
      dictionaries/
        greek        # terms_no, editure, editure, pub_year ...
        latin        #
        arabic       #
      sf/
        dune         # pages_no, volumes, editure, author, pub_year
        foundation   # prizes, rating, ...
        ubik         #
   computers/
      workstations/ 
        john's       # owner, os, monitor, video, ram, ip, location,
        mine         # hdd, cpu, keyb, mouse, ...
        his          #
      servers/       
        alpha        # location, ip_list, os, ram, cpu, hdd, services
        beta         # ...
        gamma        #

ok, so I have all these different 'things' that should live together in the same eclectic catalog. I want to be able to do complex but still fast searching all around this db. The scalability range I would like to be somewhere near thousands of categories and thousands of unique items in each category.

how to organize it? As I see it at this moment there are two main solutions:

What would be the best database design for such a thing, keeping in mind that searching through this db should be as fast as possible? Also remember that attributes may differ a lot in number (and size).

The first solution I think it's crappy and wouldn't like to go on with it. The second is the one that I have already implemented and the results are pretty good until now, but I haven't yet had the time to build up some real stress test scenarios so I don't know for now what could happen when I would reach to 1K categories and 1K items in each category =)

heh, I haven't mentioned the DBMS because I wouldn't like to get some particular solutions optimised for xSQL or ySQL ;-) ... yet, ofc, notices regarding implementation flaws on i.e. mySQL or PostgreSQL or whatsoever are welcome

--
AltBlue.

In reply to database design issue: one big table vs hundred of smaller tables by AltBlue

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.