Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2011
    Posts
    4

    Avoiding data duplicate

    I have a part table that stores parts data. the table looks like this (simplified version):
    table part
    id
    partcode (this should be unique)
    description

    information about partcode can come from two or more different sources. let's say vendorACode, vendorBCode and vendorCCode catalogues.
    It often happens that user refers to the same part using different part code. Let's say part P is identfied as x01 by vendorACode, y02 by vendorBCode, and z11 by vendorCCode catalogues (names are arbitrary).
    So the problem is that user inputs part P using vendorACode catalogue. And another user inputs the same part using vendorBCode catalogue without knowing that data about part P is already stored in the table, resulting in data duplicate.

    My immediate answer to this proble is to create another table that acts as an alias (part_alias), where one record in part table maps to one or several records in part_alias (one to many relationship).
    How can I solve this problem ?
    Any help would be greatly appreciated..

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by groove View Post
    table part
    id
    partcode (this should be unique)
    description
    if you want partcode to be unique, all you have to do is ~make~ partcode unique by declaring a UNIQUE constraint on it

    problem solved

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

  3. #3
    Join Date
    Jul 2011
    Posts
    4
    I'm afraid the solution is not that simple.. what if two unique values of partno refer to the same part (object)..??

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    you can't have it both ways
    Quote Originally Posted by groove View Post
    partcode (this should be unique)
    AND
    Quote Originally Posted by groove View Post
    I'm afraid the solution is not that simple.. what if two unique values of partno refer to the same part (object)..??
    either partcode IS unique, or it isn't.
    if it isn't then you may need to define another mechansim to make it unique. the only way I can see of doing that is if the partnumber is supplied from one or more suppliers and each supplier uses the same partcode for an equivalent part.

    in that event the partcode would still be unique, but you would need a table to identify suppliers and an intersection tabel to associate suppliers with specific partcodes.


    incidentally I'm surprised to find an ID column on what you pass off as a unique part, surely the partcode by itself is unique, or are you changing the rules (again), or merely being frugal with the business requirement
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    This is a standard business requirement. Our ERPS meets it with intersection tables that hold the SKU ID value, customer/supplier ID value and the third party's SKU ID value.

    We also get around potential duplication of SKUs by only allowing one department to allocate and enrol new SKUs, but that's more of a business process than a feature of the database.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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