plastik77 Posted October 30, 2008 Share Posted October 30, 2008 Hi, I'm fairly newish to web development (about a year and a half or so) and am constantly trying to improve my coding & application design. I recently started coding in OOP and used a framework to build a CMS, using ORM to map objects to database tables. I am now looking back over my code and database design to see where I can make improvements and would be interested in some feedback in my database structure. Here is an overview of the db: PAGES: Menus, quicklinks options and submenus are generated by querying the pages table. I have used boolean flags in this table to discern whether a page should be shown on the quicklinks menu, or whether the page is a department (the CMS was built for a school, department pages are treated differently from other pages). There is a recursive relationship in this table, allowing me to check if a page belongs to a parent page (page_id) and this means i can generate submenus dynamically. DEPARTMENTS: Not convinced this is the best way to deal with this, but when I check the "department" boolean flag in the Pages table, I need to generate a dropdown list of Departments. I need to store the department names somewhere, so i created this table. CMSARTICLES: Pretty straightforward - page content which is assigned to a page. VISUALS: Almost every page on the site has a visual attached to it. This can either be a flash slideshow or an image. The visuals table stores the location of the image, has a boolean flag to let me know if it is Flash or an Image, allowing me to generate the correct html. NEWS/NEWS_IMAGES - Standard fare - news items which can have an image associated with it EVENTS/EVENTS_IMAGES - Same as news USERS - CMS user, is assigned to an admin role. ROLES - Contains the list of available admin roles OPTIONS - Contains the list of available editing privileges for each role OPTIONS_ROLES - join table for many to many relationship Anyway, I'm grateful to anyone who has taken the time to read this far. Here is my EER diagram. Many thanks! Quote Link to comment Share on other sites More sharing options...
Mchl Posted October 30, 2008 Share Posted October 30, 2008 Some questions I got. I concentrated mostly on column types. For boolean values use TINYINT. INT(5) is the same as INTEGER which is 4 bytes long, while TINYINT is just one byte. The number in () parentheses after INT does not define field width. It's for presentation purposes only. I'd change visuals.flash BOOL to visuals.type ENUM('image','flash','youtube',...) to have more flexibility in the future. Also 100 characters for an url might not be enough. And why limit alt text to 250? VARCHAR is cheap. Why users.password is VARCHAR(50)? How are you going to store passwords? Why news.story_date is VARCAHR(250)? If it's a date us proper column format (DATE, DATETIME, TIMESTAMP)... there are like three fields for date in this table... I don't get it. Could you explain? Are you sure user_tokens.user_agent VARCHAR(40) is enough? Quote Link to comment Share on other sites More sharing options...
plastik77 Posted October 30, 2008 Author Share Posted October 30, 2008 Thanks for the quick reply Mchl! Firstly, using TINYINT for boolean values makes much more sense. I'll update this. Regarding using ENUM for visuals.type, you're right this is a much better, more flexible approach. I wasn't very familiar with ENUM but having just checked out how it works, this is perfect for this instance. I use sha1 to hash passwords, so they will always be 32 characters long. Is VARCHAR appropriate for this? News.story_date is VARCHAR as I am allowing the user to enter a date manually. If there is a backlog of news stories, then they will not want all the stories to have the same timestamped date. So they enter a date in a certain format, the system validates it and then enters it in the db. I also convert the user entered date to UNIX format (converted_date), which made it easier for me to sort when retrieving news items and displaying in date order. This might be a pretty clumsy way to do it though, the more i think about it. The date_posted field is just the timestamp of when the story was actually created, for auditing purposes. Interested to hear if I could improve this section. Re user_tokens.user_agent, I will increase this to allow a bit more flexibility. Quote Link to comment Share on other sites More sharing options...
Mchl Posted October 30, 2008 Share Posted October 30, 2008 I use sha1 to hash passwords, so they will always be 32 characters long. Is VARCHAR appropriate for this? It is, but VARCHAR(32) not VARCHAR(50). Even if it will use only 32 bytes anyways, it's good to state implicitly that you don't expect anything above 32 bytes. And some say, that CHAR(32) would be the best, as it might be a bit faster. I haven't seen any evidence to this though. News.story_date is VARCHAR as I am allowing the user to enter a date manually. If there is a backlog of news stories, then they will not want all the stories to have the same timestamped date. So they enter a date in a certain format, the system validates it and then enters it in the db. I also convert the user entered date to UNIX format (converted_date), which made it easier for me to sort when retrieving news items and displaying in date order. This might be a pretty clumsy way to do it though, the more i think about it. The date_posted field is just the timestamp of when the story was actually created, for auditing purposes. Interested to hear if I could improve this section. Use some data-picker (example) so that users will pick the dates instead of entering them. You will still have to validate the data, but it will be easier for them. Save only converted_date. You can easily convert it back to human readable format using date() function. Actually, you should change converted_date to either DATETIME or TIMESTAMP, as it will enable you to use MySQLs datetime functions on them. Converting them back and forth between MySQL format, UNIX timestamp and human readable format is easy using date() and strtotime() functions. Quote Link to comment Share on other sites More sharing options...
plastik77 Posted October 30, 2008 Author Share Posted October 30, 2008 Thanks Michl. Good idea about the date picker, definitely more user friendly. I was a bit unfamiliar with date handling when i was working on this CMS, so that's why the approach is pretty clumsy. I'll have a look at php's date functions and get rid of varchar news.story_date. Appreciate the feedback! Quote Link to comment Share on other sites More sharing options...
NikkiLoveGod Posted November 12, 2008 Share Posted November 12, 2008 Hi! Sorry to get of topic here, but what software did you use to make that diagram? I am looking for a good software to do the diagrams needed in web development, thanks! -NikkiLoveGod Quote Link to comment Share on other sites More sharing options...
Mchl Posted November 12, 2008 Share Posted November 12, 2008 That looks like MySQL WorkBench 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.