Difference between revisions of "Database Table Overview"

Revision as of 06:54, 4 August 2009

Note: This page has been started by a complete newbie, who is trying to make sense of the database table structure as she goes and is writing the information down. As a result, the details may be inaccurate and so on. Hopefully, it's still better than nothing to the equally clueless! ;-) Aphenine 22:36, 28 July 2009 (UTC)

Introduction

All the information that is entered into Dreamwidth (like with most web apps), gets stored in a database. So understanding the table structure can help you understand how Dreamwidth is storing things. That's the theory, anyway. It also helps in debugging, extending Dreamwidth and so on. So it's good to know, even if you're not planning on altering the tables or the interfacing code directly.

This section provides an overview on the tables, what they do and how they map to what you see. Enough information so that you get up and running, anyway. That's the plan.

Tables by Concept

Users

Database Table: user

The details describing a user are, shockingly, held in Database Table: user.

There are also many other tables starting with user. Some of them define extra information, stuff that could have gone into user but didn't for whatever reason. In database lingo, they all belong to the user table. Database Table: userbio is an example.

The rest of the user* tables map an entry in the user table onto another table. These come in two types.

The first type maps a user onto another entry in a table. In database lingo, this is a Has And Belongs To Many relationship. For example, user_schools maps a user (via a userid) to a school in the school database table (via a schoolid), with some extra information about the join (year started and year ended).

The second type are user property tables, which come in pairs called user*proplist and user*prop. The user*proplist defines a list of properties that can be set. user*prop sets the value of the property per user.

Database Table: userprop and Database Table: userproplist are an example pair of the second kind and contain the common properties for a user. For example, userproplist defines the property twitter and tells you that this is the twitter user account name. If you enter a twitter account when setting up a user, this goes into userprop (which maps a userid onto a userpropid with a final value field setting the value).

Posts

Posts are held in the unintuitively named tables log2 and logtext2. logtext2 contains the title and body while log2 contains all the other details (like security settings).

Comments

Comments are held in two tables, Database Table: talk2 and Database Table: talktext2. The actual text is held in talktext2, and if you SELECT * FROM talktext2; in MySQL, you get to see all comments ever made in your DW server. Exciting or what?

talk2 holds the structure of the comment, along with who posted it. It has a field called parenttalkid, and any table with a column heading with the words parent and id in them implies that this table actually represents a tree. This is how, in the database, is represented the lovely threading you get with comments as they recursively indent down with each new reply until you can barely read. There are also two fields with node in them, which must play some role in how the tree is constructed, but I don't know how. Yet.

Tags

Tags are confusing because there appear to be two different systems.

In the first, the tags are provided by the userkeywords table, which maps a user to a keyword and a keyword ID. This allows every user to maintain their own list of tags.

In the second, tags are provided by two tables, the keywords table and the usertags table. The keywords table is simple, mapping a keyword ID to a keyword. The mapping to a user is then done through the usertags table, which maps a user to a single keyword. Excitingly, there is also a parent keyword ID field, which implies that tags can now be sorted in trees, which is awesome.