Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2008
    Posts
    2

    Question Unanswered: avoiding composite keys

    Hi there. All I read about composite keys is that they should be avoided. I'm using access although its more of a general question, I think.
    Here is my basic problem:

    Very Basically the three tables would be Orders, Order_items, and products
    Orders would have an autonumber PK (oID)
    Products would have an autonumber PK (pID)
    Order-items would need a composite PK to be unique. It would consist of the oID and the pID.

    I understand that isn't the preferred method of doing things. This is just a small project and it won't be complicated but for the sake of experience I would like to follow best practices. How can I work around this?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by devincode
    All I read about composite keys is that they should be avoided.
    you might want to consider changing what you're reading

    composite keys are not to be avoided -- they are inherent in the data

    you might, however, wish to use an autonumber as your primary key, and use a unique index on the composite key

    nevertheless, in this particular example -- the many-to-many relationship table between orders and items -- an autonumber would be wrong

    the primary key should be the composite key consisting of the two FKs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2008
    Posts
    2
    So the way I would have done it without reading up on the subject would have been correct

    yes?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    seems it would have been

    the correct way is very often the simplest way

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

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you declare a composite unique key in Access?
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by georgev
    Can you declare a composite unique key in Access?
    yes
    IIRC been there from day one, not too sure about Access2007 though!
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    It's the same.

    All you have to do is select the fields that make up the key and hit the Primary Key command.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the only reasons I can think of as to why you may want to use a surrogate key in place of a composite key are...

    one of more of ther composite keys is liable to frequent change causing problems of updating the key in child tables, and or performance degradation as the indexes get bashed about.

    its getting complex with numerous elements comprising the key so administratively its easier to replace with a surrogate

    if you are concerned about the length of the key

    outside of that natural keys (even if they are composites) are preferable to surrogate keys in my books
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by StarTrekker
    All you have to do is select the fields that make up the key and hit the Primary Key command.
    Nonono, you're confusing a unique key with a primary key...
    George
    Home | Blog

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    Can you declare a composite unique key in Access?
    Yes. In the olden days GUI you would get to indexes via the toolbar and define composite indexes there, specifying unique.

    The correct way to do this in 2007 is to spend 10 frustrating minutes trying to find the right icon, loudly curse Microshaft and their damn new GUIs, kick the PC over, apologise to your workmates, reconnect the PC components, reboot, then execute:

    Code:
    CREATE UNIQUE INDEX myIndex ON myTable (myColOne, myColTwo)
    Last edited by pootle flump; 10-09-08 at 09:16.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ah yes, thanks for that I did confuse unique composite key with primary composite key.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Joining relational tables on 2 fields

    A little off the main post but if you're expecting fairly large recordsets (in both tables) between 2 tables joined together relationally, avoid joining them together relationally on 2 fields (ie. 2 fields in the main table joined to 2 fields in the relational table.) Multi-join or whatever the names is (I can't think of it at this moment.)

    The performance of the queries is a killer for returning data and totalling! Even making temp table routines, indexing, changing queries to snapshots, etc... or using the SQL Server driver (opposed to ODBC) doesn't help with the speed. I ended up adding some needed fields to the main table from the relational table just to help with query performance and not needing the relational join in the query (as changing the tables relational join would have required a vast amount of re-work in several areas (triggers, stored procedures, multiple interfaces, etc...)
    Last edited by pkstormy; 10-11-08 at 11:15.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Good to know... glad I haven't done it like that before
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  14. #14
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    The sad part is that "I" designed it that way. It wasn't until I started getting large datasets when I realized my flawed structure (which still haunts me in my "development" dreams to this day for that application even though I'm with a different company now.) My only compensation is that the structure is still working fairly well for them (just slow for some queries.)
    Last edited by pkstormy; 10-11-08 at 11:29.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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