Mayon Information Architecture

The intention of this document is to give a general guide on data schema interpretation and to provide some best practices and tips on things to avoid when updating the schema. A basic foundation, including person and communications, is established for further development. The schema is by no means complete, but provides a good start for the application to built on top of. It is still undergoing changes as we gain more understanding of how Doctrine, Symfony, and the database work together as well as enhancing the application with more features.

The database is built upon two concepts: (1) the database should reflect and support as much as possible the business logics, and (2) the database should be normalized to ensure data integrity. Data normalization is a systematic process of managing a relational database. It has several progressional series of normal form data. The third normal form is the most commonly practiced and recommended in database normalization. Below is a list of references on data normalization:

• http://www.bkent.net/Doc/simple5.htm
• http://en.wikipedia.org/wiki/Database_normalization
• http://www.agiledata.org/essays/dataNormalization.html

Doctrine is the official ORM used in Symfony to handle Agasti's backend relational database. Every ORM has limitations and benefits in data management and software development. Doctrine correlates the relational database into PHP classes. This allows more flexibility for software developers to access the database without requiring an in-depth knowledge of sql queries. In addition, developers can take advantage of their already made-easy templates and functions, such as timestampable, versionable, i18n, softdelete, and data validations.

It is strongly recommended to build the initial schema in YAML format. This guarantees that the frontend model classes corresponds to the backend database structure and that the schema is designed the way it is expected to work. Doctrine's nature is to interpret the YAML files (YMLs) into model classes. Every parts of the application development spawns from the models. The actual database tables and the module, form, and filter classes all derive from the models. Symfony relies heavily and exclusively on the classes generated by Doctrine models as the building block for frontend work and db abstractions. Absolutely avoid using Doctrine build-in tool to reverse engineer a database into YMLs, especially when the sql commands are not ANSI standard but db engine specific. Doctrine's build-schema tool will convert any sql into YML with its limitation extending from data types to views to table relations conversions. Doctrine has the basic db conversions, but may not always have the correct conversions. MySQL's integer(11) data type, for instance, will automatically be converted as a BigInt in the Doctrine world. Although an incorrect data type, the sql data type will consistently be converted to the same Doctrine data type. Doctrine supports db views. However, it does not recognize db views in reverse engineer. It treats views as if they are regular table eliminating all the preset data. Composite foreign keys and primary foreign keys are not recognized nor supported by Doctrine. Doctrine will retain the table structures, but not their relations when converted to YMLs. The tables will be treated as stand-alones. This relations limitations also constricted the data normalization to be either in the first or fifth normal form. For any db elements that Doctrine does not recognize, they get ignored and not translated into YML. As suggested, it is best practice to build the database through the YMLs.

The table name defined in the YML will be used as the class file name.

• Table name
  • Table names in the schema YML are camel-cased and are used throughout the applications as file and class names. The camel-case in the YML will then be converted to underscores in the database. For example, Doctrine automatically converts agPersonName in YML to ag_person_name in the db. Camel-case only applies to table names. Field names remain the same in the YML and the db.
  • Avoid acronyms and abbreviations which can be interpreted differently by different developers. The name should represent what the functions of the table.
  • The table, field, and index namings should not exceeed 64 characters.
  • Doctrine prefer using surrogate keys.
• Index name - Unique index should be indicated with a unq suffix. It is not necessary to label idx for indices since Doctrine automatically appends idx when converting to sql statements.
• Table relations
  • Best to define the table structure and its relations rather than allowing Doctrine to auto-detect. “Doctrine Relations”:http://www.doctrine-project.org/documentation/manual/1_0/hu/yaml-schema-files#relationships
  • A one-to-one relationship in Doctrine is established by creating a one-to-many table relation and then set a unique key on the foreign key forcing it to be a one-to-one.
  • Doctrine requires a special set-up for a many-to-many relation. The join table establishes the foreign keys to the two parents that are being joined. The parent tables also needs to reference back to the joining table.
• Keywords - Avoid using db and doctrine's key words such as create, select, and desc as table or field names.
• Reference keys - Reference keys in data fixtures - Refer to the table name instead of the field name in fixture files. Otherwise, Doctrine will treat it as normal field data and may not insert data in their proper order.

More to come.

As of November 22, 2010, the core database schema is comprised of a default schema and 18 plugins:

• agAudit
• agAuthentication
• agAuthGuardLink
• agCommunications
• agDeployment
• agEvent
• agFacility
• agGeodata
• agMessage
• agOrganization
• agPerson
• agScenario
• agSite
• agSkill
• agStaff
• agStandards
• sfDoctrineGuardPlugin
• agPet

All the above plugins are Agasti custom schema with the exception of sfDoctrineGuardPlugin. SfDoctrineGuardPlugin is an open source Symfony plugin that we adapted into our application to handle the web login authentication. We will not be discussing about sfDoctrineGaurdPlugin in this document other than it generates several tables prefixed with “sf_” to capture login user name, password, permission and such. For more information please refer to their website, http://www.symfony-project.org/plugins/sfDoctrineGuardPlugin.

The default schema defines the general schema that is used by the system. It holds general host information and system specific information, such as module versions, module enabled status, and more. It also defines the top level person-like entity for sub-levels of person, organization, and facility entities. An entity id should have a one-to-one relationships with and not shared among its sub-level entity. This needs to be enforced from frontend scripts.

AgAudit plugin should capture all audit related tables. Currently, it only has a small set of tables to capture file importing information.

Both agAuthentication and agAuthGuardLink plugins are used to manage login accounts.

AgAuthentication plugin is Agasti's custom extension to sfDoctrineGuardPlugin. This gives the flexibility for users to create types of access account, such as a staff member, a client, or a facility generic login accounts, and the account statuses. However, user account permissions should still be set by the sfDoctrineGuardPlugin.

AgAuthGuardLink plugin ties sfguard login tables to our custom account table where any person in the system can have a login credential to login to the system.

AgPerson is a sub level of the person-like 'entity' defined in the default schema. It is consist with the most basic form of a person. Whether the person is a staff or a client, they all have the same person attributes of name, language, birth date, sex, ethnicity, religion, profession, marital status, and residential status. Persons and their sex and birthdate should only have a one-to-one relationship. Every other person's attributes has a many-to-many relationship. Person's language and name are slightly more complicated than the other attributes. A person may be skilled with multiple languages, each with different level of competencies in the areas of reading, writing, and speaking. These components are defined in the agLanguageFormat and the agLanguageCompetency tables. Person's name is another complex attribute of a person. Person's full name can be broken down into different components of first, middle, last, and maiden names. Thus, person's name is captured as individual components in agPersonName table, first name as one entry in the table and last name as a separate entry. Different components of name is defined in agPersonNameType. Both name and language tables have a priority field to set primary and secondary orders of language and name used. The priority field is an integer data type and is unique per person. The lowest number should always be treated as the primary.

Extending from person's schema is the agSkill plugins. It allows the options for administrators to define person's skill sets and their qualities. It also allows staff skills provisioning where certain skills may be a requirement or a desire for certain staff resources. For instance, if a staff is recognized as a doctor, it would make more sense if the staff has a certified doctorial degree in practing medical treatments.

Client is a sub tier of a person who uses the services provided by and is event specific. A client cannot exists if an event did not occur. For clients with no specific birthdate given during registration, a static information on age and the recorded date is captured within the system, which may be used for client searches. To avoid convoluting staff's skills information, client skills are captured in a separate client raw skill table, which can then be mapped to the main person's skill table. Their medical and other awareness information are simply captured in their respective tables in the schema.

For clients who come in as a group can be represented as a group entity with its members defined in the schema. Every client group has a specified group leader as the main contact person within the group. Note that the group leader should also be defined in the ag_client_group_membership as a member of the group. Their relationships can be captured in the ag_entity_relationship table. Needless to say, group composition and its count can be captured in the ag_client_group_composition table.

Similar to staff, facility, and facility group allocation, client's allocation is also captured to help manage facility population, especially when clients transfer from one facility to another or leaving and re-admitting to a facility.

Any entity that functions similar to a person is a sub level of entity. AgStaff and agOrganization are sub level of entities. They all have email, phone, and address contact information. They both can provide financial support, which is not covered in the current schema. AgStaff is a specific group of person, a sub level of person. Anyone (whether they are volunteers, humanity organization members, city workers, or even clients) who helps out in an event can be part of the staff group.

AgSite is an exceptional person-like entity. A sub level of agSite, agFacility, has features of all three person's communications. By including agFacility as the lower tier of entity, it can share the use of the agCommunication plugins. The advantage of sharing is eliminating unnecessary duplicate communication tables. The function of agSite is further discussed below.

As mentioned, agCommunications plugin collects both person and facility contact information. These communication tables are associated to the entity directly, skipping the person or facility middle tier.

Refer to Persons Communication Schema for more information on the use of tables.

AgStandards handles all ISO information. Currently it only has country's ISO info, but it will expand with other standards like language and person's gender. Standardization will be helpful for world wide communication and global data sharing.

AgGeodata has a geo entity. It is connected with geographic points. A geo can be a point in a map or a collections of geo points to form a polygon area in a map. Each geo entity is defined by the source and the quality of the source with a score table. AgAddress will be mapped with a geo point along with its own score table. In addition, the agGeodata provide a table of geo distance calculations. This is a cartesian table of geo to geo point distance, which will be used in staff and facility deployment.

Currently, agDeployment schema is under developed. It is a place holder for future developement to capture mathematic deployment methods.

Please refer to Event Scenario and Facility Schema.

AgMessage plugin captures the flow of messages between admins and persons. It consists of three component: the batch, the elements, and the reply argument. The message id is an unique identifier that ties all three components. All stages of the message is time stamped and logged in ag_message_detail table.

The message element piece has a template table that outlines each elements of a message. It defines what a message should have, such as the sender, receive, and the body of the message. For email types of messaging, it can also include a cc, a bcc, and a subject line fields. The element values of a message template is stored as a blob, which gives the system the flexibility to store large body content. It can store the entire content of the body of the message as replaceable string, such as “Hi %s, please report to %s at %s.” The actual sent message is also logged in the ag_message_element table as such “Hi Mary Varges, please report to the Grand Army Plaza Library at 9am.”

The message batch defines the media types. Similar to the message element piece, message batch also has a template table. A message can be a form of an email, a phone call back, or a pager.

The message reply argument piece captures the response collected from receivers. It is stored in the ag_message_reply table as blob. no validation is done at this time. Each reply is identified with a message_reply_argument_id, such as date of birth, SSN, and work time availability.

Pets have potential to also being serviced in an emergency activation. Since they can be brought in by clients or by animal welfare organizations, pets are treated independently from their care taker. Basic pet information, such as their name, age, species, physical description and any other additional information, can be stored within the agPet plugin schema. An allocation status table is also used to track pets whereabouts.

Notice some of the tables throughout all schema plugins has an additional field of app_display, a boolean data type. This field is used by the frontend to determine which values the users want to display and what to hide. By default, it is set to 1. Take agAddressContactType in agCommunication plugin for example. User may only care for work address and not a home address. In this case, user can switch the app_display to 0 for home address address contact type. The page can can check against the app_display field before displaying any data.