Results 1 to 12 of 12
  1. #1
    Join Date
    May 2009
    Posts
    12

    Hello everybody, Design question

    Hi this is my first post in this forum, I have one query regarding database design.

    I have 3 tables.

    1) Upload(UploadId Primary key)
    2) Category(CategoryId Primary Key)
    3) Subcategory(SubcategoryId primary key)

    relation among in them is...

    Category (1) : (Many) Upload

    Category(1) : (Many) Subcategory

    My question is how should I design database and foreign key contraints?

    I am eagerly waiting for your answers.

    Thnx.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you don't need separate category and subcategory tables

    this might help: Categories and Subcategories
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2009
    Posts
    12
    Thank You r937,

    I didn't know this technique before.

    Much Appreciated.

  4. #4
    Join Date
    May 2009
    Posts
    12

    Can write like this?

    Instead of adjacency model recommanded by r937,
    can i write like following?

    tblUpload
    -----------------
    UploadId(PK)
    .................................................. .....................................

    tblCategory
    -----------
    CategoryId(PK)
    UploadId(FK)
    .................................................. .....................................

    tblSubCategory
    ---------------
    SubcategoryId(PK)
    CategoryId(FK)
    UploadId(FK)
    .................................................. .....................................

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Why does UploadId appear in the Category table? It looks like you can determine the Category from the SubCategory table anyway. So I'd expect you can remove UploadId from Category. Of course, I don't know your data.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The adjacency model lets you have subcategories of subcategories, and subcategories of those, and subcategories of those, and subcategories of those, and subcategories of those, and subcategories of those, and subcategories of those, and subcategories of those, and subcategories of those, and subcategories of those, and subcategories of those, and subcategories of those (etc)

    Yours allows only two layers deep. Is this what you require?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    May 2009
    Posts
    12
    Yours allows only two layers deep. Is this what you require?
    Yes I want only 2 levels deep.

  8. #8
    Join Date
    May 2009
    Posts
    12
    Why does UploadId appear in the Category table? It looks like you can determine the Category from the SubCategory table anyway. So I'd expect you can remove UploadId from Category. Of course, I don't know your data.
    CORRECTED ONE:->

    tblUpload
    -----------------
    UploadId(PK)
    .................................................. .....................................

    tblCategory
    -----------
    CategoryId(PK)
    UploadId(FK)
    .................................................. .....................................

    tblSubCategory
    ---------------
    SubcategoryId(PK)
    CategoryId(FK)
    .................................................. .....................................

  9. #9
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    This doesn't look right either. If it is correct then each CategoryId can only have ONE upload, which appears to contradict what you said before: "Category (1) : (Many) Upload". My guess is that either CategoryId or SubCategoryId actually belongs in the Upload table and that UploadId should not go in the Category table.

    Unfortunately these kinds of design-by-forum exercises tend to make very slow progress and are rarely much help in my experience. The best option may be to hire someone to assist you if you aren't familiar with how to model a database.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Modelling a substantial business problem, yes. Something like this - nah. This and other forums have helped sort out thousands and thousands of problems of this sort. If they aren't of any use then why do you continue to contribute?

    Kalundra - the principle problem is you have not explained in detail what you are trying to achieve, what you are modelling, what are the real world circumstances. You keep posting models, but we don't know if they are appropriate or not unless you tell us the background.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    May 2009
    Posts
    12
    Kalundra - the principle problem is you have not explained in detail what you are trying to achieve, what you are modelling, what are the real world circumstances. You keep posting models, but we don't know if they are appropriate or not unless you tell us the background.
    I am developing community based website where people can contribute their images, documents, audios and videos.
    client's requirement specification tells that all of those uploads have categories and subcategories.(no subsubcategories). After thinking lots of alternate solutions I have come across following design..

    Upload belong to particular category or subcategory. Categories have subcategories or they don't have any subcategory. In that case it may be NULL.

    Category
    ------------
    CategoryId(PK)
    CategoryName

    Subcategory
    ------------
    SubcategoryId(PK)
    CategoryId(FK) ref Category(CategoryId)
    SubcategoryName

    Upload
    -------------
    UploadId(PK)
    CategoryId(FK) ref Category (CategoryId)
    SubcategoryId(FK) ref Subcategory (SubcategoryId)
    UploadName
    ....

    really want to know your point of view..
    ===========================================
    Last edited by kalrudra; 05-07-09 at 13:06.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kalrudra
    really want to know your point of view..
    Category
    ------------
    CategoryId(PK)
    CategoryName
    ParentCat(FK) references Category (CategoryId)

    Upload
    -------------
    UploadId(PK)
    UploadName
    CategoryId(FK) references Category (CategoryId)


    K.I.S.S.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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