Skill v1.0.0

currentAutomated scan100/100

ramihollings/mr.technology/dataverse-schema-design

──Details

PublishedApril 26, 2026 at 04:52 PM

Content Hashsha256:8f072827443a1881...

Git SHA389106de039e

──Files

Files (1 file, 4.8 KB)

SKILL.md4.8 KBactive

SKILL.md · 206 lines · 4.8 KB

version: "1.0.0"

dataverse-schema-design

This skill provides guidance on designing Dataverse table schemas and data models. Use when users ask about "Dataverse table design", "Dataverse schema", "Dataverse relationships", "Dataverse columns", "data modeling Dataverse", "Dataverse best practices", or need help designing their data structure.

Table Design Fundamentals

Naming Conventions

Table prefix: Use publisher prefix (e.g., new_, cr123_)
Table names: PascalCase, singular (e.g., new_Project, new_Task)
Column names: prefix_columnname (e.g., new_projectname, new_startdate)

Create Table with SDK

python

# Basic table
client.create_table("new_Project", {
    "new_ProjectName": "string",      # Text column
    "new_Description": "string",       # Multi-line text
    "new_Budget": "decimal",           # Currency/decimal
    "new_StartDate": "datetime",       # Date and time
    "new_IsActive": "bool",            # Yes/No
    "new_Priority": Priority           # Choice/enum
})

Column Types

Text Columns

python

# Single line (max 4000 chars)
"new_Name": "string"
# Multi-line text (memo)
"new_Description": "string"  # Will be nvarchar(max)

Number Columns

python

# Integer
"new_Quantity": "int"
# Decimal (with precision)
"new_Amount": "decimal"  # Default precision
# Currency (use decimal with formatting)
"new_Budget": "decimal"

Date/Time Columns

python

# Date and time
"new_StartDate": "datetime"
# Date only (format in app)
"new_DueDate": "datetime"

Boolean Columns

python

# Yes/No
"new_IsActive": "bool"
"new_IsApproved": "bool"

Choice (Picklist) Columns

python

from enum import IntEnum
class Status(IntEnum):
    DRAFT = 1
    SUBMITTED = 2
    APPROVED = 3
    REJECTED = 4
class Priority(IntEnum):
    LOW = 1
    MEDIUM = 2
    HIGH = 3
# Create table with choices
client.create_table("new_Request", {
    "new_Name": "string",
    "new_Status": Status,
    "new_Priority": Priority
})

Relationship Patterns

One-to-Many (1:N)

Parent table has many child records.

Account (1) ←→ (N) Contact
└── An account has many contacts
 
Project (1) ←→ (N) Task
└── A project has many tasks

Implementation:

Create lookup column on child table
Reference parent using @odata.bind

python

# Create contact linked to account
contact = {
    "firstname": "John",
    "lastname": "Doe",
    "parentcustomerid_account@odata.bind": f"/accounts({account_id})"
}
client.create("contact", contact)

Many-to-Many (N:N)

Records in both tables can relate to multiple records in the other.

Account (N) ←→ (N) Contact
└── Contacts can be related to multiple accounts

Note: N:N relationships require creating an intersection entity via the UI or advanced API calls.

Self-Referential

Table references itself (e.g., employee hierarchy).

Employee
├── new_ManagerId → Employee
└── Parent employee record

Schema Design Best Practices

Do's

Plan before creating - Design schema on paper first
Use meaningful names - Clear, descriptive column names
Add descriptions - Document purpose of each column
Set required fields - Enforce data quality
Use appropriate types - Don't store numbers as text
Create indexes - On frequently filtered columns
Use lookups - Instead of duplicating data

Don'ts

Don't over-normalize - Balance between normalization and performance
Don't use reserved names - Avoid system column names
Don't create wide tables - Split into related tables if >100 columns
Don't store calculated data - Use calculated columns instead
Don't ignore security - Plan field-level security early

Common Schema Patterns

Master-Detail

Account (Master)
├── new_AccountNumber
├── new_Name
└── Contacts (Detail)
    ├── new_FirstName
    ├── new_LastName
    └── _parentcustomerid_value (FK)

Status Workflow

new_Request
├── new_Name
├── new_Status (Draft → Submitted → Approved/Rejected)
├── new_SubmittedOn
├── new_ApprovedBy
└── new_ApprovedOn

Audit Trail

new_Order
├── new_OrderNumber
├── new_Status
├── createdon (system)
├── createdby (system)
├── modifiedon (system)
└── modifiedby (system)

Adding Columns to Existing Tables

python

# Add new columns
client.create_columns("new_Project", {
    "new_CompletionPercentage": "int",
    "new_ActualEndDate": "datetime"
})
# Remove columns
client.delete_columns("new_Project", ["new_OldColumn"])

References

See references/table-design.md for detailed patterns
See references/relationships.md for relationship examples
See references/performance.md for optimization tips

All versions