squiblo Posted January 8, 2012 Share Posted January 8, 2012 I have managed to figure out how to use MySQL Workbench EER and I have come up with a design. I'm not sure if it is done correctly, if it's not please let me know. If you think is can be optimized in anyway please let me know aswell. Download http://www.squiblo.com/DBModel.mwb **Download file and open with MySQL Workbench** Thanks Quote Link to comment Share on other sites More sharing options...
ngreenwood6 Posted January 8, 2012 Share Posted January 8, 2012 Ok so here is my opinion on it with a quick review of it without really looking too deeply into it. First of all I would add more normalization to the tables. For instance in the stories table you have two fields in there for totalThumbUp and totalThumbDown. I would probably separate them out into another table storing the user id that gave it a thumbs up/down. This will allow them to change there vote in the future and you will still be able to find those values when you need them which you may not always need them. Another thing that I would do is separate out the actual content for the stories. Because that field is storing a large amount of data it will make your queries slower and most of the time you will not need all of that information unless you are viewing the actual story (depending on your scenario). It also seems as if you have some fields in there that dont really need to be. For example in the stories table you have a field for totalViews but there is a table for StoryViews which seems to just be storing the views which you should just be able to count the views from that table if needed. One last thing is it seems as if you are storing the dates as timestamps since you are using int fields. Why not just store them as dates? It is alot easier to look through the data in the table and find articles within a specific date range. Also in the future if you decide to do horizontal partitioning (which dates work perfectly for, especially with "stories") you will be able to do them by the year that they were posted which will greatly improve the speed of your queries with alot of entries in the database. Quote Link to comment Share on other sites More sharing options...
squiblo Posted January 8, 2012 Author Share Posted January 8, 2012 Thank you ngreenwood6, It also seems as if you have some fields in there that dont really need to be. For example in the stories table you have a field for totalViews but there is a table for StoryViews which seems to just be storing the views which you should just be able to count the views from that table if needed. I have a reason for this, lets see if I can explain. When a user clicks thumbUp or thumbDown it will insert a row into "StoryLikes" (if they haven't previously) and will also increment the field "totalThumbUp/totalThumbDown" in the Stories table by 1. This will make it faster for selecting how many likes/dislikes a story has, instead of having to do a COUNT every time. I am sacrificing INSERT and UPDATE speeds for SELECTS speeds. I've tried my best to explain sorry if you don't understand. Thanks for the help! Quote Link to comment Share on other sites More sharing options...
Philip Posted January 8, 2012 Share Posted January 8, 2012 Personally, I'd make the things like "__By" linked to the user table via ID. You do this for Stories, but not for Likes, View, Comments, etc. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.