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.
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".
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.