Advanced Modelling Use Cases
Every database is different, and sometimes one or more of the following edge case scenarios may need to be considered when modelling the data.
Multiple Datasets Mapped to the Same Table
This is typically needed when one table represents multiple logical entities, and each entity should be represented by a separate dataset in the LDM.
While the LDM Modeler supports mapping of multiple datasets to the same table, publishing an LDM with such mapping fails.
To avoid this issue, create multiple views on top of the table and map each dataset to a separate view.
For example, you have two tables, users
and tickets
.
- The
users
table contains ticket creators and assignees. - The
tickets
table contains theassignee_id
andcreator_id
columns.
To avoid mapping multiple datasets to the users
table, do the following:
- In the database, create two views on top of the
users
table:v_users_assignees
andv_users_creators
. - In the LDM, create three datasets:
assignees
,creators
, andtickets
. - Map the
tickets
dataset to thetickets
table. - Map the
assignees
dataset to thev_users_assignees
view. - Map the
creators
dataset to thev_users_creators
view. - Create a relationship from the
assignees
dataset to the thetickets
dataset using theassignee_id
column as a primary key in theassignees
dataset. - Create a relationship from the
creators
dataset to thetickets
dataset using thecreator_id
column as a primary key in thecreators
dataset.
No Single-column Primary Key in Tables
While the LDM Modeler supports setting multi-column primary keys in datasets, publishing an LDM with multi-column primary keys fails.
To avoid this issue, create one-attribute primary keys in your database:
- Concatenate the table columns that comprise the primary key in the table.
- Calculate a hash value for the concatenated columns.
- Use the hash value as a one-attribute primary key.