Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2005

    Unanswered: Database design need any critic/feedback


    I would like some critic or feedback on the attach database design.

    The one that I am focusing on is : item_id in fr_item table.

    I would like to have a central place where from the web it will have id that map to file, posting and map.

    I have three tables fr_file, fr_post, fr_geo which map to fr_item.
    My question is :
    1. Is it good idea to have additional file_id, geo_id, post_id in fr_file, fr_post,fr_geo (as primary key) INSTEAD OF directly put item_id without file_id, geo_id or fr_file.

    Thank you in advances
    Attached Thumbnails Attached Thumbnails DatabaseDesign.jpg  

  2. #2
    Join Date
    Jun 2003
    Provided Answers: 1
    Please explain more clearly, and the image you posted seems to have cut off one of the tables your refer to.

    Are you familiar with the principles of data normalization? You should always start with these, and then break them only when necessary and with good reason.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

  3. #3
    Join Date
    Mar 2005
    Thanks for feedback. I am familiar with data normalization. The table which is cut off is not that important in the question that I refering too.

    Usually when I normalize table, it is a trade off, as you mention "to break them only if nescessary".
    For example:
    1. I have table fr_item and fr_item_cat.
    fr_item_cat basically just taking the category out of fr_item, with reason
    a. Performances. It is faster to select fr_item.item_cat_id with integer, instead of select comparing it with nvarchar.
    b. Data Integrity

    Here is another shot on the purpose. Let's say you want to make a site like "Flickr", instead of posting only picture, you are able to post videos, audios, etc. You are able to have comments, etc

    Now the "fr_item" is the central id that refering to every things in the site. whether is videos, audios, comments, posting.(fr_item_cat can have value of "posting", "comment","file"). The reason why to choose this layout. It will enable a central id refering to others table.

    Now the problems with this method,
    a. every insert statement to other table like (for example: fr_file required an insert to fr_item).
    b. every update to fr_file might also need to write sql statement update to fr_item.

    hopefully, it is more clear

Posting Permissions

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