Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2005
    Posts
    22

    Unanswered: How to limit duplicate Issues in table design

    I'm working on a issues tracking database.

    Here are the fields in the issues tables:
    ISSUES_ID PK
    ATM_NUMBER FK
    ISSUE_TYPE
    ISSUE_DATE_REPORTED
    ISSUE_PERSON_REPORTED
    ISSUE_STATUS
    ISSUE_DATE_RESOLVED
    ISSUE_COMMENTS
    ISSUE_DATE_MODIFIED

    Also there is a table called ATM_INFO, which has all of the information about the ATM_NUMBER. A ATM_NUMBER has to be entered in the ATM_INFO before the enter anything in the Issues table.

    I would like to eliminate the possiblility of entering in duplicate issues for a specific machine. But the issues table tracks many different kinds of issues so there is a 1 to many relationship between the ATM_INFO and ISSUES tables. So I cannot restrict the FK to no-duplicates in the design. Is there another way of eliminating a way of entering in duplicate issues in this table.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    think about what combination of column values will uniquely identify an issue

    that's all there is to it

    hint: leave ISSUES_ID completely out of consideration for the moment

    what makes one issue different from another?

    what identifies a unique issue?

    i'm guessing that this should do it --
    ISSUE_TYPE
    ISSUE_DATE_REPORTED
    ISSUE_PERSON_REPORTED
    (assuming that DATE_REPORTED is granular enough, e.g. if more than one issue of the same type can be reported by the same person on the same day, then DATE_REPORTED should be datetime, so that they can be distinguished from each other)

    once you have identified a candidate key (which is this combination of columns that uniquely identifies each issue), declare a unique constraint on it

    in microsoft access, i'm not sure how to do it via the table gizmo, but i think i can do it with sql

    anyhow, the point i'm getting to is that this unique constraint is a natural key, whereas your ISSUE_ID is a surrogate key (and probably an autonumber to boot)

    a natural key is often the best primary key, although in the real world most people routinely use a surrogate key instead

    when using a surrogate key, don't forget how easy it is to create "dupes" so pay attention to what the natural key actually is

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by r937
    ........in microsoft access, i'm not sure how to do it via the table gizmo, but i think i can do it with sql.....
    select the columns you want to make into the primary key, and then select the key button on the toolbar
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Mar 2005
    Posts
    22
    thanks for helping me out with this design issue. I know of natural keys, but as you stated they are not used very often. Both replies were appreciated.

    Just another question, what is the best method to help identify natural keys. Maybe point me in some information so I can further research this topic.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by healdem
    select the columns you want to make into the primary key, and then select the key button on the toolbar
    but doesn't that simply make the selected column(s) the primary key?

    what if a column such as ISSUES_ID (autonumber) is already the primary key? how do you then declare a unique constraint on some other set of columns?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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