Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    3

    ERD Design Problem( I Think)

    Im Trying to Create a Task Database. The Relation Ship i am having problems with is this.I Have a Task Table and a Person Table as well as an Assigned Table.The Rules Are Many People Can Be Assigned to one Task and One Task May be Assigned to Many People. This works Fine.
    Where i am having the problem is that one Task will have one Creator(person ) from Person Table as Well As One Person Who is in Charge Of the Task(from Person Table). Now these 2 entries could be the same person or different people.And One Person Can be either of these entries for many tasks. Currently I have 2 (1 to many) Relationships leaving the person table and going to the Task Table. Is There A way Around this?Should This Be Done?How would i Query This if for example if i wanted to get the the Last and First Names or the Creator and Person In Charge of a given Task.

    Thanks For The Help
    Jerry

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    A solution,

    Insert 2 additional columns, "created by" and "inCharge" into tasks. If there is indeed a 1:1 relationship then set a unique constraint on these fields.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Jan 2004
    Posts
    3
    I currently have these 2 fields in Task but with this situation i need to have 2 relations between Task and Person is this an ok solution.IF so how would i query this if i needed both names(inperson)of the InCHarge and Creator people fora given task?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Yes that is OK and perfectly normal. If you need to get details of both people, then join to person twice like this:

    select t.task_id, p1.name, p2.name
    from task t, person p1, person p2
    where t.incharge = p1.id
    and t.createdby = p2.id;

  5. #5
    Join Date
    Jan 2004
    Posts
    3
    Thank you soooooo much

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •