Normalization/Design Help with MySQL

[Nick_W]

Hi all,

OK, as may have noticed i'm building! ;)
Here's my latest hair pulling dillema:

I'm making a public access blog that will allow comments. I need to add a editlog table and am trying to work out if I should re-design a little or, how best to incorporate it. Here's what I have: (only fields needed to explain...)

blogtable:

• id
  
• authid
  
• date
  
• title
  
• desc
  
• text

Commenttable:

• id
  
• authid
  
• date
  
• text

Now, if I make an editlog table to contain authid, who edited and editnotes how do I tie it into both of the above tables efficiently?

I thought of just adding an emum field with ('c','p') for comment or post but, would I be better of redesigning the way the system works and putting the comments in the same table as the posts?

Anyone understand what I mean? ;)

Nick

[daisho]

Or have 2 edited tables 1 for blogs and 1 for comments. Enum will work fine and if you don't have to many records will be fine on speed. If you have lots of records (lots is a moving target that is based on your server/ram/activity volume) then 2 tables would be more efficiant.

[Nick_W]

That sounds sensible, thanks daisho.

So, you dont think combining the posts and comments is a good idea? - making the title and desc fields NULL so comments don't have them and adding a 'parent' field?

It really wouldn't be any extra work to make two tables and as the more complex qureies would be for the administrator only (like find out which admin has made which edits) it wouldn't really matter if those queries were'nt lightning fast...

Nick

[daisho]

A general rule in normalization is that you shouldn't have fields that you know will always be "NULL" for a certain type of record.

ie. Parent will always be null for a blog and title/desc will always be null for a comment. This indicates that you were correct in making them seperate tables.

[Nick_W]

>indicates that you were correct

<happy wilson!> - Must be on the right track then. Just threw me as 'un-ideal' when I came to think about editlogs.

Thanks again, much appreciated..

Nick

[DrDoc]

Well, it depends on how many posts/comments you will allow.. If only a few (or only "approved" ones) you can always add them in a separate column of the main table.

Otherwise, you can let the first column in the 2nd table auto increment, and add the #'s to the main table (column "comments", comma separated and updated when new comments are posted). That way it will be much faster and easier to grab the comments (from the second table) since you know exactly which ones to grab, and since that table will be larger than the other one. Each comment would have its own ID, which means one less column in that table (even though it will be one more in the main one).

[Nick_W]

Hmmm.. see the point about speed but it's a little messy I think? - here's the 2 main tables at present. I think i need to make 'parent' in the 2nd table an index...

[pre]
CREATE TABLE blogentries (
 id INT NOT NULL AUTO_INCREMENT,
 author INT NOT NULL,
 date TIMESTAMP NOT NULL,
 title VARCHAR(255) NOT NULL,
 description VARCHAR(255) NOT NULL,
 text TEXT NOT NULL,
 PRIMARY KEY(id)
)
CREATE TABLE blogcomments (
 id INT NOT NULL AUTO_INCREMENT,
 parent INT NOT NULL,
 author INT NOT NULL,
 date TIMESTAMP NOT NULL,
 text TEXT NOT NULL,
 PRIMARY KEY(id)
)[/pre]

Nick

[daisho]

Yes I would make parent an index. I would also suggest putting and index on the data and author fields since I'm sure they would be used in searches. Without an index the database would be forced to do a full table scan which is very inefficiant.