I was having a look through the Umbraco 4.9 database a while ago and made some notes, thought I’d share them here. It has changed for V6.
Relates content nodeIds to contenttypeIds (doctypes) (So tells each content item to which doctype they belong) Since both Documents and DocumentTypes exist within umbracoNode, this table enables an association between them.
Lists a version of each contentId and a versionId (guid) and the date it was created. On dev these can be cleared down.
The table contains the value of every property of each version of each content node. The editors data is saved in one of dataint, dataDate, dataNVarchar or dataNText depending on the field editor type.
Stores the heirarchy of DocumentTypes, MediaTypes, Documents and Templates
Contains the configuration for the document tabs shown to the content editor
Contains a DB reference to the on disk template/layout (also the contents of that template / view which isnt used at runtime)
Defines the Templates that can be used for each DocumentType.
A list of the content types (doctypes), their alias, icon and master doctype
A 1<->1 list of content type ids (doctypes). To determine what type can be created under which parent type
Sefines an Umbraco macro
List of properties on macros
For properties on macros this defnes the data type for the property
Contains a list of properties for each ContentTypeId (doctype). Includes helptext, description, alias etc
Contains a list of host headers and the link to the starting nodeId
Contains the active language
Contains the lists of datatypes
Contains prevalues for the datatypes (i.e MNTP definition)