Database Model

Pimcore tries to keep a clean and optimized database model for managing the data. Nevertheless, there are quite a lot of tables around and finding the correct data might be a bit challenging at the beginning.

Basically, there are two types of tables

• Default tables which are created during install - for all sorts of data like assets, documents logs, versions, ...
• Dynamically created tables which are created during object data model configuration - mainly for all object related data.

Default Tables

These tables are created during Pimcore install and are always the same.

Table	Description
application_logs	Contains all application logs. For more information see Application Logger. Additionally, there might be application_logs_archive* tables for old logging entries.
assets	Assets (Images, etc.), with system metadata
assets_metadata	Additional user metadata (Metadata tab in the asset panel)
classes	List of all object classes with ID and name. Everything else is stored in PHP configuration files.
classificationstore_collectionrelations	Relation Collection - Group for Classification Store
classificationstore_collections	Collections of Classification Store
classificationstore_groups	Groups of Classification Store
classificationstore_keys	Keys of Classification Store
classificationstore_relations	Relation Group - Key for Classification Store
classificationstore_stores	Stores of Classification Store
custom_layouts	Definition of the custom layouts for object classes
dependencies	Stores dependencies between elements such as objects, assets, documents
documents	List of all documents, folders, links, hardlinks, emails and snippets of the document area with meta- and config-data, relations
documents_editables	Editables of documents (data), in a serialized form
documents_email	Extra config data
documents_hardlink	Extra config data
documents_link	Extra config data
documents_page	Extra config data
documents_printpage	Extra config data
documents_snippet	Extra config data
documents_translations	Relation between same documents for different languages
edit_lock	Tracks which user opened which resource in the backend
element_workflow_state	Keeps track of workflow state for all elements
email_blocklist	Blocklist for eMail-addresses
email_log	Log for sent emails
glossary	Words to auto-link in texts. See Glossary
http_error_log	HTTP error log
lock_keys	Store for Locking functionality
notes	Notes for elements
notes_data	Additional data for notes
objects	List of all objects with metadata like id, class name, path, parent, ...
object_url_slugs	URL Slug data
properties	Data from the properties tab
quantityvalue_units	Available quantities for quantity value object data type
recyclebin	Stores metadata of deleted elements
redirects	Stores redirects
schedule_tasks	Stores scheduled tasks
search_backend_data	Stores the index for the backend search - is a InnoDb Table with fulltext capabilities
sites	Stores sites
tags	Stores available tags
tags_assignment	Stores assignment of tags to elements
tmp_store	Pimcore internal tmp store
tracking_events
translations_admin	Backend translations
translations_messages	Frontend translations
tree_locks	Locks in the tree of Pimcore backend interface
users	Backend users
users_permission_definitions	List of globally assignable user permissions
users_workspaces_asset	Stores user access permissions for asset folder
users_workspaces_document	Stores user access permissions for document folders
users_workspaces_object	Stores user access permissions for object folders
uuids	stores Unique Identifiers - if enabled
versions	List of object/asset/document versions. Actual data is serialized and written to disk

Object Tables

These tables are created and modified dynamically during the configuration of the object data model. As a result, they look different on an every Pimcore installation depending on the data model.

Objects

As soon as a new object class is created in Pimcore, at least three tables are added to the database. The tables have a numerical suffix, denoting the number (id) of the object class: object_query_(id), object_relations_(id), object_store_(id) and an additional database view object_(id) which is a combination of object_query_(id) and objects.

Table / View	Description
object_(id) View	Database view joining object_query_(id) and objects table
object_query_(id) Table	Use this table to retrieve data incl. inherited data. Data types with relations are usually stored in a serialized form here, too. Pimcore Object-Lists work with this table.
object_relations_(id) Table	Contains data of fields with relations to objects, assets, etc.
object_store_(id) Table	This is the main data storage table of an object class. It contains all "flat" data without any relations or external dependencies.
objects Table	Contains an entry for each and every object in the system. The id field is an auto_increment and the source of the primary key for an object. Metadata about an object is stored in this table, too.

When restore of query tables is necessary (for what ever reason) calling DataObject\Concrete::disableDirtyDetection(); and saving all data objects of class will do the trick. When not disabling dirty detection, there might be data missing in query table.

Simple Data Field Types

Following is an overview of how different object data types are stored in the database. This overview might not be complete. This overview might be a useful starting point when querying object data with object lists.

Text

Table: object_store_(id)

Name	Data Type	Default	Comment
Input	varchar(255)	NULL	/
Textarea	longtext	NULL	/
wysiwyg	longtext	NULL	Text with HTML-tags
password	varchar(255)	NULL	Password - as hash

Number

Table: object_store_(id)

Name	Data Type	Default	Comment
Number	double/decimal(64,3)	NULL	Datatype depends on selected precision
Slider	double	NULL	/

Date

Table: object_store_(id)

Name	Data Type	Default	Comment
Date	bigint(20)	NULL	< 1970 = negative Timestamp
Date & Time	bigint(20)	NULL	< 1970 = negative Timestamp
Time	varchar(5)	NULL	String - e.g.: "12:00"

Select

Table: object_store_(id)

Name	Data Type	Default	Comment
Select	varchar(255)	NULL	Selected value
User	varchar(255)	NULL	Pimcore User-ID
Country	varchar(255)	NULL	Country code
Language	varchar(255)	NULL	Language code
Multiselection	text	NULL	String, selected values, separated by ","
Countries (Multiselect)	text	NULL	String, selected language-codes, separated by ","
Languages (Multiselect)	text	NULL	String, selected language-codes, separated by ","

Relations

Table: object_relations_(id) & object_meta_data_(id)

• Data fields of relation types are stored in extra tables
• Data fields are not stored in distinct columns, but as rows, whereas the field name is in an extra column fieldname
• The column type specifies the type of the linked resource (Object, Document, Asset)
• The columns src_id and dest_id define the relation / the link between the objects.
• Column index is used to specify the order of the relations
• Columns ownertype, ownername and position are used when relations are within field collections, localized fields, object bricks, etc.
• The data type Advanced Many-To-One Object Relation stores the extra data in a table object_meta_data_(id) - the column column specifies the name of the meta item and data stores the value

Structured

Name	Comment
Table	Table data is stored as a string - serialized.
Structured Table	Each table cell is stored distinctively; schema: (fieldname)__(row key)#(column key)
Field-Collections	see special data fields later
Objectbricks	see special data fields later
Localized Fields	see special data fields later

Geographic

Table: object_store_(id)

Name	Data Type	Default	Comment
Geographic Point	double	NULL	Creates two columns: ‘(name)__longitude’ and ‘(name)__latitude’
Geographic Bounds	double	NULL	Creates four columns: ‘(name)__NElongitude’, ‘(name)__NElatitude’, ‘(name)__SWlongitude’ und ‘(name)__SWlatitude’
Geographic Polygon	longtext	NULL	Serialized geo-data

Other

Name	Data Type	Default	Comment
Image	int(11)	NULL	ID of the image asset
Image Advanced	int(11), text	NULL	Creates a column (name)__image(int) for the image assets id and the column (name)__hotspots(text). Hotspots are stored serialized.
Video	text	NULL	Serialized data
Checkbox	tinyint(1)	NULL	Boolean value (1 = true)
Link	text	NULL	Serialized data

Special Data Fields

Objectbricks

Table/View	Purpose
object_brick_query_(id) Table	Analog to object_query_(id)
object_brick_store_(id) Table	Main data storage

Localized fields

Table/View	Purpose
object_localized_(id)_(language-code) View	A database view per language, combining regular and localized data fields
object_localized_data_(id) Table	Stores localized field data
object_localized_query_(id)_(language-code) Table	Analog to object_query_(id)

Field Collections

Table/View	Purpose
object_collection_(collection-name)_(object-id)	Stores data of the field collections fields and the order (index)