SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Apr 2002
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    need help with db structure

    Hello,

    I have some 20000 links, which I'd like to store in a mysql database to create a searchable yahoo-like directory (with php). I can't decide how to structurize the db:

    1. to have for each category a separate table? wouldn't this slow down the searches?

    2. to have all links in one big table, with a record, in which categories they belong? wouldn't this slow down the listing of categories?

    3. some combination? how?

    4. any other options? tell me pleeeassse:-)

    ..thanks in advance,
    qwedsa

  2. #2
    What? Maelstrom's Avatar
    Join Date
    Oct 2001
    Location
    Whistler BC originally from Guelph Ontario
    Posts
    2,175
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is a quick and rough way to do it

    a) if anything this would speed up the searches due to indexing and the cut down of redundancy

    b) yep. It would also create a large database due to a lot of redundancy



    LINKS
    lid pk
    link
    linkDesc

    CATEGORY
    cid pk
    lid fk
    catDesc


    You could even setup a quick search engine. It is beyond the scope of this letter to explain how but here is the idea

    SEARCH
    sid pk
    lid fk
    word

    Now when you do a search you search this table which relates back to the original LINKS table. Here is an article that gives an ok explanation.

    http://www.phpbuilder.com/columns/clay19990421.php3
    Maelstrom Personal - Apparition Visions
    Development - PhP || Mysql || Zend || Devshed
    Unix - FreeBSD || FreeBsdForums || Man Pages
    They made me a sitepoint Mentor - Feel free to PM me or Email me and I will see if I can help.

  3. #3
    SitePoint Member
    Join Date
    Apr 2002
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    aaa, I get it, thanks! just one more thing:

    some sites will have multiply entries, because they do not fit in one category. for this reason, one big table would be easier to handle for the editors.. got not much experience in dbs - is it possible to let some script create the multi-tabled indexing from the big table? if yes, how if not too complicated, if complicated, I ask someone to write it for me;-)

    thanks in advance,
    qwedsa

  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    If you've got links that fall into many categories, then you want something like this:

    Table Links:
    LinkID PK
    LinkDesc

    Table Categories:
    CategoryID PK
    CategoryDesc

    Table LinkCategories (make BOTH fields part of PK):
    LinkID FK to Links
    CategoryID FK to Categories
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •