so, i'm trying to figure out how this should work. i haven't done much with mysql lately, so i'm a bit rusty. right now, ttf stores posts in a myisam table like this:
`post_id` mediumint NOT NULL auto_increment, `thread_id` mediumint default NULL, `author_id` mediumint default NULL, `date` int default NULL, `ip` varchar default NULL, `body` longtext, PRIMARY KEY (`post_id`), KEY `thread_id` (`thread_id`), FULLTEXT KEY `body` (`body`)
so i could add a field `revision` smallint default 0. but then, when i'm pulling a thread with this:
SELECT ttf_post.post_id, ttf_post.author_id, ttf_post.date, ttf_post.body, ttf_user.username, ttf_user.title, ttf_user.avatar_type FROM ttf_post, ttf_user WHERE ttf_post.author_id = ttf_user.user_id && ttf_post.thread_id = '$thread_id' ORDER BY date ASC
i'd have no idea how to get the HEAD revision only (defined as the highest revision number). do i need another field that is something like `rev_head` enum('yes','no') default 'yes', so that i can just add "WHERE rev_head = 'yes'" at the end of the query?
alternatively, i could store all non-head revisions in a seperate table. this is less than optimal, however, because both tables will have the exact same fields. and one thing i learned about table design is that you should not split up similar data into multiple tables.
another possible solution is to make the revision numbers inverted. make revision 0 the HEAD revision, and keep pushing older versions back to higher (or lower) numbers. then, i can just do a "WHERE `revision`=0" to get the HEAD revision of every post in a thread.
thoughts?
`post_id` mediumint NOT NULL auto_increment, `thread_id` mediumint default NULL, `author_id` mediumint default NULL, `date` int default NULL, `ip` varchar default NULL, `body` longtext, PRIMARY KEY (`post_id`), KEY `thread_id` (`thread_id`), FULLTEXT KEY `body` (`body`)
so i could add a field `revision` smallint default 0. but then, when i'm pulling a thread with this:
SELECT ttf_post.post_id, ttf_post.author_id, ttf_post.date, ttf_post.body, ttf_user.username, ttf_user.title, ttf_user.avatar_type FROM ttf_post, ttf_user WHERE ttf_post.author_id = ttf_user.user_id && ttf_post.thread_id = '$thread_id' ORDER BY date ASC
i'd have no idea how to get the HEAD revision only (defined as the highest revision number). do i need another field that is something like `rev_head` enum('yes','no') default 'yes', so that i can just add "WHERE rev_head = 'yes'" at the end of the query?
alternatively, i could store all non-head revisions in a seperate table. this is less than optimal, however, because both tables will have the exact same fields. and one thing i learned about table design is that you should not split up similar data into multiple tables.
another possible solution is to make the revision numbers inverted. make revision 0 the HEAD revision, and keep pushing older versions back to higher (or lower) numbers. then, i can just do a "WHERE `revision`=0" to get the HEAD revision of every post in a thread.
thoughts?