Hi all,

I have created a diagram that shows how the API Tables reference each other.


  • This is an “unofficial” diagram.
  • I would say its 95%+ accurate, so there maybe a couple of errors.
  • Tables and Fields are based on Infusionsoft Table documentation.
  • Text/String fields are set to TEXT type, as the limits are unknown.
  • I will provide the files and SQL output later. Need to see what other tweaks are needed.
  • Making this diagram has revealed additional things that could aid developers, which will be explained in a later date.
  • Ticket tables are not included, as the module is deprecated from Infusionsoft.


Click on the image or the link below to enlarge it. It is quite large at 4795 × 5149 pixels!



Table Relationships

List of the table relationships are also present on the diagram on the bottom left of the picture.

Affiliate

Affiliate.ContactId > Contact.Id
Affiliate.ParentId > Affiliate.Id

Referral.ContactId > Contact.Id
Referral.AffiliateId > Affiliate.Id


Company

Company.CompanyID > Company.Id
Company.LeadSourceId > LeadSource.Id
Company.OwnerID > User.Id


Contact

Contact.CreatedBy > User.Id
Contact.LeadSourceId > LeadSource.Id
Contact.OwnerID > User.Id

Expense.ContactId > Contact.Id

FileBox.ContactId > Contact.Id


Contact Action

ContactAction.ContactId > Contact.Id


Credit Card

CCharge.CCId > CreditCard.Id
CCharge.PaymentId > Payment.Id

CreditCard.ContactId > Contact.Id


Custom Fields & Tabs

DataFormField.GroupId > DataFromGroup.Id

DataFormGroup.TabId > DataFormTab.Id


Follow Up Sequence

Campaignee.CampaignId > Campaign.Id

CampaignStep.CampaignId > Campaign.Id
CampaignStep.TemplateId > Template.Id


Lead / Opportunity

Lead.AffiliateId > Affiliate.Id
Lead.ContactID > Contact.Id
Lead.StageID > Stage.Id
Lead.StatusID > Status.Id

StageMove.OpportunityId > Lead.Id
StageMove.UserId > User.Id


Lead Source

LeadSource.LeadSourceCategoryId > LeadSourceCategory.Id

LeadSourceExpense.LeadSourceId > LeadSource.Id
LeadSourceExpense.LeadSoruceRecurringExpenseId > 

LeadSourceRecurringExpense.Id
LeadSourceRecurringExpense.Id.LeadSourceId > LeadSource.Id


Orders / Jobs / Invoices / Payments / Pay Plans

Payment.InvoiceId > Invoice.Id
Payment.ContactId > Contact.Id

InvoicePayment.InvoiceId > Invoice.Id
Invoice.ContactId > Contact.Id
Invoice.JobId > Job.Id
Invoice.AffiliateId > Affiliate.Id

InvoiceItem.InvoiceId > Invoice.Id
InvoiceItem.OrderItemId > OrderItem.Id

Job.ContactId > Contact.Id

OrderItem.OrderId > Job.Id
OrderItem.ProductId > Product.Id

PayPlan.InvoiceId > Invoice.Id

PayPlanItem.PayPlanId > PayPlan.Id


Product

ProductCategoryAssign.ProductId > Product.Id
ProductCategoryAssign.ProductCategoryId > ProductCategory.Id

ProductInterest.ObjectId > Lead.Id
ProductInterest.ProductId > Product.Id

ProductOption.ProductId > Product.Id

ProductOptValue.ProductOptionId > ProductOption.Id


Saved Search

SavedFilter.UserId > User.Id


Subscription / Reoccurring Orders

CProgram.ProductId > Product.Id

JobRecurringInstance.InvoiceItemId > InvoiceItem.Id
JobRecurringInstance.RecurringId > RecurringOrder.Id

RecurringOrder.AffiliateId > Affiliate.Id
RecurringOrder.CC1 > CreditCard.Id
RecurringOrder.CC2 > CreditCard.Id
RecurringOrder.ContactId > Contact.Id
RecurringOrder.LeadAffiliateId > Affiliate.Id
RecurringOrder.OriginatingOrderId > Job.Id
RecurringOrder.ProductId > Product.Id
RecurringOrder.ProgramId > CProgram.Id

RecurringOrderWithContact.AffilaiteId > Affiliate.Id
RecurringOrderWithContact.CC1 > CreditCard.Id
RecurringOrderWithContact.CC2 > CreditCard.Id
RecurringOrderWithContact.ContactId > Contact.Id
RecurringOrderWithContact.ProductId > Product.Id

RecurringOrderWithContact.ProgramId > CProgram.Id
RecurringOrderWithContact.RecurringOrderId > RecurringOrder.Id
RecurringOrderWithContact.SubscriptionPlanId > SubscriptionPlan.Id

SubscriptionPlan.ProductId > Product.Id


Tag / Contact Group

ContactGroupAssign.ContactId > Contact.Id
ContactGroupAssign.GroupId > ContactGroup.Id

ContactGroup.GroupCategoryId > ContactGroupCategory.Id


User

GroupAssign.GroupId > UserGroup.GroupId
GroupAssign.UserId > User.Id

UserGroup.OwnerId > User.Id


Enjoy