How to design an approval worflow? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

How to design an approval worflow?

Author

Topic

lopes_andre
Starting Member

12 Posts

Posted - 2009-08-19 : 10:45:23

Hi,

I need to design a approval workflow for a blog.

I have 3 types of users. "Administrators", "Managers" and "Writers".

The "Managers" approve "Writers" posts and the "Administrator" approve the "Managers" decisions.

How can I design the database to handle this?

Best Regards,
André.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-08-20 : 08:34:08

You are funny.

ScottWhigham
Starting Member

49 Posts

Posted - 2009-09-03 : 09:40:47

This is a very common design requirement. Check out some of the database schema sample sites for help:

[url]http://www.databaseanswers.org/data_models/[/url]

[url]http://msdn.microsoft.com/en-gb/express/bb403186.aspx[/url]

There are tons of variations on this - anything from 1-5 tables (the table schemas below are well shortened...):

One Table:
CREATE TABLE dbo.Users (UserId, Group)

In this example (which is a bad design IMO) you are simply repeating the group info

Two Tables - Option 1:
CREATE TABLE dbo.Users (UserId, Name)
CREATE TABLE dbo.UserGroup (UserId, Group)

Again a bad design however this allows a user to belong to multiple groups

Two Tables - Option 2:
CREATE TABLE dbo.Group (UserId, Group)
CREATE TABLE dbo.Users (UserId, Name, GroupId)

A better design for when you want a user to at most belong to one group

Three tables:
CREATE TABLE dbo.Users (UserId, Name)
CREATE TABLE dbo.Groups (GroupId, Name)
CREATE TABLE dbo.UserGroup (UserId, GroupId)

A better design when you want a user to belong to multiple groups

Four Tables:
CREATE TABLE dbo.Groups (GroupId, Name)
CREATE TABLE dbo.Users (UserId, Name, GroupId)
CREATE TABLE dbo.Permissions(PermissionsId, Name)
CREATE TABLE dbo.GroupPermission (GroupId, PermissionsId)

When you want (a) users to be able to belong to at most one group, and (b) you want the groups to have complex, database-driven permissions schemes.

Five Tables:
CREATE TABLE dbo.Users (UserId, Name)
CREATE TABLE dbo.Groups (GroupId, Name)
CREATE TABLE dbo.Permissions(PermissionsId, Name)
CREATE TABLE dbo.UserGroup (UserId, GroupId)
CREATE TABLE dbo.GroupPermission (GroupId, PermissionsId)

When you want (a) users to be able to belong to multiple groups, and (b) you want the groups to have complex, database-driven permissions schemes.

And I'm sure there are tons more - anyone else have ideas?

========================================================

I have about 1,000 video tutorials on SQL Server 2008, 2005, and 2000 over at http://www.learnitfirst.com/Database-Professionals.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources