locked
Design for Person-Role-Task relationship RRS feed

  • Question

  • Hello all,

    I am trying to create a database with the following tables: Person, Role and Task.

    I need to associate each person to an event and a person can be associated to multiple events having a different role in each event.  For each role he will also have a set of tasks.  The set of tasks that he has can be marked as done.   My problem here is that I can't properly associate the person to a task in which other people can also perform the task and after the task is performed it will still be marked as undone for other people.

    In a nutshell:

    User has a role. Role has tasks.  Tasks will be associated in 1:1.  In this way all other people with a similar role will have the exact same tasklist.

    I tried creating a role then extending the role so that it will have a name of person and the association would be much simpler.  However I need to create a class for a person due to additional properties.

    I also tried creating another table that maps the relationship:

    EventRoleAssignment : EventID, RoleID and UserID

    I can get the tasks associated with a user however I cannot go further because when the one person marks the task as done all the other users will have their task done too.  I tried using virtual in C# but it does not work for me.  I'm looking for suggestions since I admit that DB design is my weakness.  Thanks

    Tuesday, December 11, 2012 10:22 AM

Answers

  • Here is the DDL version of your schema:

    CREATE TABLE Person(
     PersonID int identity(1,1) PRIMARY KEY,
     SSNO char(9) not null UNIQUE,
     fName varchar(30) not null,
     lName varchar(30) not null,
     ModifiedDate date default (CURRENT_TIMESTAMP));
     
    CREATE TABLE Role(
     RoleID int identity(1,1) PRIMARY KEY,
     rName varchar(60) not null UNIQUE,
     ModifiedDate date default (CURRENT_TIMESTAMP));
    
    CREATE TABLE Task(
     TaskID int identity(1,1) PRIMARY KEY,
     tName varchar(256) not null UNIQUE,
     CompletionTime datetime,
     ModifiedDate date default (CURRENT_TIMESTAMP));
    
    CREATE TABLE Event(
     EventID int identity(1,1) PRIMARY KEY,
     eName varchar(256) not null UNIQUE,
     eDate DateTime not null,
     eDescription varchar(max) not null,
     ModifiedDate date default (CURRENT_TIMESTAMP));
    
    -- Junction table
    CREATE TABLE ERA (
     ERAID int identity(1,1) PRIMARY KEY,
     EventID INT REFERENCES Event(EventID) ON DELETE CASCADE,
     RoleID INT REFERENCES Role(RoleID) ON DELETE CASCADE,
     [User] varchar(60),
     ModifiedDate date default (CURRENT_TIMESTAMP));
    
    -- Junction table
    CREATE TABLE AssignedTask(
     ATID int identity(1,1) PRIMARY KEY,
     Person INT REFERENCES Person(PersonID) ON DELETE CASCADE,
     TaskID INT REFERENCES Task(TaskID) ON DELETE CASCADE,
     dateDone datetime,
     ModifiedDate date default (CURRENT_TIMESTAMP));


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: SQL Programming & Database Design Using Microsoft SQL Server 2012

    Tuesday, December 11, 2012 10:07 PM

All replies

  • Since your requirements specify an attribute that is unique to each PersonTask, you will need a PersonTask relation to store it.

    Was that so hard?


    "Premature optimization is the root of all evil." - Knuth

    If I provoked thought, please click the green arrow

    If I provoked Aha! please click Propose as Answer

    We are here to learn, to share knowledge, and to earn points; all in about equal measure.

    Tuesday, December 11, 2012 10:38 AM
  • But how will tasks be automatically assigned to each person? Also how will the boolean flag for done be unique per person? Does that mean that there would be a date/boolean value per PersonTask? 
    Tuesday, December 11, 2012 12:02 PM
  • You know the business requirements, not me; so you need to answer that questions. However, let me run the following by you to assist in that analysis: List the relations in the "working" design, with their attributes:

    • Person:
    • Task:
    • Role:
    • Event:
    • PersonTask: IsDone

    Expand this structure until all required attributes are listed. Normalize this set of relations. (I don't care if you denormalize your physical table design, though I advise against it; but if you do not Normalize this Conceptual Design you simply have not completed it.)

    If you desire assistance working through this, by all means reply back. But frankly I cannot understand you rfuzzy defintions and vague definitions;  and furthermore,I BELIEVE THIS IS THE ACTUAL SOURCE OF YOUR DIFFICULTY IN PERFORMING THIS ANALYSIS.

    This strategy is nothing new to me - I frist saw it in one of Coad's books > 20 years ago. Why isn't this stuff taught anywhere?

    Note: PK for relation Person is (PersonID; PK for relation PersonTask will be (PersonID,TaskID); these are all implicit and need not be isted at this point.


    "Premature optimization is the root of all evil." - Knuth

    If I provoked thought, please click the green arrow

    If I provoked Aha! please click Propose as Answer

    We are here to learn, to share knowledge, and to earn points; all in about equal measure.

    Tuesday, December 11, 2012 12:36 PM
  • Let me reiterate it then:

    There is an event, each event has multiple roles for people.  Roles have a number of tasks.  That is one task is assigned only to one role.  A role can be assigned to more than one person.  Once the role is assigned to the person all the tasks assigned to that role are given to the person.  The person then can mark the task as done or not.

    The only solution so far that I have is to create a table that will map relationships between a role, person and event.  In this way I have covered the part where a person is already assigned to a role which varies per event.  I can pull data from the DB using queries to get which tasks he should perform.  My question here is how can I represent a boolean value that will correspond to action is done for the task in a way that multiple number of people can perform the task independently (Person A has completed the task while Person B has not yet completed the task).

    Pardon if you think that my DB knowledge is lacking which I accepted in my first post.  I am not doing this as a DB project or something.  I am merely trying to create an MVC .net C# program that might be useful for some people I know.  In this paradigm I am coding first and then let the framework create the DB for me. It just so happens that I can't figure it out as to how to use roles as something like a "profile" which would serve as basis for the tasks.  I can also do a DB first approach if this framework cannot do it for me.  It's just that I am exploring this new technology for me.

    Here are the contents of the DB as of now:

    Person:
    long id
    String fName
    String lName

    Role:
    long id
    String rName

    Task:
    long id
    String tName
    DateTime completionTime

    Event:
    long id
    String eName
    DateTime eDate
    String eDescription

    ERA (event-role-assignment):
    long id
    Event event
    Role role
    User user

    AssignedTask:
    long id
    Person person
    Task task
    DateTime dateDone

    Tuesday, December 11, 2012 1:25 PM
  • Here is the DDL version of your schema:

    CREATE TABLE Person(
     PersonID int identity(1,1) PRIMARY KEY,
     SSNO char(9) not null UNIQUE,
     fName varchar(30) not null,
     lName varchar(30) not null,
     ModifiedDate date default (CURRENT_TIMESTAMP));
     
    CREATE TABLE Role(
     RoleID int identity(1,1) PRIMARY KEY,
     rName varchar(60) not null UNIQUE,
     ModifiedDate date default (CURRENT_TIMESTAMP));
    
    CREATE TABLE Task(
     TaskID int identity(1,1) PRIMARY KEY,
     tName varchar(256) not null UNIQUE,
     CompletionTime datetime,
     ModifiedDate date default (CURRENT_TIMESTAMP));
    
    CREATE TABLE Event(
     EventID int identity(1,1) PRIMARY KEY,
     eName varchar(256) not null UNIQUE,
     eDate DateTime not null,
     eDescription varchar(max) not null,
     ModifiedDate date default (CURRENT_TIMESTAMP));
    
    -- Junction table
    CREATE TABLE ERA (
     ERAID int identity(1,1) PRIMARY KEY,
     EventID INT REFERENCES Event(EventID) ON DELETE CASCADE,
     RoleID INT REFERENCES Role(RoleID) ON DELETE CASCADE,
     [User] varchar(60),
     ModifiedDate date default (CURRENT_TIMESTAMP));
    
    -- Junction table
    CREATE TABLE AssignedTask(
     ATID int identity(1,1) PRIMARY KEY,
     Person INT REFERENCES Person(PersonID) ON DELETE CASCADE,
     TaskID INT REFERENCES Task(TaskID) ON DELETE CASCADE,
     dateDone datetime,
     ModifiedDate date default (CURRENT_TIMESTAMP));


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: SQL Programming & Database Design Using Microsoft SQL Server 2012

    Tuesday, December 11, 2012 10:07 PM