If your organization is building a practice management system, the following guidance will help you build a future-proof application that will not need constant maintenance and refactoring as your client base grows.
Database Design
Use "long" Primary Keys
Avoid string values or guids as these often lead to performance issues and index fragmentation.
Avoid composite keys.
Use Relational Databases
Relational databases with strict schemas do a great job of keeping bad data out of your application.
On No-SQL/JSON/XML-based databases, we often see that it is easy for XML/JSON data to be stored that is in the incorrect schema or of an incorrect type. (For example, it is easy for the boolean value true to accidentally be stored as the string value "true".
Generally Choose *-to-Many Relationships
One of the most common areas that practice management vendors have to refactor as their client base grows is the cardinality of relationships.
For example, many systems start with only allowing a Contact record to have:
- 1 Email Address
- 2 Phone Numbers (Home/Work)
- 1 Postal Address
But then later need to refactor their app to support contacts with any number of email addresses, phone numbers, and postal addresses.
Plan for Hierarchical Category Objects
There are many different object types that can be through of as categories such as:
- Practice Areas
- Contact Types
- Document Types
- Calendar Event Types
- Task Types
When building your database, you should allow nested categories. For example, you should support the following:
- Practice Area: Family Law > Divorce > With Children
- Contact Type: Providers > Medical > Pediatric
- Document Types: Motions > Private Process
- etc.
Minimize *Required* Inter-Object Dependencies
As you build your application, you should do so in a way that makes as many relationships optional as possible.
For example, your application will likely have contacts, matters, practice areas, and documents.
You should be able to:
- Store a matter without having a client
- Store a document without associating it to a matter.
Plan for Notes on Any Object
Many new-to-market systems start out with:
- a MatterNotes table.
- Then they add a ContactNotes table
- and an InvoiceNotes table
- and a DocumentComments table
- and a DocumentVersionComments table
- then a TimeEntryInternalComments table
- and the list goes on.
Instead, create a Notes table that supports attaching notes to any kind of object.
Plan for Custom Fields on any Object and of any Object Type
Many new-to-market systems start out with:
- Supporting custom text-line fields on matters
- Then they add custom integer fields, custom checkbox fields, custom money fields, custom decimal fields, and custom text-area fields, and custom URL fields.
- Then they add custom picklist fields, custom checklist fields
- Then they allow custom fields to be attached to contacts
- Then they allow custom fields to be attached to documents
- Then they allow a custom field to be a contact-picklist, matter-picklist, user-picklist or document picklist.
- Then they allow a custom field to be a task picklist, a calendar entry picklist, and more.
Avoid all that refactoring and build a database that supports all of the above.
Always Soft-Delete
All records (contacts, matters, notes, etc.) should have one of the following statuses:
- Active
- Archived
- Draft
- SoftDeleted
When records are soft-deleted, it allows you to solve two problems:
- If the customer deletes something unintentionally, restoring is easy.
- When a consumer of your API is synchronizing, it is easy to detect soft deletes but very hard to detect hard deletes.
- For example, if a record has been soft-deleted, a developer can tell your API "Show me anything that has been modified since my last synchronization, including deleted records". If you only support hard-deletes, then the request will be "Give me everything so I can detect what you've deleted."
All records should have the following fields:
- Created_At / Created_By
- Updated_At / Updated_By
- Deleted_At / Deleted_By
Reference Codes and DisplayOrders
In many systems, objects have ReferenceCodes and DisplayOrders.
You should support these on all object types.
ReferenceCodes are non-numeric identifiers/abbreviations used by humans to identify a record.
Some examples of reference codes are:
- Matter "Numbers" (they're not numbers)
- Invoice "Numbers" (they're not numbers)
- Document Type Codes (ie. MOT = Motion)
- Practice Area Codes (ie. FAM = Family Law)
DisplayOrders are human-assigned custom sort orders.
Some examples of DisplayOrders being used are:
- Custom Sorting Practice Areas
- Custom Sorting Picklist Items
Don't Store People and Companies in Different Tables
Some systems do this. It causes issues for their customers because if a contact has the wrong type, it can't easily be changed.
Instead, use one table, and have a Kind property that indicates whether the contact is a Person or a Company.
You should not have different tables for Clients, Vendors, Opposing Parties, etc.
Instead, you should implement ContactCategories and allow a contact to be associated to 0 or more ContactCategories.
Design Rate Classes Correctly
A lot of new-to-market systems really mess up their rate class implementation.
- They will start with users having a default rate.
- Then they'll allow per-client overrides
- Then they'll allow per-matter overrides
This creates significant problems for firms because they lack a global view of their firm's rates and any customizations have to be manually implemented in mass. (For example, imagine having to manually change the rates on each one of AAA Insurance's matters to the negotiated 2025 rates.)
A good rate class implementation should consist of two object types:
- Rate Class
- Rate Class Rule
The Rate Class object should have the following properties:
This way rate classes can be nested inside of each other like this:
- Default Rate Class
- Family Law
- Litigation
- Insurance Defense
- AAA
- Customized Rates
- Smith, John
- Doe, Jane
- JD.001
- JD.002
The Rate Class Rule object should have the following properties:
- RateClassId
- DisplayOrder
- Date_From
- Date_Till
- User/Group/Activity/UtbmsCode
- Rate
- RateType (Hourly/Fixed)
In this way the RateClassRules support the following "statements":
- The rate for Paralegals is $100/hr
- From 2022-2025, the rate for Paralegals is $150/hr
- From 2022-2025, the rate for Jessical Paralegal is $175/hr
- All A102 work is $200/hr
It should be possible to link a contact and a matter to a rate class.
When determining the rate for an activity:
- First, find the rate class to use.
- If a Matter has a rate class, use that rate class
- Otherwise, fall-back to the Client's rate class
- Otherwise fall back to the Default Rate Class
- Then Find the Rate Class Rule to use
- Then make a list of all of that rate class's parent rate classes in reverse order
- ie: AAA < Insurance Defense < Default Rate Class
- Starting with the current rate class, (ie. AAA)
- Get a list of that Rate Class's rules, sorted by Reverse DisplayOrder.
- For each rule, if the rule applies, set that rule as the Matching Rule and then stop searching
- Compute the rate using the Matching Rule.
App Design
Push - Don't Pull
The best applications store all their data in their own database. They may push data to remote systems and utilize data in the remote system, but they never treat that remote system as the source of truth.
For example, if you are using a permissions API, your app should store all the data in your database and then push the relevant information into the remote system. Once the data is pushed, you might query it directly from the remote system, but, in a disaster situation, you should be able to delete the remote system and rebuild it from the data in your database.
Documents, Versions, and Sharding
In your app, you will likely store documents, and their revisions in Amazon or Azure. Whatever system you choose, your database should have its own documents and versions table: you should not rely on Azure or AWS's built-in versioning system.
When you store document versions, they should be stored with zero important metadata on them. Your database should have everything it needs in order to directly download a document without needing to query anything in the underlying storage system.
For example, you might have a document version stored at any of the following locations:
- SuperSecureDocs.s3.AmazonAws.com/1/2024-03-1/1/2/3/4
- SuperSecureDocs.s3.AmazonAws.com/1/3/2/7/1/1
- SuperSecureDocs.s3.AmazonAws.com/1/5455bcdd-329d-4388-97cd-bed453b09d92
You'll notice that the document blob has no extension on it or anything.