Data modeling suggestions for table joins

User

Give suggestions for data modeling i.e. table joins up to 4 nested levels, specify one-to-many or one-to-one

Cursor

High‑value join patterns (up to 4 levels) with cardinalities

Accounts → Contacts → Cases → Case Updates
- Accounts 1→N Contacts via accounts_contacts (M→M table, but per account you’ll have many contacts)
- Contacts 1→N Cases via contacts_cases (M→M table; many cases per contact)
- Cases 1→N aop_case_updates via aop_case_updates.case_id
- Example:

SELECT a.id AS account_id, c.id AS contact_id, cs.id AS case_id, cu.id AS update_id
FROM accounts a
JOIN accounts_contacts ac ON ac.account_id = a.id AND ac.deleted='0'
JOIN contacts c ON c.id = ac.contact_id AND c.deleted='0'
JOIN contacts_cases cc ON cc.contact_id = c.id AND cc.deleted='0'
JOIN cases cs ON cs.id = cc.case_id AND cs.deleted='0'
LEFT JOIN aop_case_updates cu ON cu.case_id = cs.id AND cu.deleted='0';

Accounts → Opportunities → Quotes → Quote Line Items
- Accounts 1→N Opportunities via accounts_opportunities (M→M table)
- Opportunities 1→N Quotes via aos_quotes.opportunity_id
- Quotes 1→N Line Items via aos_products_quotes.parent_id where parent_type='AOS_Quotes'
- Example:

SELECT a.id, o.id, q.id, ql.id
FROM accounts a
JOIN accounts_opportunities ao ON ao.account_id = a.id AND ao.deleted='0'
JOIN opportunities o ON o.id = ao.opportunity_id AND o.deleted='0'
LEFT JOIN aos_quotes q ON q.opportunity_id = o.id AND q.deleted='0'
LEFT JOIN aos_products_quotes ql ON ql.parent_id = q.id AND ql.parent_type='AOS_Quotes' AND ql.deleted='0';

Accounts → Invoices → Line Item Groups → Line Items
- Accounts 1→N Invoices via aos_invoices.billing_account_id
- Invoices 1→N Groups via aos_line_item_groups.parent_id with parent_type='AOS_Invoices'
- Groups 1→N Line Items via aos_products_quotes.group_id
- Example:

SELECT a.id, i.id, g.id, li.id
FROM accounts a
JOIN aos_invoices i ON i.billing_account_id = a.id AND i.deleted='0'
LEFT JOIN aos_line_item_groups g ON g.parent_id = i.id AND g.parent_type='AOS_Invoices' AND g.deleted='0'
LEFT JOIN aos_products_quotes li ON li.group_id = g.id AND li.deleted='0';

Accounts → Documents (via link) → Document Revisions
- Accounts M↔M Documents via documents_accounts
- Documents 1→N Revisions via document_revisions.document_id
- Example:

SELECT a.id, d.id, dr.id
FROM accounts a
JOIN documents_accounts da ON da.account_id = a.id AND da.deleted='0'
JOIN documents d ON d.id = da.document_id AND d.deleted='0'
LEFT JOIN document_revisions dr ON dr.document_id = d.id AND dr.deleted='0';

Users → SecurityGroups (via link) → ACL Roles
- Users M↔M SecurityGroups via securitygroups_users
- SecurityGroups M↔M ACL Roles via securitygroups_acl_roles
- Example:

SELECT u.id AS user_id, sg.id AS group_id, ar.id AS role_id
FROM users u
JOIN securitygroups_users sgu ON sgu.user_id = u.id AND sgu.deleted='0'
JOIN securitygroups sg ON sg.id = sgu.securitygroup_id AND sg.deleted='0'
LEFT JOIN securitygroups_acl_roles sgar ON sgar.securitygroup_id = sg.id AND sgar.deleted='0'
LEFT JOIN acl_roles ar ON ar.id = sgar.role_id AND ar.deleted='0';

Campaigns → Email Marketing → Email Queue (emailman) → Emails
- Campaigns 1→N Email Marketing via email_marketing.campaign_id
- Email Marketing 1→N Email Queue via emailman.marketing_id
- Emails linked via emails_beans or by emails.parent_type/parent_id depending on reporting need
- Example (queue focus):

SELECT c.id, emkt.id, eq.id
FROM campaigns c
LEFT JOIN email_marketing emkt ON emkt.campaign_id = c.id AND emkt.deleted='0'
LEFT JOIN emailman eq ON eq.marketing_id = emkt.id AND eq.deleted='0';

Meetings → Contacts (attendees) → Accounts (via link) → Opportunities
- Meetings M↔M Contacts via meetings_contacts
- Contacts M↔M Accounts via accounts_contacts
- Accounts 1→N Opportunities via accounts_opportunities
- Example:

SELECT m.id AS meeting_id, c.id AS contact_id, a.id AS account_id, o.id AS opportunity_id
FROM meetings m
JOIN meetings_contacts mc ON mc.meeting_id = m.id AND mc.deleted='0'
JOIN contacts c ON c.id = mc.contact_id AND c.deleted='0'
JOIN accounts_contacts ac ON ac.contact_id = c.id AND ac.deleted='0'
JOIN accounts a ON a.id = ac.account_id AND a.deleted='0'
LEFT JOIN accounts_opportunities ao ON ao.account_id = a.id AND ao.deleted='0'
LEFT JOIN opportunities o ON o.id = ao.opportunity_id AND o.deleted='0';

Projects → Project Tasks → Assigned Users → SecurityGroups
- Projects 1→N Project Tasks via project_task.project_id
- Project Tasks N→1 Users via project_task.assigned_user_id
- Users M↔M SecurityGroups via securitygroups_users
- Example:

SELECT p.id, pt.id, u.id AS assignee_id, sg.id AS group_id
FROM project p
LEFT JOIN project_task pt ON pt.project_id = p.id AND pt.deleted='0'
LEFT JOIN users u ON u.id = pt.assigned_user_id AND u.deleted='0'
LEFT JOIN securitygroups_users sgu ON sgu.user_id = u.id AND sgu.deleted='0'
LEFT JOIN securitygroups sg ON sg.id = sgu.securitygroup_id AND sg.deleted='0';

Knowledge Base Category → Knowledge Base → Contacts
- Categories M↔M KB via aok_knowledgebase_categories
- KB M↔M Contacts via am_projecttemplates_contacts_1_c? (No; for KB use direct fields only. For contacts, you’ll typically relate via cases/emails; keep to 2 levels here.)
- Practical 2‑level:

SELECT cat.id, kb.id
FROM aok_knowledge_base_categories cat
JOIN aok_knowledgebase_categories l ON l.aok_knowledge_base_categories_id = cat.id AND l.deleted='0'
JOIN aok_knowledgebase kb ON kb.id = l.aok_knowledgebase_id AND kb.deleted='0';

Opportunities → Documents (via link) → Accounts
- Opportunities M↔M Documents via documents_opportunities
- Opportunities M↔M Accounts via accounts_opportunities
- Example:

SELECT o.id, d.id AS document_id, a.id AS account_id
FROM opportunities o
LEFT JOIN documents_opportunities do_ ON do_.opportunity_id = o.id AND do_.deleted='0'
LEFT JOIN documents d ON d.id = do_.document_id AND d.deleted='0'
LEFT JOIN accounts_opportunities ao ON ao.opportunity_id = o.id AND ao.deleted='0'
LEFT JOIN accounts a ON a.id = ao.account_id AND a.deleted='0';

Emails → Email Addresses (through relation) → Contacts
- Emails M↔M Email Addresses via emails_email_addr_rel
- Email Addresses M↔M Beans via email_addr_bean_rel filtered to bean_module='Contacts'
- Example:

SELECT e.id AS email_id, ea.email_address, c.id AS contact_id
FROM emails e
JOIN emails_email_addr_rel eea ON eea.email_id = e.id AND eea.deleted='0'
JOIN email_addresses ea ON ea.id = eea.email_address_id AND ea.deleted='0'
JOIN email_addr_bean_rel eab ON eab.email_address_id = ea.id AND eab.bean_module='Contacts' AND eab.deleted='0'
JOIN contacts c ON c.id = eab.bean_id AND c.deleted='0';

Cardinality quick guide

One‑to‑many (1→N):
- Parent id stored in child:
  - aos_quotes.opportunity_id (Opportunity 1→N Quotes)
  - aos_invoices.billing_account_id (Account 1→N Invoices)
  - aop_case_updates.case_id (Case 1→N Updates)
  - document_revisions.document_id (Document 1→N Revisions)
  - project_task.project_id (Project 1→N Tasks)
  - contacts.reports_to_id (Contact 1→N Direct Reports)
  - Most _audit tables: audited entity 1→N audit rows
Many‑to‑many (M↔M) via link tables:
- accounts_contacts, accounts_opportunities, documents_accounts, documents_opportunities, meetings_contacts, calls_contacts, securitygroups_users, securitygroups_acl_roles, projects_*, etc.
One‑to‑one (1↔1):
- Core ↔ Custom extension tables on id = id_c:
  - accounts ↔ accounts_cstm
  - contacts ↔ contacts_cstm
  - leads ↔ leads_cstm
  - opportunities ↔ opportunities_cstm
  - cases ↔ cases_cstm
  - project ↔ project_cstm
  - meetings ↔ meetings_cstm
- Note: Password reset and token tables are usually 1→N per user, not 1↔1.

Practical notes

Prefer LEFT JOINs outward from your primary entity to retain rows when related data is optional.
Always filter link tables with deleted='0' and, where applicable, constrain parent_type to the expected module.
For line items, constrain by parent_type (AOS_Quotes or AOS_Invoices) and/or group_id.

I can tailor joins for your exact reporting questions if you share the metrics you want.

SELECT a.id AS account_id, c.id AS contact_id, cs.id AS case_id, cu.id AS update_id
FROM accounts a
JOIN accounts_contacts ac ON ac.account_id = a.id AND ac.deleted='0'
JOIN contacts c ON c.id = ac.contact_id AND c.deleted='0'
JOIN contacts_cases cc ON cc.contact_id = c.id AND cc.deleted='0'
JOIN cases cs ON cs.id = cc.case_id AND cs.deleted='0'
LEFT JOIN aop_case_updates cu ON cu.case_id = cs.id AND cu.deleted='0';

SELECT a.id, o.id, q.id, ql.id
FROM accounts a
JOIN accounts_opportunities ao ON ao.account_id = a.id AND ao.deleted='0'
JOIN opportunities o ON o.id = ao.opportunity_id AND o.deleted='0'
LEFT JOIN aos_quotes q ON q.opportunity_id = o.id AND q.deleted='0'
LEFT JOIN aos_products_quotes ql ON ql.parent_id = q.id AND ql.parent_type='AOS_Quotes' AND ql.deleted='0';

SELECT a.id, i.id, g.id, li.id
FROM accounts a
JOIN aos_invoices i ON i.billing_account_id = a.id AND i.deleted='0'
LEFT JOIN aos_line_item_groups g ON g.parent_id = i.id AND g.parent_type='AOS_Invoices' AND g.deleted='0'
LEFT JOIN aos_products_quotes li ON li.group_id = g.id AND li.deleted='0';

SELECT a.id, d.id, dr.id
FROM accounts a
JOIN documents_accounts da ON da.account_id = a.id AND da.deleted='0'
JOIN documents d ON d.id = da.document_id AND d.deleted='0'
LEFT JOIN document_revisions dr ON dr.document_id = d.id AND dr.deleted='0';

SELECT u.id AS user_id, sg.id AS group_id, ar.id AS role_id
FROM users u
JOIN securitygroups_users sgu ON sgu.user_id = u.id AND sgu.deleted='0'
JOIN securitygroups sg ON sg.id = sgu.securitygroup_id AND sg.deleted='0'
LEFT JOIN securitygroups_acl_roles sgar ON sgar.securitygroup_id = sg.id AND sgar.deleted='0'
LEFT JOIN acl_roles ar ON ar.id = sgar.role_id AND ar.deleted='0';

SELECT c.id, emkt.id, eq.id
FROM campaigns c
LEFT JOIN email_marketing emkt ON emkt.campaign_id = c.id AND emkt.deleted='0'
LEFT JOIN emailman eq ON eq.marketing_id = emkt.id AND eq.deleted='0';

SELECT m.id AS meeting_id, c.id AS contact_id, a.id AS account_id, o.id AS opportunity_id
FROM meetings m
JOIN meetings_contacts mc ON mc.meeting_id = m.id AND mc.deleted='0'
JOIN contacts c ON c.id = mc.contact_id AND c.deleted='0'
JOIN accounts_contacts ac ON ac.contact_id = c.id AND ac.deleted='0'
JOIN accounts a ON a.id = ac.account_id AND a.deleted='0'
LEFT JOIN accounts_opportunities ao ON ao.account_id = a.id AND ao.deleted='0'
LEFT JOIN opportunities o ON o.id = ao.opportunity_id AND o.deleted='0';

SELECT p.id, pt.id, u.id AS assignee_id, sg.id AS group_id
FROM project p
LEFT JOIN project_task pt ON pt.project_id = p.id AND pt.deleted='0'
LEFT JOIN users u ON u.id = pt.assigned_user_id AND u.deleted='0'
LEFT JOIN securitygroups_users sgu ON sgu.user_id = u.id AND sgu.deleted='0'
LEFT JOIN securitygroups sg ON sg.id = sgu.securitygroup_id AND sg.deleted='0';

SELECT cat.id, kb.id
FROM aok_knowledge_base_categories cat
JOIN aok_knowledgebase_categories l ON l.aok_knowledge_base_categories_id = cat.id AND l.deleted='0'
JOIN aok_knowledgebase kb ON kb.id = l.aok_knowledgebase_id AND kb.deleted='0';

SELECT o.id, d.id AS document_id, a.id AS account_id
FROM opportunities o
LEFT JOIN documents_opportunities do_ ON do_.opportunity_id = o.id AND do_.deleted='0'
LEFT JOIN documents d ON d.id = do_.document_id AND d.deleted='0'
LEFT JOIN accounts_opportunities ao ON ao.opportunity_id = o.id AND ao.deleted='0'
LEFT JOIN accounts a ON a.id = ao.account_id AND a.deleted='0';

SELECT e.id AS email_id, ea.email_address, c.id AS contact_id
FROM emails e
JOIN emails_email_addr_rel eea ON eea.email_id = e.id AND eea.deleted='0'
JOIN email_addresses ea ON ea.id = eea.email_address_id AND ea.deleted='0'
JOIN email_addr_bean_rel eab ON eab.email_address_id = ea.id AND eab.bean_module='Contacts' AND eab.deleted='0'
JOIN contacts c ON c.id = eab.bean_id AND c.deleted='0';

Data modeling suggestions for table joins

High‑value join patterns (up to 4 levels) with cardinalities​

Cardinality quick guide​

Practical notes​

High‑value join patterns (up to 4 levels) with cardinalities

Cardinality quick guide

Practical notes