Getting started - SuperOffice

This section will give you an overview of the SuperOffice Database and explain the basic elements that you need to get started. We are now going to look at how the database is put together, and how the different tables in the database relate to the user interface. This should make it easier to understand what the components are doing for you.

General principle

As a general principle, the SuperOffice database is very lax. There are no declared constraints on tables. Referential integrity is not enforced strictly, and the structure would make a computer science professor or system administrator shudder. However, there is a reason for this lax design. People are not strict or formal. A CRM system is mainly about people, what they do, and when. It is not an accounting system. It is not subject to the rigors of accounts auditing. People delete things and then want them back. People change things around and merge in partial data from external sources. The SuperOffice database schema is intended to be flexible while being understandable and efficient.

SuperOffice view of the world

SuperOffice has 5 important things:

Companies in code and database references referred to as contact.
Associates are SuperOffice users - employees of the company.
Projects are a type of activity that usually involves several stakeholders and various types of other activities.
Activities are things employees do with companies, contacts, projects, diary, and sales.
- appointments of various task types
- documents
- mailings and form submissions
- chat sessions
Tickets from Service are linked to persons who may or may not belong to a company.

See the main tables below for more detail.

Business rules

Activities happen at a particular point in time.
Sales are expected to be closed on a date in the future.
Appointments are scheduled for a particular date and time.
Documents are written on a specific day.
Activities start on one day and finish on another.
Time is important for activities.
Companies and projects are not time-specific, but linked to activities in time.

The appointment table contains foreign keys to all the other important tables, plus a date field (ActiveDate).

Context-sensitive labels

Real-world term	UI	Database table
company organization	company	contact
person	contact associate
customer	contact	person
employee	associate	associate
user account	user	ejuser
database owner	tenant	Company

The main tables

The main tables in the system are:

Table	Contains
Contact	The companies
Person	The contact persons
Associate	The users - these are linked to contact persons
Appointment	Meetings, to-do activities
Document	Document activities
Sale	Sale activities
Project	Projects
ProjectMember	Persons who are part of a project
Selection	Selections
SelectionMember	Link table for Contact+Person who are selection members
Ticket	Service tickets

The Company table is used to store the ID of the company that owns the license. It has nothing to do with the company display in the user interface.

Unfortunately, not all table names and UI labels match. If in doubt, see SuperOffice view of the world above.

Overview of tables

The database has quite a few tables in it. About 456 tables in total. Nothing like a Siebel or SAP system, but still enough to be confusing at first look. The main objects in the system are:

Activities (Appointment table) - including the date and time the activity happened
Companies (Contact table)
Projects
Users (Associate table)
Tickets

In the database, these relationships are expressed using the following set of tables and fields. Table structure showing how Appointment, Contact, Person, Project, and Associate relate to each other

Table structure showing how Appointment, Contact, Person, Project, and Associate relate to each other

This gives us the following join conditions we can use:

Appointment.contact_id = Contact.contact_id
Appointment.person_id = Person.person_id
Appointment.project_id = Project.project_id
Appointment.associate_id = Associate.associate_id
Person.contact_id = Contact.contact_id
Associate.person_id = Person.person_id
Ticket.cust_id = person.person_id

The appointment table contains foreign keys to all the other important tables, plus a date field (ActiveDate). Because the people working for your customers are often important too, we keep track of them as well. We break 3rd normal form (a database design principle) by storing both the contact_id and the person_id in the appointment to make the database more efficient. Also, in the future, we may break the Person-contact relation, allowing consumers to be registered: person records without a corresponding company. Whenever a user enters an appointment, it will have a non-zero associate_id, an activeDate, and one of the other two IDs set (contact, or project). The person ID can be zero even if the user has set a contact (selected a company, but no person).

Continue reading

Naming conventions

​General principle

​SuperOffice view of the world

​Business rules

​Context-sensitive labels

​The main tables

​Overview of tables

​Continue reading

General principle

SuperOffice view of the world

Business rules

Context-sensitive labels

The main tables

Overview of tables

Continue reading