Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2002
    Posts
    18

    Unanswered: Entity (ER) Diagram for a direcotry database

    Hello,

    I am designing a database a directory website in which each business (for example Burger King) is added under the proper category such as

    Food
    --Fast Food
    ----Burgers
    ------Burger King


    you can see that there three level category system here. Sometime there is 4 or 5 level subcategory system.

    I have attached a copy of my ER diagram.

    Thank you
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Congratulations on your intent to design a directory database. Do you have a question about your design, or did you just want to state your intent?

    -PatP

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by oozypal
    Sometime there is 4 or 5 level subcategory system.
    this may help you design the table (there is only one table for all categories/subcategories at all levels) -- Categories and Subcategories
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2002
    Posts
    18

    Talking

    Quote Originally Posted by Pat Phelan
    Congratulations on your intent to design a directory database. Do you have a question about your design, or did you just want to state your intent?

    -PatP
    ooops,

    I did not state my question.

    I need help in design the categories and subcategories table. r937 gave me a link which really nice. If you more info, please post it.

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I would have modelled the category/subcategory relationship the very same way ... a few years ago.

    Now I understand better why some say it is bad habit to have a FK point to a PK of the same table (self-referencing tables). It can make maintenance/manipulation problematic, once the table is filled with records.
    Imagine you want to do something simple like copying your srCategory records to another table with the very same structure. It should be a breeze. But it isn't. The DBMS will check for each inserted record if the FK constraints are met. That would mean you'd have to insert them in such a sequence that you only add records that point to a parent record that is already present. A quick solution could be to drop the FK constraint in the destination table, do the INSERTS, then create the FK constraint again: ugly.

    A better approach is to provide a junction-table srCategorySubcategory with two FK's to srCategory, ParentCategoryId and ChildCategoryId (PK or unique index defined on ChildCategoryId).
    Now, if you want to copy your srCategory records to another table with the very same structure, it is a breeze: first copy srCategory then the junction table srCategorySubcategory.

    That said, it doesn't occur that many. Of all the self-referencing tables in our database (not so many really), I have only experienced that maintenance problem once. But then it wasted a lot of my time. I don't model self-referencing tables any more.

    Seems you're using PowerDesigner. I love its clearness over UML.
    Last edited by Wim; 11-20-08 at 09:57.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Sep 2002
    Posts
    18
    Hello Wim, the total number of categories and subcategories are not more that 150 records. Do you thing it is still bad idea to use one table?

    Moreover, does the junction table works ok for categories and 5 level down subcategories?

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    Do you thing it is still bad idea to use one table?
    Eliminating the use of self-referencing tables is good practice. It makes maintenance afterwards easier. I didn't saw the relevance of this rule, until it bit me.
    Imagine populating your table the very first time: Your INSERT-statements will have to be submitted in the exact sequence so all referenced parents exist before you INSERT its children.

    Make the comparison: During development one must frequently populate (related) tables and afterwards delete them (not drop), you must do that it in a special sequence: making sure you INSERT in parent tables before inserting in child tables. And apply the reverse order when emptying the tables: make sure you DELETE from child tables before deleting the parent tables.

    The order of filling and emptying tables is one thing, if you also must apply that logic to individual INSERT statements, it really gets cumbersome. That's the whole point.
    Code:
    Moreover, does the junction table works ok for categories 
    and 5 level down subcategories?
    Yes, no problem. The difference between the original srCategory and srCategorySubcategory is not that big:
    Code:
    srCategory		srCategorySubcategory
    Category_ID		Category_ID
    SubCategory_ID		SubCategory_ID
    Category
    Category_thumbnail
    Category_URL
    Just make sure you have a unique constraint defined on srCategorySubcategory.Category_ID, so no category can have multiple parent-categories.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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