Basic database concepts in Odoo

To make the best use of Odoo Studio, you will need to have some understanding of relational databases. Fortunately it’s not difficult if you are familiar with any ERP system (or other similar applications)

A good way to start is by understanding the database in an existing Odoo application.

We’ll use Sales Orders as an example. A sales order consists of:

  1. Sales Order “header” with information such as:
    • Order Number
    • Customer
    • Order Date
  2. Sales Order Lines
    • Products or Services
    • Quantities
    • Prices

It should be fairly obvious that there will be a database relationship between the Sales Order “header” and the order lines, and also there must be a relationship between the order and the customer (who is a partner, so that information is on res.partner) .

In addition, we know that orders and invoices are related, but in a flexible way so that we don’t have to create one invoice for one order.

Fortunately, Odoo has three types of database relationship

  1. one2many
    • From Sales Order Header (sale.order) to Sales Order Lines (sale.order.line)
      • One sales order can have many sales order lines
  2. many2one is the inverse of one2many
    • From Sales Order Line (sale.order.line) to Sales Order (sale.order)
      • Many sales order lines for one sales order
    • From Sales Order Header (sale.order) to Customer (res.partner)
      • Many sales orders for one customer
  3. many2many
    • Between Sales Order Header (sale.order) and invoices (account.invoice):
      • A sales invoice can be for many sales orders AND
      • A sales order can have many invoices
        • e.g. you deliver 50% this week and 50% next week, and raise two invoices.

Note that Odoo does NOT have one2one database relationships.

Field types

For each type of database relationship there is a matching field type (still using the Sales Order Header model, sale.order)

  1. one2many
    • Order Lines | order_line | links to sale.order.line
  2. many2one
    • Customer | partner_id | links to res.partner
  3. many2many
    • Invoices | invoice_ids | links to account.invoice

Other field types are listed here

Sales Order Line

Similar to the link to a customer on the order header, there must be a link to products (or services) on the order line.

Note that the link to the product database is to product.product and NOT to product.template

These are some of the key database relationships:

  1. many2one
    • From Sales Order Line (sale.order.line) to Sales Order (sale.order)
      • Many sales order lines for one sales order
    • From Sales Order Line (sale.order.line) to Products (product.product)
  2. many2many
    • Between Sales Order Header (sale.order) and invoice lines (account.invoice.line):
      • A sales invoice can be for many sales order lines AND
      • A sales order line can have many invoices
        • e.g. you deliver 50% this week and 50% next week, and raise two invoices.

3 thoughts on “Basic database concepts in Odoo

  1. Great work thanks. Maybe adding Create Table SQL statements would be beneficial to understanding what is going on in the three types of database relationships. Your explanation is clear but at the end of the day we are still talking about a SQL database.

    Like

    1. Remember that this site is not intended for use by programmers (there are plenty of other places to find more technical information about Odoo).

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s