Database Table Overview
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
The details describing a user are, shockingly, held in Database Table: user.
There are also many other tables starting with user. Some of them map a user onto something else. 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). Some of them map extra information onto user (e.g. userbio, which just maps a bio field onto a userid).
Some join with each other and user and come in the form user*prop and user*proplist. All these do is define properties for something, (in the user*proplist table) and the user*prop table defines a value for that property for each user.
userprop and userproplist 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.
Posts
Posts are held in the unintuitively named tables log2 and log2text. 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.
