Schema Selection for Queries & Reporting

What is a Schema?

Think of your database as a "house of data". 

This house has a number of rooms and each room has one or more windows.  Each of the windows lets you view a different part of the house.  In this analogy, the rooms are areas of your database (such as donations or events), and the windows and doors are a view "schema" of the inside of your database house.  Each window or door provides a different view of various parts of your database house.

The big door of your house lets you see right into most of the house but does not let you see deep into each room.  This big door is the "default schema" that lets you see part of each room of the house (a summary view of most of the rooms of data).

The other windows represent views with more details on particular rooms in your house.  You need to pick the appropriate window (schema) to see the data that you want in specific rooms of your house. 

The Default Schema

Most queries that involve basic person contact, demographic and attribute-based selection criteria will use the default schema - "* Persons with H'hold & Org & Related Info".  This default schema provides a view including:

• Person data
• Household data
• Contributions history summaries
• Primary organization information
• Inquiry tracking information
• Current opportunities involving a person
• Most recent contact events for a person
• Subscription/membership information
• Invoice/transaction details
• Credit cards stored at Internet Gateway
• Event Registration & Attendance summary information
• Communications Opt-in/out status and link tracking information
• All Attributes for persons

Most applicable fields in the above areas can be used to define criteria for the query, however, there are limitations to the query criteria and details available in each area.  If your query is for one of the following purposes, you would be best advised to use one of the schemas listed below.

Special Purpose Schemas

Purpose	Recommended Schema
Households info with one result row per household as long as you do not use any individual person fields without aggregating them. Generally used for surface mail to households.	Persons Grouped by Households & Related Info
Organization List with general address and gift info, optionally returning contact information	Organizations With Contacts and Fin. History
Organization List with primary contact info, optionally filtered	Organizations With Primary Contacts
Organization List with all contacts info, optionally filtered	Organizations With All Contacts
Contributions and Income details for acknowledgments and/or reporting with details for each transaction.  Use this schema when you want details of each transaction, otherwise, use the primary schema for aggregate financial reporting.	Contributions, Contributors & Financials
Gnosis database user reporting, such as login history.	Database User Information
Opportunity reporting with details of linked organization and person records	Opportunities/People/Organizations
Subscription/Membership reporting and communications, based on subscription status and type.  Will return a row for each subscription matching the criteria.  If you specify individual payment info, it will return a row for each payment for every subscription matching the criteria.	Subscription & Billing Information
Email sending analysis based on a log of persons receiving specified email messages.	Email Analysis
Inventory reporting for membership and subscription-based inventory items.	Inventory Management
Event Sponsorship information, administrative reporting on organization sponsors and communications with sponsor contacts.	Event Sponsorship Administration
Event-centric management, planning and reporting (other than registration, bookings and attendance).  Will return a row for each event matching the criteria.	Event Administration
Event registration related reporting and communications. Will return a row for each registration matching the criteria.	Event Registration Administration
Event bookings related communications and reporting - centering around the person making the booking. Will return a row for each booking matching the criteria.	Event Bookings Communication
Event Attendance reporting and communications, including registration details if any.  Will return a row for each event attendance matching the criteria.	Event Attendance

If you still have a question about what schema you should use for a particular purpose, please contact Connect4 Support for assistance.