Results 1 to 4 of 4

Thread: index question

  1. #1
    Join Date
    Jan 2004
    Location
    Houston, TX
    Posts
    29

    Unanswered: index question

    I have a table [tblDates] which stores dates and a date type.

    tblDates
    DateID(key)
    ProjectID
    DateTypeID
    Date1
    Date2
    Date3
    Date3

    Each record in this table represents a unique set of dates in the project review cycle. Thus each record is assigned a datetypeID (1- initial review; 2 - continuing review, 3 - final review). My dilemma arises in that each projectID will have multiple date sets (M-1 relationship with project table) however the date type of "initial" can only be used once, but the other can be used many times.

    My question is without using a (no duplicates) index on the DateTypeID field how can I make sure that each ProjectID has only one DateTypeID (1-Initial Review) record.

    I hope this is not too confusing.

    MrResearch

  2. #2
    Join Date
    Feb 2004
    Location
    Houston, Texas
    Posts
    45
    MrResearch,
    Wouldn't extending the key to include DateID, ProjectID and DateTypeID have the desired effect?

  3. #3
    Join Date
    Jan 2004
    Location
    Houston, TX
    Posts
    29
    Originally posted by rtkracht
    MrResearch,
    Wouldn't extending the key to include DateID, ProjectID and DateTypeID have the desired effect?
    No, it wouldn't. Lets think of these three fields as a triad
    (DateID, ProjectID, DateTypeID).

    In your suggestion the triad (1,1,1) would be a unique value. I agree with this and that would be fine except.... (2,1,1) would also be unique. I am looking for a way to prevent the following secnario:

    (1,1,1)
    (2,1,1)
    (3,1,1) and so on.

    I only want the (?, X, X) combination to happen once. Thus an acceptable scenario would be:

    (1,1,1)
    (2,1,2)
    (3,1,2)
    (4,1,2)
    (5,1,2)
    (6,2,1)
    (7,2,2) and so on. This is because the DateTypeID of "1" represents the inital review, and there can only be 1 intital review. However all other DateTypeID's can occur any number of times.

    -MrResearch

  4. #4
    Join Date
    Jan 2004
    Location
    Houston, TX
    Posts
    29
    I guess I should clarify further.

    For each project ID I want the Project ID and DateTypeID of "1" to happen one time.

Posting Permissions

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