Definition of a Relational Database according to dictionary.reference.com
An electronic database comprising multiple files of related information, usually stored in tables of rows (records)
and columns (fields), and allowing a link to be established between separate files that have a matching field
so that the two files can be queried simultaneously by the user.
Oracle's Definition of a Primary Key
In Oracle, a primary key is a single field or combination of fields that uniquely defines a record.
None of the fields that are part of the primary key can contain a null value.
Oracle's Definition of a Foreign Key
A foreign key is a way to enforce referential integrity within your Oracle database.
A foreign key means that values in one table must also appear in another table.
The referenced table is called the parent table while the table with the foreign key is called the child table.
The foreign key in the child table will generally reference a primary key in the parent table.
Students
ID (Primary Key)
5
DCID
2
Last_Name
Adair
First_Name
Brandon
Storedgrades
ID (Primary Key)
2587
StudentId (Foreign Key)
5
Grade
A
Storecode
S1
Course_Number
MAT1000
Note: The ID field in the Students table is not technically the primary key, but it is a unique identifier
in the table that can be used to establish table relationships.
The Old Way - Custom Fields
Students
Last_Name
Adair
First_Name
Brandon
DCID
2
ID
5
CUSTOM
Medical Notes; Allergic to peanuts, 546,Mr. B, 78489635, 555-743-5547, contact_1_eamil; contact1@email.com,
contact_2_email; contact2@email.com, contact_1_employer;Wal Mart, contact_2_employer; Lanham Law Offices
7875 West Greenway Blvd. Middleton, WI 53562
parent_notes; Brandon is only allowed to be picked up from school on Wednesdays by his father.
With custom fields, all custom data is stored in a single field in the core table.
The New Way - Database Extensions
Students
Last_Name
Adair
First_Name
Brandon
DCID
2
ID
5
U_Def_Ext_Students
StudentsDCID
2
Contact1_Email
contact1@email.com
Contact2_Email
contact2@email.com
Contact1_Employer
Wal Mart
Conatct2_Employer
Lanham Law Offices
Medical_Notes
Allergic to Peanuts
With database extensions, users can define their own tables and fields in the relational database.
This method of storage makes data retrieval exponentially faster.
System > Page and Data Management > Manage Database Extensions
There are four steps to creating a database extension.
Step 1 - Choose the core table that will be extended (the table for which we are creating or migrating a field).
Note: Here, the user can also choose Basic or Advanced Extensions. Choosing the Basic option will automatically select default options
for steps 2 and 3, bringing the user directly to step 4.
Step 2 - Choose or add a database extension group. The database extension group is simply a name given to a group of database extended tables.
Many users choose to use a single extension group for all custom fields. Others choose to create different groups to compartmentalize data
(i.e. demographic data and contact data). Either approach is valid.
U_Students_Extension is the default extension group when extending the Students table.
This is the option that will be used if the Basic Extension type is selected in step 1.
Step 3 - Choose or add a database table. Each extension group can contain multiple tables. There are two types of tables.
One-to-One tables hold data that can be defined only once for a student. These are the tables that will hold new or migrated custom fields.
One-to-Many tables can have multiple records for a single student. Examples of these types of tables in core PowerSchool include CC, StoredGrades, and Log
U_Def_Ext_Students is the default extension table when extending the Students table.
This is the option that will be used if the Basic Extension type is selected in step 1.
Step 4 - Create new field for the selected table.
Here the user can create new fields. When the Add button is clicked, the Add Field dialog will appear.
Important notes about field creation
Once a field has been created, it cannot be modified. Take time to consider names, data-types, and lengths carefully before submission.
Leave the 'Migrate Data From' dropdown empty to create a new field.
If migrating a new extended field will be created and all data from the custom field will be migrated into the newly-defined extended field.
Fields can be renamed when migrating. PowerSchool maps previous names to the new extended names to ensure that no custom pages or queries will break.
Data Migration Tool
System > Page and Data Management > Custom Field Data Migration
In addition to migrating field individually, users have the option to migrate all fields at once using PowerSchool's Custom Field Migration Tool.
This process is naturally much faster than migrating fields individually, but the user loses the ability to rename fields and define data-types.
When using the migration tool, all custom fields are copied to and extended field of the same name with type - String and length - 4000.
In addition to migrating user-created custom fields, the migration tool can be used to migrate core custom fields. These are core PowerSchool fields
which have been implemented using the custom field framework.
Note: As indicated by the warning at the top of the migration screen, any custom fields that have over 4000 characters will be truncated.
This situation is unlikely, but the Migration Report (Step 2) will indicate if this situation does exist for any field, allowing the user to make
any necessary modifications prior to migration. If no data in excess of 4000 characters is found, step 3 can be skipped.
Advanced Extensions (One-to-Many Extensions)
Library System Example
Here we will explore advanced database extensions by creating tables and pages to track library books, checkouts, and returns.
The solution includes two extended tables.
U_Lib_Book - Records for all library books.
U_Lib_Checkout - Records of students checkout and return of library books.
Both tables will be created in a dedicated Extension Group called U_Library
U_Lib_Book Table Setup
System > Manage Database Extensions
Step 1 - Choose other from the first selector and then click the "Add Independent Table" button.
Step 2 - Add an extension group.
Step 3 - Add the U_Lib_Book table.
Step 4 - Create the needed fields, and submit the extension.
U_Lib_Checkout Table Setup
System > Manage Database Extensions
Here, we will create the table to store checkouts as a child table of the Students table.
Step 1 - Choose Students and Advanced Extension.
Step 2 - Click the radio button to 'View all user created database extension groups'. Next select the extension group we created earlier (U_Library).
Step 3 - Add the U_Lib_Checkout table. "Can have multiple records" must be checked so a one-to-many table is created.
Step 4 - Create the needed fields, and submit the extension.
A Look at the Tables
Below are the tables that we have created.
In addition to the fields that we defined when creating the tables, PowerSchool creates an ID field as well as a foreign-key
(StudentsDCID) for the checkout table.
The WHO and WHEN fields are audit fields which are automatically created as well.
Accessing One-to-Many Extensions in Custom Pages
Direct Table Access Tag
~[DirectTable.Select:<tableName>;<pkColumn>:<pkValue>]
Example: ~[DirectTable.Select:U_Lib_Book;id:104]
tableName - Name of the extended table.
pkColumn - Primary key column of the extended table.
pkValue - Primary key value of record being updated in extended table (-1 to create new record)
Once the Direct Table Select tag has been included in a page, extended table fields can be referenced with the same syntax used
to access core table fields.
<input type="text" name="[tableName]fieldName"/>
Example: <input type="text" name="[U_Lib_Book]title"/>
Syntax to delete an extended table record.
<input type="hidden" name="DD-<tableName>.<idColumn>:<idValue>" />