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 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
)
# 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
})
# Single line (max 4000 chars)
"new_Name": "string"
# Multi-line text (memo)
"new_Description": "string" # Will be nvarchar(max)
# Integer
"new_Quantity": "int"
# Decimal (with precision)
"new_Amount": "decimal" # Default precision
# Currency (use decimal with formatting)
"new_Budget": "decimal"
# Date and time
"new_StartDate": "datetime"
# Date only (format in app)
"new_DueDate": "datetime"
# Yes/No
"new_IsActive": "bool"
"new_IsApproved": "bool"
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
})
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
# Create contact linked to account
contact = {
"firstname": "John",
"lastname": "Doe",
"[email protected]": f"/accounts({account_id})"
}
client.create("contact", contact)
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.
Table references itself (e.g., employee hierarchy).
Employee
├── new_ManagerId → Employee
└── Parent employee record
- 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'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
Account (Master)
├── new_AccountNumber
├── new_Name
└── Contacts (Detail)
├── new_FirstName
├── new_LastName
└── _parentcustomerid_value (FK)
new_Request
├── new_Name
├── new_Status (Draft → Submitted → Approved/Rejected)
├── new_SubmittedOn
├── new_ApprovedBy
└── new_ApprovedOn
new_Order
├── new_OrderNumber
├── new_Status
├── createdon (system)
├── createdby (system)
├── modifiedon (system)
└── modifiedby (system)
# Add new columns
client.create_columns("new_Project", {
"new_CompletionPercentage": "int",
"new_ActualEndDate": "datetime"
})
# Remove columns
client.delete_columns("new_Project", ["new_OldColumn"])
- See
references/table-design.md
for detailed patterns
- See
references/relationships.md
for relationship examples
- See
references/performance.md
for optimization tips