LegalSuite Database Structure

Introduction

LegalSuite is a Windows-based program designed to assist attorneys in managing their legal practices. It is written in Clarion and .Net and uses the MS SQL database to store the program’s data. It also exposes an API to allow 3^rd party programs to access the data. This document serves to provide a brief overview of the database structure to assist those wanting to access the data from the API.

The Matter Table

An attorney typically has numerous Clients (stored in the Party table) and each Client may have one or more Matters. Most of the information regarding a Matter is stored in the Matter table, but additional Matter information may also be found in ColData, ConveyData and BondData depending on the type of Matter.

Every table in LegalSuite has a Primary Key based on an auto-incrementing integer called RecordID.

The Matter table also has a unique key on the FileRef column (which is the how the user typically identifies a Matter from the front end).

SQL: SELECT RecordID, FileRef, Description, ClaimAmount, MatterTypeID FROM Matter

Internally, the Clarion language identifies a table column by a table prefix and the column name. In the above example, the five columns are referenced as MAT:RecordID, MAT:FileRef, MAT:Description, MAT:ClaimAmount, MAT:MatterTypeID in the actual programming code. You can use this internal syntax to identify the table and the column name.

To find out the table and name of a particular database column, load the LegalSuite program and go to the relevant form and hover your cursor over an field until the tooltip appears. The table prefix and column name is displayed in the last line of the tooltip.

Tooltips contain the Table Prefix Column name of each field

In the above example, MAT:FileRef indicates that this data is stored in the Matter (MAT:) table and its column name is FileRef.

The Party and MatParty Tables

All Parties are stored in the Party table.

A Client is a Party who plays the role of Client on a particular Matter.

The Party table also has a unique key on the Codecolumn (which is the how the user typically identifies a Party from the front end).

SQL: SELECT RecordID, Code, Name, VatNumber, PartyTypeID FROM Party

Matters typically have a number of Parties (Matter Parties) associated with it which represent the Parties involved in the Matter.

For example, John Smith may be the Client, Absa Bank may be the Defendant and John Smith may also be the Plaintiff. In other words, a Party can play more than one role on a Matter.

The Matter’s Parties are stored in the MatParty table which is a joining table between the Matter and Party tables.

SQL: SELECT RecordID, MatterID, PartyID, RoleID FROM MatParty WHERE MatterID = 1

The RoleId column identifies the role the Party plays on the Matter. In the above example, RoleID = 1 is the Client role, RoleID = 2 is the Plaintiff role and RoleID = 3 is the Defendant role. Roles are stored in the Role table.

SQL: SELECT RecordID, Description FROM Role

Related Tables

In a relational database, tables are linked (or related) to each other. This is an important concept and vital to understanding the layout and relationship of the tables used by the LegalSuite program.

The RecordID column is typically hidden from the user’s view and they are usually unaware of it. It is very important, however, from a database point of view as it serves to:

(1) Uniquely identify each record.
(2) Link related tables to each other.

In the above example, you will notice that the Matters, FNB1/0003 and FNB1/0004, both have the same ClientID (12). That is because these Matters both belong to the same Client (in this case, FNB).

FNB’s details are not stored in the Matter table – this would be highly inefficient because if FNB’s address changed, for example, we would have to change their address in every record in the Matter table! In database parlance, this is called normalization and LegalSuite strictly conforms to database normalization.

Instead of storeing FNB’s details in the Matter, we record that these two Matter records have a ClientID = 12 and so FNB’s details are stored, once, in the Party table (which is called the Address Book from the user’s point of view).

You will notice that FNB has a RecordID = 12. This is how the Matter table is linked to the Party table: MAT:ClientID (12) = PAR:RecordID (12).

The Party table is linked to the Matter table via PAR:RecordID = MAT:ClientID

This is called a many-to-one relationship because many Matters can be linked to one Party record (e.g. we could have thousands of Matters with a ClientID of 12 but we will only have one Party record with a RecordID of 12). The Party table is the Parent table and the Matter table in this case would be the Child table.

Parent and Child Tables

Parent tables are those which have tables linked to them in a one-to many relationship. The Matter table is often the Parent to numerous child tables. For example, the File Notes, Fee Notes and Reminders are all children of the Matter table.

The FeeNote table is linked to the Matter table by FN:MatterID = MAT:RecordID

Notice that the link is based on the MatterID of the FeeNote (FN:MatterID) and the RecordID of the Matter (MAT:RecordID). This is a common pattern used in LegalSuite.

Here is a list of the main tables and their prefixes:

Party Relationships

Party Relationships

Matter Relationships

Child tables linked to a Matter

Conclusion

The LegalSuite database comprises of over 200 tables, but the core of the program centres around the Matter and Party tables (and their parent tables and child tables).

With an understanding of how these relate to each other, a 3^rd party developer can access most of the most critical data required by a client.