database-design

Integers vs GUIDs - The Great Primary Key Debate

One of the first steps in creating any database table is deciding what kind of data will uniquely identify a given row in said table; we call this a primary key. In modern databases, if we want to create a unique key from "thin air" and not use already-existing data (this situation is known as using a surrogate key) we have two commonly-accepted options: using integers, or using Globally-Unique Identifiers (GUIDs). Both integers and GUIDs can be generated automatically, either... Read more >

Designing a Workflow Engine Database Part 8: Complete Schema and Shortcomings

This is the final part of an eight-part series describing how to design a database for a Workflow Engine. Click here for Part 1 We've now completed the database design for our Workflow Engine, so let's take a look at the final database diagram (click here for a larger view): That's a lot of tables! But we're trying to design a generic engine, one that can be used for many disparate processes, and as such the complexity of this design... Read more >

Designing a Workflow Engine Database Part 7: Request Actions

This is Part 7 of an eight-part series describing how to design a database for a Workflow Engine. Click here for Part 1 All of the infrastructure we've built so far has lead to this moment. At long last, we can build the final piece of our schema: the Request Actions table. Request Actions So we now have Actions that Users can perform to invoke Transitions. Obviously we can't allow for any action to be performed against the Request; only... Read more >

Designing a Workflow Engine Database Part 6: Groups and Targets

This is Part 6 of an eight-part series describing how to design a database for a Workflow Engine. Click here for Part 1 We've now got most of the Process tables defined, but we are still missing a few things. One of those things is a definition for exactly who can perform Actions or receive Activities; we're going to call this Targets. The second piece we need is Groups, or collections of people that perform a similar or related job... Read more >

Designing a Workflow Engine Database Part 5: Actions and Activities

This is Part 5 of an eight-part series describing how to design a database for a Workflow Engine. Click here for Part 1 Having already defined our Process Infrastructure, our Request structure, and our States and Transitions, we can now start to design tables for what a User can actually do to a Request in this engine. For that, we will be defining two terms: Actions: Things a user can perform on a Request. Activities: Things that result from a... Read more >

Designing a Workflow Engine Database Part 4: States and Transitions

This is Part 4 of an eight-part series describing how to design a database for a Workflow Engine. Click here for Part 1 Now that we've got the Process and Request tables created, we can start building tables for the actual process itself. In this part, we'll design the tables that will hold the different States a Request can be in as part of a Process, and we'll also design the tables that show how to get from one State... Read more >