Reasons for Partitioning Your Data

Partitioning splits data across multiple tables, databases, and/or database servers. There are two types; vertical and horizontal partitioning. Horizontal partitioning divides data across mutiple tables based on rows that fall within the appropriate partition rules. Each partition has the same columns, but has its own partition rule (Example: Between 01/01/2005-02/01/2005). Veritical partitioning splits the table definition into two or more tables based on the columns.

Here are some basic reasons why a system may need to have its data partitioned: Performance, Workflow, Security, & Change Tracking.

Performance: Volume
Performance issues due to data volume is the most common reason for partitioning tables. Data warehouses commonly use Horizontal partitioning to partition based on a clearly defined set of rules. If a row fits within a partition rule set, it is inserted into that partition. When defining partitions, database designers look for natural partitions for the business; Example:

· Date Range: By Year, Month, or Day
· By Database Source: Database System X, Y, & Z
· By Collecting Point: Data is collected by Nth number of data collectors
· By Business Unit
· By User
· By User Location

Workflow: Status
Workflow partitioning is used in systems like document tracking and management services. It’s a horizontal partition that partitions based on Workflow Status Rules; Example:

· (Partition 1) Draft Incomplete, Draft Complete, Draft Cancelled, Draft Approved
· (Partition 2) Active, Suspended, Expired
· (Partition 3) Archived

WorkFlow: User Type
Some of the reasons for partitioning based on Workflow Status may be based on types of users as well. Contracts are the most common managed documents and are used by several different types of users. Example:

· Sales Department
· Legal Department
· Service Fulfillment Departments
· Accounting Department

Security: PII
Partitioning data based on levels of security risk is used to Isolate PII (Personal Identification Information). Example:

· SSN
· Phone and Address
· Email
· Name
· Credit Cards and Accounts
· Passport ID

Security: Need To Know
Other type of information that may be partitioned for security reasons is information that requires a security clearance and/or a need to know.

· Medical History
· Sensitive Documents
· Trademark Secrets

Security partitions are usually horizontal partitioned using tables, databases, and/or servers. This type of partitioning lowers the security risk and increases the manageability of enforcing security requirements.

Change Tracking
Change tracking and history tracking partitions are very common in mission critical applications requiring full tracking of data changes within a database. These partitions are horizontal partitions with the addition of extra tracking attributes. These partitions are usually managed by a trigger on its corresponding master table. This trigger inserts a new row into the partition every time there is an update or delete. With this technique, it is easy to determine what changed when, and by whom.