Results 1 to 5 of 5

Thread: Relationship

  1. #1
    Join Date
    Jul 2010
    Posts
    5

    Question Unanswered: Relationship

    Hey I need help with a database im trying to do

    ok I have a table name DOCUMENTS and in this table there is a few attribute to the table but some of them change depending on what MARKET wants them so I have the table DOCUMENTS---DOCUMENTS_MARKET--MARKET
    (many to many relatioships) now in my DOCUMENTS_MARKET I have the 2 primary keys from this 2 other table it works but then when it comes to joining DOCUMENTS_MARKET to some other tables of the database Im not sure how to do it because there is 2 primary keys thanks aloottt

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The simplest way of achieving that would consist in adding a surrogate primary key (Autonumber) to the table DOCUMENTS_MARKET and using it to create links with other tables. Otherwise, you'll have to work with a composite key made of the two foreing keys already stored in that table.
    Have a nice day!

  3. #3
    Join Date
    Jul 2010
    Posts
    5
    Thanks alot Sinndho.. but can I use an autonumber without making it a primary key because if I do make it a primary key there could be 2 documents and market of the same name. thanks

  4. #4
    Join Date
    May 2010
    Posts
    601
    I agree with Sinndho.

    Quote Originally Posted by emy View Post
    Thanks alot Sinndho.. but can I use an autonumber without making it a primary key
    because if I do make it a primary key there could be 2 documents and market of the same name. thanks
    That is not true.

    An autonumber data type field does NOT have the be the primary key. It just normally should be the priamary key.

    ... now in my DOCUMENTS_MARKET I have the 2 primary keys ...
    Not possible.

    A table can only have a single primary key! You primary key can be based on multiple fields (composite/compound key)

    I avoid every having a primary key that uses multiple fields. It usually causes lots of headaches. I have rarely seen when it is required.

    I would make an index on the two fields (composite/compound index) and set the index to not allow duplicates. Also set both fields to required.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    Join Date
    Jul 2010
    Posts
    5
    Oh really thanks ! Thats true.. for some reasons i thought when you had a many to many relationship.. the table in the middle needed to have the 2 primary keys from the other tables as primary keys... But yes you must be right it makes sense

    but i dont think it fix my problem umm..

    DOCUMENT A can be buy by MARKET A
    DOCUMENT A can be buy by MARkET B
    DOCUMENT B can be buy by MARkET A

    then the document A bought by market A is different than the document A bought by maket b.. it depends on the Market rules.

    and then i have to join this "new " documents to other tables

    so if i set the index as no duplicate its not gonna work.

    it gets complicated ive been working on this for a while now

Tags for this Thread

Posting Permissions

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