Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2008
    Posts
    3

    Unanswered: Problem in designing sequence

    I am facing a unique problem. I have three different tables say Task, StepGroup and Step. The relation between these tables is a many to many. In other words :a Task can have number of StepGroups and also a number of Steps.
    :a StepGroup can have a number of Steps and also can be in a number of Tasks
    : a Step can be in a number of Tasks and also in a number of StepGroups

    Uptill this, its fine and I am able to get things to work.
    To acomplish this I have three join tables between them.
    Now not only a Task can have a number of StepGroups and Steps. They need to be in a specified order.
    Example :
    Task1 can have--
    Step1
    StepGroup2
    Step5
    Step9
    in that order

    Task2 can have--
    Step5
    StepGroup1
    StepGroup4
    Step6
    in that order

    I am having hard time to reflect this in the database.
    One thing I could think of was to make another table in which there would
    be three columns being three foreign keys from three different table where one would make sure that either one of the step and stepgroup foreign keys would be null for a given row.And it would have an id column which would determine the order. But I dont know would it be the right thing to do?

    Could somebody suggest a good database design in order to accomplish this releationship.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    Actually it's more of a 'sequential ordering' which would have to be driven by the application.

    You could add an task_order table to your schema, maybe something like this:

    Code:
    TASK_ORDER ( 
    TASK_ID,       -- Identifies the task
    TASK_SEQ,      -- Identifies the order
    STEP_TYPE,     -- Type of task ('G'=Group|'S'='Step)
    STEP_KEY   )   -- PK to the Step/StepGroup
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Apr 2008
    Posts
    3
    Thanks for the reply LKBrwn_DBA,
    Correct me if I am wrong, I think you meant foreign key in the line
    "-- PK to the Step/StepGroup"
    This is probably the place where I am stuck. Now for this column how would I define one column having foreign keys from two tables? Also if you are into hibernate could you also tell me how to map this kind of relation?
    Secondly, I have read a lot of places, its not considered to be good design.

    When you say "application driven" , do you mean that it should manually edit this table and then manually search too while retrieving?.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Correct, not a good design but you are "mixing" keys from two tables in the same sequential ordering -- therefore you need to distinguish to which table it belongs.

    I meant by PK that the value of the column is the PK to the original table.

    An alternate design would be the 'list of materials' type table:

    task_order( key, keytype, parent_key, parent_keytype);

    Don't know or care about hibernate, just know it was (and continues to be) the cause of many developers headaches -- even causing deadlocks in production database.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Don't know or care about hibernate, just know it was (and continues to be) the cause of many developers headaches -- even causing deadlocks in production database.


    I second, this.

    Hibernate is a royal PITA to have in the environment; only to be compounded by JAVA DUHvelopers to only treat Oracle as a glorified trashcan to hold data.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Apr 2008
    Posts
    3
    Every new technology takes some time to evolve and mature.
    Don't comment on things you dont know about.
    I would say "Hibernate is like a sugar coating on a bitter pill(DBlanguages) making it easy to swallow".
    Java is the youngest and yet the most expansible language.

Posting Permissions

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