Not really sure that is more normalized, but it might be a tad clearer. I would say the increase in complexity isn't worth the (small) increase in clarity though. If you want to attach additional metadata to a change (read: comment) you can just as easily store it in the existing table I would think.
--Noah On May 2, 2010, at 8:33 PM, Josh Godsiff wrote: > Oh sorry, I should clarify - I wasn't really looking to commit these back > into the trunk unless there was some major call for me to do so. This is an > internal thing for my company. > > The reason I want to do this is because I want to attach information > specifically to a comment, as opposed to the ticket itself. The other benefit > is this kind of structure is more normalised. I'm looking for feedback on > what the current rationale for the way comments are stored is, and whether or > not this conflicts. > > Also, just realised a couple of problems with the previous schema I suggested. > > CREATE TABLE ticket_revision ( > ticket integer, > revision integer, > time integer, > author text, > comment text, > UNIQUE (ticket,revision) > ); > > CREATE TABLE ticket_change ( > revision integer, > field text, > oldvalue text, > newvalue text > UNIQUE (ticket,revision,field) > ); > > Thanks > - Josh > -- http://oxideinteractive.com.au/ > > > On 3/5/2010 12:54 PM, Noah Kantrowitz wrote: >> How is this in any way more useful than the current DB layout? Just look for >> field='comment' and it seems the same to me? I would say there will be a lot >> of resistance to changing an existing table in a backwards-incompatible way >> without a major use-case that is currently overlooked. >> >> --Noah >> >> On May 2, 2010, at 7:35 PM, Josh Godsiff wrote: >> >> >>> Hi folks >>> >>> I'm looking to do some things with comments which would be easiest if they >>> got normalised out of 'ticket_changes' into their own db table. >>> >>> What I'd propose as a DB schema is something like this: >>> >>> CREATE TABLE ticket_revision ( >>> ticket integer, >>> time integer, >>> author text, >>> comment text, >>> UNIQUE (ticket,time) >>> ); >>> >>> CREATE TABLE ticket_change ( >>> ticket integer, >>> revision integer, >>> field text, >>> oldvalue text, >>> newvalue text >>> UNIQUE (ticket,revision,field) >>> ); >>> >>> A couple of questions regarding this - firstly, would this be of any >>> benefit to the wider Trac community, or is there some particular rational >>> behind the current method of storing comments that would make this a not >>> terribly useful idea to others. >>> >>> Secondly, is it possible for Plugins to modify the schema of core DB >>> tables, or can they only create/edit their own? >>> >>> Thanks >>> - Josh >>> -- http://oxideinteractive.com.au/ >>> >>> -- >>> You received this message because you are subscribed to the Google Groups >>> "Trac Development" group. >>> To post to this group, send email to [email protected]. >>> To unsubscribe from this group, send email to >>> [email protected]. >>> For more options, visit this group at >>> http://groups.google.com/group/trac-dev?hl=en. >>> >>> >> > > -- > You received this message because you are subscribed to the Google Groups > "Trac Development" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/trac-dev?hl=en. > -- You received this message because you are subscribed to the Google Groups "Trac Development" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/trac-dev?hl=en.
