---
title: Introduction
slug: db
createdAt: 2023-12-14T15:10:32.019Z
---

All Ticketmatic data is stored in a Postgres relational database. The tables in this database are not directly accessible. Ticketmatic instead provides a set of publicly available views on the database, called the `public data model`. The public data model gives you fine grained, low level access to the data stored in Ticketmatic. Access is limited to read-only. For higher level access or read/write access use [the Ticketmatic API](https://apps.ticketmatic.com/docs/api).

In order to retrieve data using the public data model, you use [SQL queries](http://en.wikipedia.org/wiki/SQL). The supported sql syntax strongly conforms to the [ANSI-SQL:2008 standard](http://en.wikipedia.org/wiki/SQL:2008).

:::hint{type="success"}
Using the public data model you can:

- create custom Filters
- create custom Field definitions to be used in Views, Ticket layouts or Documents
- create custom Reports
- use the console to perform ad-hoc queries
- perform ad-hoc queries through the API by using the [tools/queries api endpoint](https://www.ticketmatic.com/docs/api/tools/queries/)
:::



:::hint{type="warning"}
The views in the public data model are contained in the namespace `tm`, so you should use this as prefix to reference a view in the public data model, for example `tm.pricetype`.
:::



***

## Structure

The public data model is roughly structured in 4 parts:

- events, tickets and prices
- orders and saleslog
- contacts
- settings

### Events, tickets and prices

This part contains the information on the defined events, tickets and pricing. The most important views are:

- [tm.event](docId\:LZR3vbnf596zjjB69XLLB)
- [tm.tickettype](docId\:cd8xjLoJJt5o0drcPbNYA)
- [tm.ticket](docId\:Ng_G3ImT5NlU1CH0cq7BG)
- [tm.pricetype](docId\:d0zOcwtsNZNwvNDp0ybfX)
- [tm.pricelist](docId:7lxHTZdDwnza74oorm3Hd)
- [tm.tickettypeprice](docId\:RSAx5uGwV3f7IjEGSonor)

### Orders and saleslog

This part contains the information on actual orders and sold tickets. The most important views are:

- [tm.order](docId:_jxcUBXabuuSuHKqCpEaN)
- [tm.orderlog](docId\:eAEL-oEk_HPbdmidRjm5u)
- [tm.payment](docId\:YKfyakqenmldUuJunMH-F)
- [tm.ticket](docId\:Ng_G3ImT5NlU1CH0cq7BG)
- [tm.saleslog](docId\:LQr90Y--R48izmXXvaUGm)
- [tm.saleslogitem](docId\:E-VijLzeDvy5PLKb53sDW)
- [tm.batchprint](docId\:JlXZmSPzAJ-Cnj0UvoEka)

### Contacts

This part contains information on the contacts. The most important views are:

- [tm.contact](docId\:uFIX35lrtF5E4t_ERzoMr)
- [tm.contactaddress](docId\:Z11mfILDJTTv2JSgo2ENT)

### Settings

This part contains information on the settings. Some important views are:

- [tm.paymentscenario](docId:7dbVCtOqsvqFzHSuCZp-m)
- [tm.saleschannel](docId\:pUqmzWY-Lt3Yr5oUGFnEO)

### Some examples

Below you will find some examples to get you started. Go to Settings -> Console to try out these examples for yourself.

To retrieve a list of all the Pricetypes available:

```sql
select * from tm.pricetype
```

To retrieve a list of the last 10 payments:

```sql
select 
    id, 
    paidts, 
    orderid, 
    amount 
from tm.payment 
order by id desc 
limit 10
```

You can join tables to combine info. For example to retrieve contact info for the last 10 orders:

```sql
select 
    tm.order.id, 
    contact.firstname, 
    contact.lastname
from tm.order
left join tm.contact on tm.order.contactid=contact.id
order by tm.order.id desc
limit 10
```

To make a summary for the number of orders per month for orders in 2014:

```sql
select 
    date_trunc('month',tm.order.createdts),
    count(*) 
from tm.order 
where tm.order.createdts>='2014-01-01' and tm.order.createdts<'2015-01-01'
group by date_trunc('month',tm.order.createdts)
order by date_trunc('month',tm.order.createdts)
```

To get a list of all contacts that created an order in 2014:

```sql
select 
    id,
    firstname,
    lastname 
from tm.contact
where id in (
    select distinct contactid from tm.order where tm.order.createdts>='2014-01-01' and tm.order.createdts<'2015-01-01'
)
order by lastname, firstname
```

***

## Multilanguage fields

Multilanguage fields have a different value for each language. The name for an event is an example of a multilanguage field. In the public data model you will always retrieve the value of these fields for all active languages. As a convention, we use `<fieldname><languagecode>` as name for the language-specific value for the field.

In an account with languages en and nl active, you can try out:

```sql
select id, nameen, namenl from ev.event
```

This will return both the english and the dutch translation of the name field for an event.

***

## More info

Consult the reference for each table for more detailed info on available fields and foreign keys.