Database schema for a site like 43 Things?

September 7, 2005 1:19 PM

Can someone please help me understand how a database for a site like 43 Things (www.43things.com) works? There are users, tags for categories, user comments (for categories), and comments that can be associated with other comments. I understand that you would need a user table, a comments table, and a tags table but how do you create a schema for a site like this that works effectively? What kind of table structure is needed and how do the tables interrelate?
posted by jlstitt to computers & internet (11 comments total)

just tables for all the things, then link tables, to create the connections. the link tables would just contain (entity 1 id) (entity 2 id) on each row.
posted by mosch at 1:39 PM on September 7, 2005

Using 43things as an e.g. and how I'd do it (minimally):
Table 1 -- thing_doing
fields: id, description

Table 2 -- users_doing
fields: id (thing_doing.id), userid

Table 3 -- user_ids
fields: userid, locale, other stuf

Table 4 -- progress
fields: id (things_doing.id), userid, progress id, comment_text

Table 5 -- comments
fields: progress id, comment_text

table 6 -- tags
fields: id (things_doing.id), tag text

If you look at the URLs generated you can pretty well suss out what they are using for ID fields and those are the lynchpin for interconnectivity.
posted by Ogre Lawless at 1:40 PM on September 7, 2005

Hmm, I still don't see how, using that method, the comments could belong to other comments...

table 5 -- comments
fields:

progress_id (if it belongs to a progress id??),

comment_text,

comment_id (if it belongs to another comment in a tree??)

Basically the relationship is like:

user has many comments
user has many tags

tags have many comments

comments have many comments

I still don't see how to tie it together in a table structure :(
posted by jlstitt at 1:46 PM on September 7, 2005

The comments table can have a self-referencing link. Eg: Comments.Id, and Comments.parentid

Every comment can be linked to another one. If you want to have multiple links, you just need another table. eg:

CommentLink.Link1 and CommentLink.Link2 where Link1 and Link2 are CommentIds.
posted by blue_beetle at 1:56 PM on September 7, 2005

blue_beetle, so you mean that tags can't have comments, or rather that they would have a separate comment table (again, with a parentid?)

If you have a comment.link1 and a comment.link2, they are associated only with other comments and cannot be associated with a tag, too?

On the site, a person can leave a comment about a tag ("goal") that he or she is trying to accomplish. Someone can then post another comment against that comment.

So, the parent has a tag as an association, and the child has a comment as an association... further children would have a comments tied.

Add to that... the user is tied to comments, and tied to tags ("goals")...

So does that mean:

table user (id, name, etc)
table comment (id, text, user_id, comment_id, tag_id)
table tag (id, text)

Or am I way off again?
posted by jlstitt at 2:03 PM on September 7, 2005

Not sure about tags, but it seems like comments can be pretty much attached to anything, so I might go for a more abstract "comment relationship" table:

idCommentRelationship (unique id)
idComment (self-explanatory)
idRelatedObject (id of whatever you're linking to- tag, comment, user, etc.)
idCommentRelationshipType (numbered id for each kind of "thing" you're linking to)

You'd also want a commentRelationshipType table for keeping track of types of relationships.

There's a trade-off in that you won't get a clean ER diagram out of it, but it saves you from having to manage individual tables for each kind of relationship.
posted by mkultra at 2:24 PM on September 7, 2005

so you mean that tags can't have comments, or rather that they would have a separate comment table (again, with a parentid?)

Stop trying to blow your own mind by making the problem harder than it needs to be.

|-------------------------------------|
|table comments                       |
|-------------------------------------|
|comment_id (unique, primary key)     |
|parent_id                            |
|parent_type (is of "comments","tags")|
|comment_text                         |
|-------------------------------------|

You could tart this up with date or times or IP addresses or whatever.
when displaying comments attached to a tag, you'd retrieve data with something like :

SELECT comment_text 
FROM comments 
WHERE parent_id=@your_tag_id_here AND
      parent_type="tags"

when needing to display comments attached to comments:

SELECT comment_text 
FROM comments 
WHERE parent_id=@your_comment_id_here AND
      parent_type="comments"

you'd call the first one as a seed to some recursive function .
posted by boo_radley at 3:07 PM on September 7, 2005

For the tagging stuff you do a many-to-many relationship with a join table.

So you'd have a comments (or posts, or 'things') table with an id per row. A tags table with tags and ids. Then a comments_tags table with comment_id, tag_id as primary key.

To find all tags for a certain 'thing':

SELECT tags.name FROM things, tags, things_tags tt WHERE things.id=? AND tt.thing_id=things.id AND tags.id = tt.tag_id;

To find all 'things' for a certain tag:

SELECT things.* FROM things, tags, things_tags tt WHERE tags.id=? AND tt.thing_id=things.id AND tt.tag_id = tags.id;

To find all 'things' which have a number of specific tags (that is, a tag 'union'):

SELECT things.* FROM things_tags tt, things, tags WHERE tt.tag_id = t.id AND (t.name = ? OR t.name = ? OR t.name = ? ?AND SO ON?) AND things.id=tt.thing_id GROUP BY things.id HAVING COUNT(things.id) = ?NUMBEROFTAGSINUNIONHERE?

And so forth.
posted by wackybrit at 4:07 PM on September 7, 2005

For the most flexibility, you might want to have a comment that's associated with multiple resources (eg a comment on a blog post and a user). This is just a link table where it's (comment.id, resource.id). That way a comment can be associated with any N resources. The same works for tags (tag.id, resource.id). Your resource table just looks like (resource.id, resource.type) and everything gets inserted into the resource table--user, tag, comment, page, post etc.
posted by nixerman at 4:31 PM on September 7, 2005

An Introduction to Database Design might be a good resource for educating yourself about how this can and should be done.
posted by majick at 11:20 PM on September 7, 2005

Thanks very much everyone, that helped a lot! :)
posted by jlstitt at 6:04 AM on September 8, 2005