Post and Comment Data Table(s)

[ghost]

I'm proposing some possible table structures to get us rolling. What does everyone think of this for post and comment data?

Post Table: id, author(user.id) title, body, reference, createtime Comment Table: id, author(user.id), title, body, post.id, parent.id, reference, createtime

The reference is to allow updates to happen by creating new data without overwriting and to retain historic data. I'm probably missing some things here. Any ideas?

[jarofghosts]

would be useful to outline how the reference system works

[robotmayo]

If I had to guess, it's just a pointer to the previous comment/post revision.

[jarofghosts]

so the only way to find the time the actual post was created is via a linked list of revisions?

[ghost]

@jarofghosts @robotmayo The reference would always be the same for a body of entries that were the same 'post'. So finding the actual post would be "select createtime from post where reference = x order by createtime desc limit 1" or something I think?

edit: select createtime

[jarofghosts]

so theres no way to know the original creation time?

[ghost]

the original creation time would be on the entry in the table for that reference with the earliest creation time. I don't guess I understand.

[jarofghosts]

ah sorry, derp makes sense

[jarofghosts]

+1

[ghost]

As a side note, I really dislike that these two tables are very much the same.

[jarofghosts]

they dont have to be... the alternative is .. maybe grosser?

[robotmayo]

For simplicity I think its best like this.