Difference between revisions of "Database Table Overview"

Revision as of 19:39, 6 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.

Database Relationships

Databases only store things in tables. However, most data as stored and handled (for example, as Dreamwidth does in Perl) comes in more complicated forms. These can often be broken down into simple tables with simple relationships mimicking the data structures used in code.

This guide lists some of the relationships between databases. Because this guide doesn't assume any knowledge of databases, I'm including a short description here as I use these terms further down.

One to One

(Also: HasOne)

Table A has a relationship to table B in that each entry in A has one and only one corresponding entry in B.

You can tell a one to one relationship because either table A has a field which references the primary key of table B, or table B's primary key is the same as table A (e.g. Database Table: userbio).

One to Many

(Also: BelongsToMany)

Table A has a relationship to table B such that many entries in B can refer to one entry in A.

You can tell a one to many relationship because table B has a field which references the primary key in table A which isn't a primary key in B.

Many to Many or Link Tables

(Also: HasAndBelongsToMany)

Table A has a relationship to table B such that many entries to A can map to many entries in B.

Unlike the other relationships, a many to many relationship requires a third table, C, to define the relationship. C is a link table and has a field containing a reference to a primary key in table A and a reference to a primary key in B.

Things are further complicated by the fact that link tables can also contain data about the relationship. For example, user_schools is a link table defining the many to many relationship between schools and users. It also has two extra fields, year started and year ended.

Tables by Concept

This section attempts to map the concepts you know (for example, from having used the site) with the database tables that represent them, to make finding things easy.

Users

Details about a user are held in the table Database Table: user and this forms the centre of how user information is stored about users.

There are many other tables prefixed by the phrase user, most of which (but not all) deal additionally with user information. We divide these into three types:

• One to one and one to many tables, which basically hold extra information about each user in the user table.
• Link tables linking users in the user table to other concepts (like schools in the schools table, for example).
• User Proplists (see #Proplists further down for a description of what a proplist tables are)

Also, there is a table dealing with linking users to users called wt_edges. This is described further under the friends concept.

Extra Information

The tables holding extra information on a user are:

• Database Table: userbio: One to one mapping of a user biography onto a user.
• Database Table: userlog: One to many mapping of user events (e.g. account create) onto a user, showing the time the event happened. This table logs user activity.

Link Tables

These tables link the concept of a user with other concepts.

• Database Table: user_schools: Maps a user onto a school (in the schools table) with the year of starting and the year of finishing.

User Proplists

Database Table: userproplist and Database Table: userproplite2 form a proplist pair for defining user properties (such as the name of a twitter account).

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.

Common Conventions

The database tables use some common forms across the different types of content and concept.

Proplists

Text Separation