Pedserve Database Design

This page describes the structure and naming conventions of database tables in a Pedserve system. It is not essential that you know this, but it is useful if you want to extend the database e.g. by adding user-defined fields or records.

Databases, Tablesets and Table Suffixes

A Pedserve system consists of a MySQL database containing various tables organized into groups we call tablesets. There is a mandatory tableset called ADMIN, and then there are 1 or more tablesets holding animal data. The majority of Pedserve systems comprise a single animal tableset; multiple animal tablesets are only used when you wish to host multiple, but unrelated, sets of animals - typically different breeds - all held within a single Pedserve system.

Each tableset comprises 1 or more actual database tables. Each table is named using a consistent naming scheme - comprising the tableset name, an underscore ('_'), and then a table suffix. Table suffixes are usually 1 or 2 letters. Table suffixes and tableset names may not themselves contain underscores. E.g. a table named ADMIN_U is a table from the ADMIN tableset, with table suffix U. This is the table that holds the user login details. E.g. (2) if your system holds a pedigree database about Burmese cats, you might have a tableset called BURMESE, within which there are tables BURMESE_A (table suffix A - the actual animal records), BURMESE_I (table suffix I - inbreeding data), etc. A user-defined table is a table within an animal tableset that has a user-defined table suffix beginning with X; e.g. BURMESE_XSR could be a user-defined table containing show records.

The way to think about tablesets is to treat them as groups of related tables, following a consistent table naming scheme. The tableset is merely a concept used within Pedserve; as far as MySQL is concerned there is no such thing as a tableset - it just deals with tables.

The ADMIN tableset comprises the following tables:

Each animal tableset comprises the following tables (replace 'FOO' with the name of your animal tableset):

* The use of the family, mating, inbreeding and user-defined tables is entirely optional. Note also that they are not supported by Pedserve Standard Edition; you need Pedserve Pro, Advanced, or Developer Edition.

Field (Column) Naming Conventions

The fields (columns) within a table all have names that start with the table suffix, followed by an underscore. E.g. the users table, ADMIN_U, contains fields such as U_LOGINNAME, U_PASSWORD, etc.

Every table is expected to have a non-null, integer primary key field which must have the name <S>_ID where <S> is the table suffix. E.g. if your Pedserve system holds a database of Golden Retrievers, you might have an animal tableset called GR and therefore the table holding all the animals will be called GR_A and its primary key field will be called A_ID.

Some tables include foreign key fields - fields whose value is the primary key of some other row either in the same table or a different table. E.g. an animals table (table suffix A) contains foreign keys for the sire and the dam. I.e., columns whose values are the primary keys of the sire and dam rows. Such foreign key fields always follow a strict naming convention in Pedserve. The name of such a field is <S>_<T>ID[_FOO] where <S> is the containing table suffix (as for all fields), <T> is the target table suffix (i.e., the table suffix of the table to which this foreign key refers), ID is literally the letters I and D, and [_FOO] is an optional suffix (used generally only when a table contains multiple foreign keys to the same target table). Note that the target table may be the same as the source table; the sire and dam fields in the animals table is the primary example of this.

Here are some examples of foreign key fields:

Chained Field Names

At this point you could be forgiven for wondering why such a naming convention is used, especially for primary and foreign key fields.

The answer lies in the use of chained field names. A chained field name is a string of field names joined together by periods ('.') where all but the rightmost field are foreign key fields, and the rightmost field is a non-key field. E.g. A_AID_S.A_NAME. Or, put another way, chained field names let you refer to the field values of foreign records within the context of a 'source record'. This is perhaps best explained by an example.

Lets say you wanted to customize your Pedserve installation by adding a new display fields definition for animal search results that shows the animal's name, sex and other fields. You do this by adding a line like this to your setup script:

&pdsSetDisplayFldsDef('A_mydispflds',
                          'label=My Display Fields;fields1=;fields2=A_NAME,A_SEX,A_DOB,...');

Now lets say that you need to include the names of the sire and dam as columns in the search results. You can't just add in the plain field names - because there are no such fields: the animals table does not have columns for the names of the parents. So, how do you do it? The answer is by using chained field names, chaining through the foreign key fields for the sire and the dam. Like this:

&pdsSetDisplayFldsDef('A_mydispflds',
                          'label=My Display Fields;fields1=;fields2=A_NAME,A_SEX,A_DOB,A_AID_S.A_NAME,A_AID_D.A_NAME,...');

You can think of A_AID_S.A_NAME as meaning the name (A_NAME) of the sire (A_AID_S).

Chained field names are not limited to a single 'link'. You can link any number of foreign key fields. E.g. lets say you want to add a new display fields definition for family search results that includes columns for the coat colours of the grandparents. (We are assuming that you have already added a user-defined field for coat colour, called A_COLOUR.) You might do it like this:

&pdsSetDisplayFldsDef('F_mydispflds',
                          'label=With Grandparent Colours;fields1=;fields2='.
                          'F_AID_S.A_AID_S.A_COLOUR,F_AID_S.A_AID_D.A_COLOUR,'.
                          'F_AID_D.A_AID_S.A_COLOUR,F_AID_D.A_AID_D.A_COLOUR,...');

... where the term F_AID_S.A_AID_D.A_COLOUR means 'the colour (A_COLOUR) of the dam (A_AID_D) of the sire (F_AID_S)' i.e. the paternal grand-dam's colour, etc.

Chained field names can also be used in ordering definitions - so you can order search results according to the values of referenced fields. E.g. a chained field name is used when ordering animal search results by the sire name.

Chained field names are also used, implicitly, when you enter search terms e.g. using the advanced search screen. E.g. if you do a search for animals where the name of the sire matches a given pattern, what you are saying to the system is that you want to search a condition that applies to the A_AID_S.A_NAME chained field.

So, chained field names provide a powerful, generic, way for you to display, order, and select information from the database based on related records from different tables.

A Glimpse Inside

If you know anything about SQL databases, you will know that there is no such thing as a chained field name at the SQL level.

E.g. you probably know that whilst you can issue a database query like this:

SELECT A_NAME FROM GR_A WHERE A_NAME LIKE 'Standfast%'

... you cannot issue a query like this:

SELECT A_NAME,A_AID_S.A_NAME FROM GR_A WHERE A_NAME LIKE 'Standfast%'

... because SQL does not know what to do with the A_AID_S.A_NAME term. So instead, you have to rewrite the 2nd query to use a join, e.g.:

SELECT GR_A.A_NAME,T1.A_NAME FROM GR_A LEFT JOIN GR_A AS T1 ON GR_A.A_AID_S=T1.A_ID WHERE GR_A.A_NAME LIKE 'Standfast%'

Embedded within the Pedserve system is a database query builder that knows how to handle chained fields occurring in three different places within a database query:

• within the fields to be returned (the SELECT part), and
• within any ORDER BY clause, and
• within the WHERE clause

It mechanically constructs the required SELECT statements inserting all the correct joins etc.

What this means to you is that users can perform quite complex queries, with results ordered in various ways, bringing together data from multiple tables within a single search results screen - all things that are either not possible, or severely limited, in other, lesser able systems.

But it also means that if you have Pedserve Developer Edition, you can easily implement complex database queries. E.g. consider the following Perl code, which manually constructs a query that searches for animals whose name begins with 'Standfast', orders them by descending order of birth date and then by dam name (when the birth dates are the same), fetches the name, birthdate, sire and dam names for the located records, and then outputs them as a table:

# Start by declaring a map that we will build the query in:
my %mQuery = ();

# Initialize the query builder, telling it the table that is
# the 'root' of the query:
&pdsDbQueryBegin(\%mQuery, 'GR_A');

# Add the fields we want returning for the query:
# - name of animal
# - DOB of animal
# - name of sire
# - name of dam
&pdsDbQueryAddApplicationFlds(\%mQuery, 'A_NAME,A_DOB,A_AID_S.A_NAME,A_AID_D.A_NAME');

# Specify the order in which we want the records returned:
# we want them ordered first by descending order of DOB, and
# then by dam name when the DOB is the same:
&pdsDbQueryAddOrderingFlds(\%mQuery, 'A_DOB:desc-1,A_AID_D.A_NAME');

# Add a WHERE clause - we only want to match records where the sire name
# starts with 'Standfast'. This requires that we first 'map' the 
# chained field name 'A_AID_S.A_NAME', then add a WHERE term using the
# mapped field:
my $strMappedFld = &pdsDbQueryMapFld(\%mQuery, 'A_AID_S.A_NAME');
&pdsDbQueryAddWhereTerm(\%mQuery, "$strMappedFld LIKE 'Standfast%'");

# Finalize the query: internally this is what builds the SQL etc, but it
# does not execute the query against the database:
&pdsDbQueryFinalize(\%mQuery);

# Now run the query, populating (i) an array @aKeys with the primary keys
# of the matching records (which in this case will be the A_ID values
# because the primary key for the animals table is the A_ID field),
# and (ii) a map %mResults with the field values for each returned record.
my @aKeys = ();
my %mResults = ();
&pdsDbFinalizedQueryRun(\%mQuery, '', \%mResults, \@aKeys);

# The following code just outputs the results returned:
if (!scalar(@aKeys)) {
    print $::g_q->p("No results found");
} else {
    my $htmlRows = $::g_q->Tr($::g_q->th(['ID', 'Name', 'DOB', 'Sire', 'Dam']))."\n";
    for (my $i = 0; $i < scalar(@aKeys); $i++) {
        my $id = $aKeys[$i];
        my @ahtmlCells = ();
        push(@ahtmlCells, $::g_q->escapeHTML($id));
        push(@ahtmlCells, $::g_q->escapeHTML($mResults{"$id:A_NAME"}));
        push(@ahtmlCells, $::g_q->escapeHTML($mResults{"$id:A_DOB"}));
        push(@ahtmlCells, $::g_q->escapeHTML($mResults{"$id:A_AID_S.A_NAME"}));
        push(@ahtmlCells, $::g_q->escapeHTML($mResults{"$id:A_AID_D.A_NAME"}));
        $htmlRows .= $::g_q->Tr($::g_q->td(\@ahtmlCells))."\n";
    }
    print $::g_q->table({'-border' => 1}, $htmlRows)."\n";
}

Executing the above code results in a SQL statement along the following lines being created and executed against the database:

SELECT GR_A.A_ID,GR_A.A_NAME,GR_A.A_DOB,T1.A_NAME,T2.A_NAME FROM GR_A
LEFT JOIN GR_A AS T1 ON GR_A.A_AID_S=T1.A_ID
LEFT JOIN GR_A AS T2 ON GR_A.A_AID_D=T2.A_ID 
WHERE T1.A_NAME LIKE 'Standfast%'
ORDER BY IF(GR_A.A_DOB IS NULL,1,0),GR_A.A_DOB DESC,IF(T2.A_NAME IS NULL,1,0),T2.A_NAME

When this code is run on the Standfast Golden Retriever Database, the following HTML is generated:

<table border="1"><tr><th>ID</th> <th>Name</th> <th>DOB</th> <th>Sire</th> <th>Dam</th></tr>
<tr><td>1048984</td> <td>Hayjoy Cola</td> <td>2004-08-06</td> <td>Standfast Del Rio of Hayjoy</td> <td>Hayjoy Rosie Posy</td></tr>
<tr><td>96602</td> <td>Hayjoy Gus</td> <td>2004-08-06</td> <td>Standfast Del Rio of Hayjoy</td> <td>Hayjoy Rosie Posy</td></tr>
<tr><td>1328411</td> <td>Hayjoy Gold Charlie</td> <td>2004-08-06</td> <td>Standfast Del Rio of Hayjoy</td> <td>Hayjoy Rosie Posy</td></tr>
<tr><td>187701</td> <td>Hayjoy Matilda</td> <td>2004-08-06</td> <td>Standfast Del Rio of Hayjoy</td> <td>Hayjoy Rosie Posy</td></tr>
<tr><td>363820</td> <td>Hayjoy Flavia Fleur</td> <td>2003-10-29</td> <td>Standfast Del Rio of Hayjoy</td> <td>Hayjoy Ellie Precious</td></tr>
<tr><td>4564646</td> <td>Hayjoy Sydney</td> <td>2003-10-29</td> <td>Standfast Del Rio of Hayjoy</td> <td>Hayjoy Ellie Precious</td></tr>
<tr><td>3797503</td> <td>Hayjoy Limo</td> <td>2003-10-29</td> <td>Standfast Del Rio of Hayjoy</td> <td>Hayjoy Ellie Precious</td></tr>
<tr><td>3607376</td> <td>Hayjoy Henry in Arden</td> <td>2003-10-29</td> <td>Standfast Del Rio of Hayjoy</td> <td>Hayjoy Ellie Precious</td></tr>
<tr><td>4532723</td> <td>Hayjoy Harvey</td> <td>2003-10-29</td> <td>Standfast Del Rio of Hayjoy</td> <td>Hayjoy Ellie Precious</td></tr>
<tr><td>1136048</td> <td>Hayjoy Popsycle</td> <td>2003-02-28</td> <td>Standfast Del Rio of Hayjoy</td> <td>Hayjoy Rosie Posy</td></tr>
<tr><td>1726073</td> <td>Hayjoy Zoe</td> <td>2003-02-28</td> <td>Standfast Del Rio of Hayjoy</td> <td>Hayjoy Rosie Posy</td></tr>
<tr><td>1580711</td> <td>Hayjoy Barney</td> <td>2003-02-28</td> <td>Standfast Del Rio of Hayjoy</td> <td>Hayjoy Rosie Posy</td></tr>
.
.
.
</table>

... which looks something like this when rendered in the user's browser:

Customizing Database Tables

The files pedserve-createanimaltables-sql.txt and pedserve-createsupporttables-sql.txt contain SQL statements to create the Pedserve tables. You are expected to edit these files - to remove fields you do not want, and to add other fields you do want (i.e., to add user-defined fields).

USEFUL LINKS:
 EULA
 Pedserve Editions
 Sample City
 Consultancy
 Data Preparation
 Installation
 Regular Expressions
 Similarity Searching
 Configuration File
 Setup Script
 Stylesheet
 Database Design
 Hooks
 Date/Time
 User Defined Fields
 User Defined Records
 Display Fields Definitions
 Ordering Fields Definitions
 Highlighting
 Page Layout
 Field Definitions
 Field Formatting
 Shortcut Query Buttons
 Plates
 Command Buttons
 Connecting to the Database
 Warning Footer Message