Hi all,
Basically, I want to build a php and mySQL driven CMS, possibly for use with a couple projects but mostly as a learning experience.
I'm trying to spend as much time as possible planning before I dive in. I'm thinking about database structure at the moment.
I want to allow users to post movie clips to the video section (like youtube), post images (similar the to the video section and/or in galleries) and post blog posts.
Each post (of a video, image or blog) can be a response to a previous post and all posts can be commented on and rated.
My first hurdle is regarding the structure for Posts, Responses, Comments and Ratings.
I could have separate tables for each type of Post (Blog, Video, Image, etc). Each Post record would have a ResponsePost (an ID) and a ResponseType (Blog, Video, Image, etc). Each Comment and Rating would have the same two fields. This was my very first thought but I really don't think it's a clever way of doing it.
Alternatively, one table for all Posts, each record has a PostType (Blog, Video, Image, etc) and a ResponsePost (an ID). Each Comment and Rating just refers to a PostID. This seems easier except that this one Post table has to have fields for videos, blog posts and images and for each record, 2 or these will be redundant.
I'm sure there are many other ways to do it - too many to list. Any suggestions about how to do this elegantly?
If there is some way to have the IDs for the Posts in the format: Vid0001, Vid0002, etc for Videos, Img0001, etc for Images and so-on, this would eliminate the need for the ResponseType fields while not creating the redundancy in the 2nd option.
Another small question I have is: Is it worth having a UserID if the Username is unique? Referencing the UserID should take up less space than always referencing the Username, right?
Lastly (for now!
, ) I'd like to allow users to both upload and embed videos. Is it going to be a bad idea to just have the one table and store either a URL or local path (depending on if they embed or upload)? If so, what is the cleanest solution?
Thanks very much in advance. Any advice or links to good resources are really appreciated.
- CBG
