> ## Documentation Index
> Fetch the complete documentation index at: https://cubed3-feat-druid-driver-streaming.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Polymorphic cubes

> Models one shared fact or dimension table that represents multiple entity types by extending a base cube into type-specific cubes with correct joins and logic.

In programming languages, polymorphism usually means the use of a single symbol
to represent multiple different types. It can be quite common for a database and
application to be designed in such a way that leverages a single database table
for entities of different types that share common traits.

For example, you are working on an online education platform, where teachers
assign lessons to students. The database can contain only two tables: one for
`users` and another one for `lessons`. The `users` table can contain a `type`
column, with possible values `teacher` or `student`. Here is how it could look:

| **id** | **type** | **name**       | **school**         |
| ------ | -------- | -------------- | ------------------ |
| 1      | student  | Carl Anderson  | Balboa High School |
| 2      | student  | Luke Skywalker | Balboa High School |
| 31     | teacher  | John Doe       | Balboa High School |

Lessons are assigned by teachers and completed by students. The `lessons` table
has both `teacher_id` and `student_id`, which are actually references to the
`user id`. The `lessons` table can look like this:

| **id** | **teacher\_id** | **student\_id** | **name**                                      |
| ------ | --------------- | --------------- | --------------------------------------------- |
| 100    | 31              | 1               | Multiplication and the meaning of the Factors |
| 101    | 31              | 2               | Division as an Unknown Factor Problem         |

The best way to design such a data model is by using what we call **Polymorphic
Cubes**. It relies on the [`extends`][ref-schema-ref-cubes-extends] feature and
prevents you from duplicating code, while preserving the correct domain logic.
Learn more about using [`extends` here][ref-schema-advanced-extend].

The first step is to create a `user` cube, which will act as a base cube for our
`teachers` and `students` cubes and will contain all common measures and
dimensions:

<CodeGroup>
  ```yaml title="YAML" theme={null}
  cubes:
    - name: users
      sql: SELECT * FROM users

      measures:
        - name: count
          type: count

      dimensions:
        - name: name
          sql: name
          type: string

        - name: school
          sql: school
          type: string
  ```

  ```javascript title="JavaScript" theme={null}
  cube(`users`, {
    sql: `SELECT * FROM users`,

    measures: {
      count: {
        type: `count`
      }
    },

    dimensions: {
      name: {
        sql: `name`,
        type: `string`
      },

      school: {
        sql: `school`,
        type: `string`
      }
    }
  })
  ```
</CodeGroup>

Then you can derive the `teachers` and `students` cubes from `users`:

<CodeGroup>
  ```yaml title="YAML" theme={null}
  cubes:
    - name: teachers
      extends: users
      sql: |
        SELECT *
        FROM {users.sql()}
        WHERE type = 'teacher'

    - name: students
      extends: users
      sql: |
        SELECT *
        FROM {users.sql()}
        WHERE type = 'student'
  ```

  ```javascript title="JavaScript" theme={null}
  cube(`teachers`, {
    extends: users,
    sql: `
      SELECT *
      FROM ${users.sql()}
      WHERE type = 'teacher'
    `
  })

  cube(`students`, {
    extends: users,
    sql: `
      SELECT *
      FROM ${users.sql()}
      WHERE type = 'student'
    `
  })
  ```
</CodeGroup>

Once we have those cubes, we can define correct joins from the `lessons` cube:

<CodeGroup>
  ```yaml title="YAML" theme={null}
  cubes:
    - name: lessons
      sql_table: lessons

      joins:
        - name: students
          relationship: many_to_one
          sql: "{CUBE}.student_id = {students.id}"

        - name: teachers
          relationship: many_to_one
          sql: "{CUBE}.teacher_id = {teachers.id}"
  ```

  ```javascript title="JavaScript" theme={null}
  cube(`lessons`, {
    sql_table: `lessons`,

    joins: {
      students: {
        relationship: `many_to_one`,
        sql: `${CUBE}.student_id = ${students.id}`
      },

      teachers: {
        relationship: `many_to_one`,
        sql: `${CUBE}.teacher_id = ${teachers.id}`
      }
    }
  })
  ```
</CodeGroup>

[ref-schema-advanced-extend]: /docs/data-modeling/extending-cubes

[ref-schema-ref-cubes-extends]: /reference/data-modeling/cube#extends