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.
[dbo].[cmsContent]
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.
[dbo].[cmsContentVersion]
Lists a version of each contentId and a versionId (guid) and the date it was created. On dev these can be cleared down.
[dbo].[cmsPropertyData]
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.
[dbo].[umbracoNode]
Stores the heirarchy of DocumentTypes, MediaTypes, Documents and Templates
[dbo].[cmsTab]
Contains the configuration for the document tabs shown to the content editor
[dbo].[cmsTemplate]
Contains a DB reference to the on disk template/layout (also the contents of that template / view which isnt used at runtime)
[dbo].[cmsDocumentType]
Defines the Templates that can be used for each DocumentType.
[dbo].[cmsContentType]
A list of the content types (doctypes), their alias, icon and master doctype
[dbo].[cmsContentTypeAllowedContentType]
A 1<->1 list of content type ids (doctypes). To determine what type can be created under which parent type
[dbo].[cmsMacro]
Sefines an Umbraco macro
[dbo].[cmsMacroProperty]
List of properties on macros
[dbo].[cmsMacroPropertyType]
For properties on macros this defnes the data type for the property
[dbo].[cmsPropertyType]
Contains a list of properties for each ContentTypeId (doctype). Includes helptext, description, alias etc
[dbo].[umbracoDomains]
Contains a list of host headers and the link to the starting nodeId
[dbo].[umbracoLanguage]
Contains the active language
[dbo].[cmsDataType]
Contains the lists of datatypes
[dbo].[cmsDataTypePreValues]
Contains prevalues for the datatypes (i.e MNTP definition)
