As I continue to refine my thoughts on an ERP system, I have come up with an idea for a database schema. The system will require that end users be able to customize the data entities they are storing. I am using the metaphor of a Work Ticket to describe these entities. Each ticket will have some common fields that integrated it with the overall system but most of the fields will be customized to each different kind of ticket.

So, I started with three tables (pictured in blue) in the database. First is the Project table to hold the details of an individual project. I am sure it will need more columns than are currently pictured. Second is the Work Ticket table. This table will be the primary data storage of work tickets. And last is the Ticket History table to hold the revision histories of the work tickets.

Next, I added a set of tables to define the customizations to the tickets. Each record in the Ticket Definition table will define a new kind of ticket. The customizations will take the form of fields added to the ticket. The fields are defined in the Field Definition table. The Field Usage table specifies which fields appear on which tickets and the validation rules for that instance of the field.

The idea is to have the system dynamically add new, strongly typed columns to the Ticket table for each Field Definition. But, this would add a large number of NULL values to the database, so I intend to reuse the columns for multiple ticket definitions. For example, if we add an INTEGER field called Estimate to TICKETA then the system will insert a new column into the Ticket table called INTEGER001 to hold the value of the Estimate field. If this column only held the Estimate field, it would be NULL on all the Tickets of any type other than TICKETA. Instead, we will reuse the column. Let's say we added a second ticket definition called TICKETB. TICKETB also has an INTEGER column but it is called RISKLEVEL. The definition of TICKETB can now map the RISKLEVEL field to the the first available integer column. In this case, the system would find the column INTEGER001 and use it for this field.

Reusing the fields should keep the database much more compact and efficient. I will just have to make the ORM layer automatically create strongly typed fields on the objects that transparently persist to the correct fields in the Ticket table.

The last section of the diagram is the workflow definitions. I am assuming that tickets will require some business process and have a defined life cycle. So, we have the Workflow table to define a state machine workflow for each ticket definition. The workflow consists of a set of States, with one defined as the initial state. Each state has a set of Transitions that define the states the ticket can move to from the state it is in now. Each transition will also have Transition Constraints that implement specific business rules during the workflow execution.