SitePoint Sponsor

User Tag List

Results 1 to 11 of 11

Thread: MySql db design

  1. #1
    SitePoint Wizard silver trophy TheOriginalH's Avatar
    Join Date
    Aug 2000
    Location
    Thailand
    Posts
    4,810
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    MySql db design

    Aaaargh - Dubois is at home, I'm all alone

    I need to create a system that will function thus:

    It will carry "articles" in pdf format (or pointers to the file ). Each article can be held in either a main category (where no sub categories exist), or a sub category.
    For example, an article on IT law may be held in:

    Main category: IT
    Sub category: Legal Issues

    It is my intention that as well at being able to search for articles, users can navigate from an index page which will display all main categories. When they select a category, a page with all subcategories is displayed, EXCEPT where no subcategories exist in which case all articles are displayed.

    Could use some advise on the best way of structuring this type of sytem in MySql.


    tia


    H
    ~The Artist Latterly Known as Crazy Hamster~
    922ee590a26bd62eb9b33cf2877a00df
    Currently delving into Django, GIT & CentOS

  2. #2
    SitePoint Zealot Paul_M's Avatar
    Join Date
    Mar 2001
    Location
    London
    Posts
    160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how deep / how many subcatagories are you likely to have?

  3. #3
    SitePoint Wizard silver trophy TheOriginalH's Avatar
    Join Date
    Aug 2000
    Location
    Thailand
    Posts
    4,810
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Literally just one main cat and multiple sub cats of that.

    IE:

    Main: Law

    Sub: IT Law, Land Law, Employment Law, Tort Law

    etc



    {edit}Two levels {/edit}
    Last edited by TheOriginalH; Aug 30, 2001 at 06:15.
    ~The Artist Latterly Known as Crazy Hamster~
    922ee590a26bd62eb9b33cf2877a00df
    Currently delving into Django, GIT & CentOS

  4. #4
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What you want to represent is a tree data structure. In fact you use the same database scheme to represent a two-level tree as a n-level tree. The top level nodes (the main categories) have no parent, whereas all other sub-nodes will have a parent. Also, the same schema can be used to represent a one-to-one relationship between a sub-category and its parent or a one-to-many mapping. That is, a sub-category may be related to only one main category or may be cross-referenced to many main categories. Either way, the tables below should do the job.

    Same thing with the articles. Does an article belong to only one category (sub or main) or many categories? Again the below will handle either case.
    Code:
    CREATE TABLE Category (
       cat_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
       name VARCHAR(250),
       parent_id INT UNSIGNED,
       PRIMARY KEY(cat_id, parent_id)
    );
    
    CREATE TABLE Article (
       art_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
       name VARCHAR(250),
       ... whatever else ...
    );
    
    CREATE TABLE Article_Category (
       art_id INT NOT NULL,
       cat_id INT NOT NULL,
       PRIMARY KEY(art_id, cat_id)
    );
    
    // insert a main category 'foo' (parent_id will default to NULL)
    INSERT INTO Category SET name='foo'
    
    // insert a sub-category 'bar' of main category parent = 'foo'
    INSERT INTO Category SET name='bar', parent='foo'
    
    // insert an article
    INSERT INTO Article SET ...
    
    // Relate article 123 to Category 345
    INSERT INTO Article_Category SET art_id=123, cat_id=345
    
    // find all Sub-categories of Category 'foo'
    SELECT * FROM Category WHERE parent_id='foo'
    
    // find all Articles associated with sub-category 'bar'
    SELECT * FROM Article_Category WHERE cat_id='bar'
    A bit of application logic
    PHP Code:
    // lookup category 'Zoot' and find its sub-categories

    $cat 'Zoot';

    $sql "SELECT cat_id FROM Category WHERE parent_id='$cat'";
    $result mysql_query($sql);

    // if there are one or more sub-categories print them out
    if ( mysql_num_rows($result) ) {
       
    //extract each row from the result set and print out "name"
       
    while ( $row mysql_fetch_array($result) {
          echo 
    $row["name"], '<br />';
       }
    // else if the no sub-cats then look up related articles and print them out   
    } else {
       
    $sql "SELECT Article.name AS artName
               FROM Article_Category, Article
               WHERE Article.art_id = Article_Category.art_id
               AND cat_id = '
    $cat'";
       
       
    $result mysql_query($sql);
       
    // if there are no articles print a message
       
    if ( ! mysql_num_rows($result) {
          echo 
    'sorry no articles';
       } else {
          while (
    $row mysql_fetch_array) {
             echo 
    $row["artName"], '<br>';
          }
       }

    that should be a bit of a start for you.
    Last edited by freakysid; Aug 30, 2001 at 06:30.

  5. #5
    SitePoint Wizard silver trophy TheOriginalH's Avatar
    Join Date
    Aug 2000
    Location
    Thailand
    Posts
    4,810
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Freakysid, you're a star!

    How did you go with the other stuff btw?

    H
    ~The Artist Latterly Known as Crazy Hamster~
    922ee590a26bd62eb9b33cf2877a00df
    Currently delving into Django, GIT & CentOS

  6. #6
    SitePoint Wizard silver trophy TheOriginalH's Avatar
    Join Date
    Aug 2000
    Location
    Thailand
    Posts
    4,810
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    hmmm - won't play. Will not allow me to create that first table as both parts of the primary key must be "not null". It suggests using Unique instead - but will that function ok?
    ~The Artist Latterly Known as Crazy Hamster~
    922ee590a26bd62eb9b33cf2877a00df
    Currently delving into Django, GIT & CentOS

  7. #7
    SitePoint Wizard silver trophy TheOriginalH's Avatar
    Join Date
    Aug 2000
    Location
    Thailand
    Posts
    4,810
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Do I actually need the "paired" primary key in the first table? Could I not just make cat_id primary?
    ~The Artist Latterly Known as Crazy Hamster~
    922ee590a26bd62eb9b33cf2877a00df
    Currently delving into Django, GIT & CentOS

  8. #8
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    CREATE TABLE Category ( 
       cat_id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
       name VARCHAR(250), 
       parent_id INT UNSIGNED, 
       PRIMARY KEY(cat_id, parent_id) 
    ); 
     
    CREATE TABLE Article ( 
       art_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
       name VARCHAR(250), 
       ... whatever else ... 
    ); 
     
    CREATE TABLE Article_Category ( 
       art_id INT NOT NULL, 
       cat_id INT NOT NULL, 
       PRIMARY KEY(art_id, cat_id) 
    );
    For your purposes, no you don't. If there is a one-to-many relationship (and NOT a many-to-many) between sub-category to parent-category then there can be only one record per category and each cat_id must be unique - so you are right, the primary key should just be cat_id.

    Well, I had designed the above as a generic solution that could handle many-to-many relationships. But I made a boo-boo! What can we say about the attribute "name" in my table Category? It is not fully dependent on the composite primary key! Category.name is fully dependent on Category.cat_id but is not dependent at all on parent_id. This can be understood immediately by just considering what the entries for a many to many relationship might look like
    Code:
    cat_id   name   parent_id
    ------   ----   ---------
    1        foo    NULL
    2        bar    NULL
    3        zoot   1
    3        zoot   2
    Here sub-category 'zoot' has two parent cateogories 'foo' and 'bar'. We can immediately see the undesirable data redundance caused by attribute 'name'. I did not follow the heuristic (which I usually do - I plead temporary insanity) of a many-to-many relationship requiring each entity to have a table and a seperate look-up table to hold the many-to-many relationship. So my design should have been two tables replacing Category like so:

    Category (cat-id, name)
    Category_Category(child-id, parent-id)

    But that is irrelevent to your question - just that I needed to fix my bad design! In your case because you are using a one-to-many relationship, the additional lookup table is not necessary. And, yes, in this case, then cat_id is a sufficient primary key as there can be one and only one record per cat_id. Phew!

    OK, same principles apply for the Category-to-Article relationship. Because you want a one-to-many relationship here (and don't need a many-to-many relationship) then there is no need for the seperate lookup table Article-Category! The one-to-many relationship can be represented by adding a foriegn key attribute to table Article like so:

    Article(art-id, name, cat-id)

    Sorry I lead you astray.

  9. #9
    SitePoint Wizard silver trophy TheOriginalH's Avatar
    Join Date
    Aug 2000
    Location
    Thailand
    Posts
    4,810
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Something else just occured to me ....

    Do I need the third table for this one-many relationship thang? Could I not just insert a catergory id into the article table? Or is that bad design?

    Thanks for the help btw
    ~The Artist Latterly Known as Crazy Hamster~
    922ee590a26bd62eb9b33cf2877a00df
    Currently delving into Django, GIT & CentOS

  10. #10
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes you are absolutely right. At the risk of repeating myself ...

    Originally posted by freakysid


    OK, same principles apply for the Category-to-Article relationship. Because you want a one-to-many relationship here (and don't need a many-to-many relationship) then there is no need for the seperate lookup table Article-Category! The one-to-many relationship can be represented by adding a foriegn key attribute to table Article like so:

    Article(art-id, name, cat-id)

    Sorry I lead you astray.

  11. #11
    SitePoint Wizard silver trophy TheOriginalH's Avatar
    Join Date
    Aug 2000
    Location
    Thailand
    Posts
    4,810
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Doh - sorry, fuzzy friday head
    ~The Artist Latterly Known as Crazy Hamster~
    922ee590a26bd62eb9b33cf2877a00df
    Currently delving into Django, GIT & CentOS


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
  •