Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Linking tables when there is a multiple-field primary key

Discussion in 'Information Technology' started by rg, Jul 28, 2006.

  1. rg

    rg
    Expand Collapse
    Guest

    I was trying to figure out how to concatenate several different fields
    automatically into a separate field to make a unique key for the table. It
    was suggested that instead, I use the several fields together to make the
    key, rather than duplicating the data just to make a key field. Now I am
    trying to link to this field, and I don't know how to link to all 4 fields
    that are used for the key, instead of linking to a specific field. Is this
    possible? Should I go back to making a single field that I would then be
    able to link to? Is there a better way? Thanks for your help.
    rg
     
  2. Loading...


  3. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    > Should I go back to making a single field that I would then be
    > able to link to?


    That is my recommendation. Use a surrogate autonumber primary key, which you
    can join to a number (long integer) data type foreign key. If you wish, you
    can create a multi-field index on your four fields, with Unique set to Yes,
    to eliminate the possibility of any duplicates for the four fields taken
    together.


    Tom Wickerath
    Microsoft Access MVP

    http://www.access.qbuilt.com/html/expert_contributors.html
    http://www.access.qbuilt.com/html/search.html
    __________________________________________

    "rg" wrote:

    > I was trying to figure out how to concatenate several different fields
    > automatically into a separate field to make a unique key for the table. It
    > was suggested that instead, I use the several fields together to make the
    > key, rather than duplicating the data just to make a key field. Now I am
    > trying to link to this field, and I don't know how to link to all 4 fields
    > that are used for the key, instead of linking to a specific field. Is this
    > possible? Should I go back to making a single field that I would then be
    > able to link to? Is there a better way? Thanks for your help.
    > rg
     
  4. david epsom dot com dot au

    david epsom dot com dot au
    Expand Collapse
    Guest

    Do you mean that you are trying to create a query? You need to
    join each of the fields. Also, to get an updatable query, there
    needs to be a matching index on each side.
    On one side, the index will be the primary key index. On the
    other side, you need to create a multi-field index with the fields
    in the same order as in the primary key index.

    I normally use a number as a surrogate primary key. This is partly
    because I was a programmer before I was a DBA. Code design theory
    always uses surrogates: it's called data hiding. DB theory tends to
    favour natural keys: the whole idea of a database is to expose data
    in an efficient and natural way.

    There is no single best answer to that question. The reason people
    disagree is because there are arguments for both sides. However,
    if your problems are more on handling the data (if you have a trivial
    application or a massive data store) you would lean to the DB theory.
    If you need to do a lot of coding and re-design, you would lean to
    the coding ideas.

    Since people use Access/Jet for agile development, not massive
    transaction farms, as a group we tend more towards surrogate keys.

    Creating a surrogate key by concatenating strings is not a good
    idea. If you choose to use a surrogate key, just use numbers.

    (david)

    "rg" <rg@discussions.microsoft.com> wrote in message
    news:00156792-A64D-4847-8867-9F43701C1088@microsoft.com...
    >I was trying to figure out how to concatenate several different fields
    > automatically into a separate field to make a unique key for the table.
    > It
    > was suggested that instead, I use the several fields together to make the
    > key, rather than duplicating the data just to make a key field. Now I am
    > trying to link to this field, and I don't know how to link to all 4 fields
    > that are used for the key, instead of linking to a specific field. Is
    > this
    > possible? Should I go back to making a single field that I would then be
    > able to link to? Is there a better way? Thanks for your help.
    > rg
     
  5. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 24 May 2006 17:31:01 -0700, rg <rg@discussions.microsoft.com>
    wrote:

    >I was trying to figure out how to concatenate several different fields
    >automatically into a separate field to make a unique key for the table. It
    >was suggested that instead, I use the several fields together to make the
    >key, rather than duplicating the data just to make a key field. Now I am
    >trying to link to this field, and I don't know how to link to all 4 fields
    >that are used for the key, instead of linking to a specific field. Is this
    >possible? Should I go back to making a single field that I would then be
    >able to link to? Is there a better way? Thanks for your help.
    >rg


    As Tom and David suggest, there are better ways to do this. Building
    an "intelligent" key by jamming together several disparate fields into
    one new redundant field is probably your *worst* option.

    To use the natural key, you need all four fields in each table. In a
    QUery, you would join the first field to the first, the second to the
    second, and so on - four lines in the design window. If you want to
    change the join properties (say to an Outer Join) you need to do so
    four times, one on each join.

    Note that if you do take this option, if you haven't done so already,
    you should open the "one" side table in design view; ctrl-click the
    four fields which jointly constitute the key; and click the Key icon
    to define them as the joint four-field Primary Key.

    John W. Vinson[MVP]
     
  6. rg

    rg
    Expand Collapse
    Guest

    The natural "key" for this data would be the combination of the four fields.
    Ex.: OV.1.7.5
    This is the code used for each piece of information that will be stored in
    the table. Each of the four parts represent something specific about the
    information and I have separate tables for each part. All the options for the
    "OV" part in in one table (Strands), the "1" part is in another table
    (Standard), the "7" is the grade level, and the "5" is the specific record in
    the child table. The entire thing (OV.1.7.5) is how the information is
    identified by those who are working with my database, that's why I would like
    to make it the primary key. However, to keep from having problems when they
    enter the key wrong, I want to have the key built as they enter the separate
    pieces of info. such as Strand, Standard, Grade...
    To keep from repeating the Strand and Standard information for each record,
    I've created separate tables for each, with thier own info. which is
    referenced through the link from the child table. Maybe I'm going about this
    all wrong, it's been over ten years since I was a database designer and I'm
    finding myself quite rusty.
    -rg

    "Tom Wickerath" wrote:

    > > Should I go back to making a single field that I would then be
    > > able to link to?

    >
    > That is my recommendation. Use a surrogate autonumber primary key, which you
    > can join to a number (long integer) data type foreign key. If you wish, you
    > can create a multi-field index on your four fields, with Unique set to Yes,
    > to eliminate the possibility of any duplicates for the four fields taken
    > together.
    >
    >
    > Tom Wickerath
    > Microsoft Access MVP
    >
    > http://www.access.qbuilt.com/html/expert_contributors.html
    > http://www.access.qbuilt.com/html/search.html
    > __________________________________________
    >
     
  7. rg

    rg
    Expand Collapse
    Guest

    John,
    Please see my reply to Tom, where I give more info on what I'm trying to
    accomplish and why. I would appreciate any suggestions you have.
    -rg

    "John Vinson" wrote:

    > On Wed, 24 May 2006 17:31:01 -0700, rg <rg@discussions.microsoft.com>
    > wrote:
    >
    > >I was trying to figure out how to concatenate several different fields
    > >automatically into a separate field to make a unique key for the table. It
    > >was suggested that instead, I use the several fields together to make the
    > >key, rather than duplicating the data just to make a key field. Now I am
    > >trying to link to this field, and I don't know how to link to all 4 fields
    > >that are used for the key, instead of linking to a specific field. Is this
    > >possible? Should I go back to making a single field that I would then be
    > >able to link to? Is there a better way? Thanks for your help.
    > >rg

    >
    > As Tom and David suggest, there are better ways to do this. Building
    > an "intelligent" key by jamming together several disparate fields into
    > one new redundant field is probably your *worst* option.
    >
    > To use the natural key, you need all four fields in each table. In a
    > QUery, you would join the first field to the first, the second to the
    > second, and so on - four lines in the design window. If you want to
    > change the join properties (say to an Outer Join) you need to do so
    > four times, one on each join.
    >
    > Note that if you do take this option, if you haven't done so already,
    > you should open the "one" side table in design view; ctrl-click the
    > four fields which jointly constitute the key; and click the Key icon
    > to define them as the joint four-field Primary Key.
    >
    > John W. Vinson[MVP]
    >
     
  8. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    Dear RG:

    There are large advantages to not creating a surrogate key (autonumber).
    There is no need to change how you are doing this.

    It is perfectly possible to build your database using multiple column
    natural keys.

    When you write a query, you can JOIN on multiple columns. This is done with
    the same drag and drop interface, or with a compound ON statement. Is this
    where you're having difficulty?

    If you create the relationships between these tables, the JOINs will be
    constructed for you automatically in the Query Design View.

    In the relationship window, you can also drag and drop to create
    relationships on multiple columns.

    None of this is at all difficult. Done well, it will create a smaller,
    better performing database.

    You should have this disclaimer. This is probably the number 1 debate among
    database designers, year in and year out. Mainly, do what you're
    comfortable doing. If you're already comfortable making natural key joins,
    then by all means, keep doing it.

    In a couple of months I propose to publish what will hopefully be a
    definitive work on this subject.

    Tom Ellison


    "rg" <rg@discussions.microsoft.com> wrote in message
    news:00156792-A64D-4847-8867-9F43701C1088@microsoft.com...
    >I was trying to figure out how to concatenate several different fields
    > automatically into a separate field to make a unique key for the table.
    > It
    > was suggested that instead, I use the several fields together to make the
    > key, rather than duplicating the data just to make a key field. Now I am
    > trying to link to this field, and I don't know how to link to all 4 fields
    > that are used for the key, instead of linking to a specific field. Is
    > this
    > possible? Should I go back to making a single field that I would then be
    > able to link to? Is there a better way? Thanks for your help.
    > rg
     

Share This Page