Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    Join Date
    Feb 2006
    Posts
    14

    Unanswered: Using concatenated fields as primary key - is this the best way?

    I've come up with a solution to a problem I was having linking primary keys in my database, but I don't know how good a solution it actually is - I am hoping for some sensible advice

    The problem, in a nutshell, was that I have unique three-digit numbers assigned to clients of this business, and a separate unique three-digit number assigned to the items these clients bring in for sale on Ebay. So each client will have an item 001, 002 and so forth, but there can only be one client numbered 001. Similarly, Client 001 cannot have one item numbered 001 and another numbered 001. Because I needed to link this somehow to tblListings, it caused conflicts with setting up primary key relationships.

    The other problem was that, when creating a listing, the only way to uniquely identify the item to be listed was to join the ClientID with the ItemID, in the format ClientID.ItemID. This was impossible with my original table structure, which was linking:

    tblClients.ClientID (P) to tblItems.ClientID (F) (one client, many items)
    tblItems.ItemID (P) to tblListings.ItemID (F) (one item, many listings)


    My "solution" was to set up the following:

    tblClients.ClientID (P), linked to tblItems.ClientID (F) (one client, many items)

    tblItems.ItemID (F), not linked to any other table (I figured that this couldn't be a primary key because otherwise I would be unable to have Client A.001 and ClientB.001)

    tblItemsClientItemID (P), linked to tblListings.ClientItemID (F)

    My view was to concatenate tblClients.ClientID and tblItems.ItemID through the form, but I wondered how valid/suitable this system would be, and whether there was a better way.

    Also, I was having trouble inserting the concatenation code in the Expression builder; it looked something like this:

    =Forms!frmClients!ClientID & "." & Forms!frmItems!ItemID (frmItems being a subform within frmClients)

    but it returns the error code #Name? when viewing the form. What is the correct code for this field?

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    A simple solution to this problem is to make a "junction" table (or as I like to put it: an intermediate table). Your example would be:

    Table: ClientItems

    ClientID
    ItemID
    Ndx


    Where the Ndx is an optional autonumber column that you could use as part of a (or as the ) primary key.
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    my strong advice: never declare an autonumber column in a junction table

    make the pk of the junction table a composite key consisting of both fks
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2006
    Posts
    4

    Why not the autonumber?

    I was wondering what the reason is for using the composite instead of an autonumbered column? I have several junction tables that i use an autonumber column as a primary key. Do you think I'm heading for disaster, or is this just a prefrence thing?

    george rapko

  5. #5
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    For some it is preferences, for others, it is religion

    Some actually go so far as to say that a composite primary key violates the first normal form, and that the correct primary key for any table is an Autonumber.

    But I think most of us will follow r937's advice, he's quite modest, isn't he? Here's one of his article's http://expertanswercenter.techtarget...054384,00.html - you will probably get your answer there. Unnecessary yes, but disaster no.
    Roy-Vidar

  6. #6
    Join Date
    Feb 2006
    Posts
    14

    Composite keys

    Thanks every one for your advice, I really appreciate it.

    Just a question though, r937, about your post: when using table wizards (I'm not good enough yet to use raw SQL ) what is the best way to create a composite key combining tblClientsItems.ClientID (F) and tblClientsItems.ItemID (F)? I tried to create a composite key by entering

    =[ClientID]&"."&[Item.ID] in Default Value, but it "couldn't" find the source fields.

    Assuming, of course, this is what you meant by creating a composite key - I could be barking up the completely wrong tree.

    Any suggestions?

    Quote Originally Posted by r937
    my strong advice: never declare an autonumber column in a junction table

    make the pk of the junction table a composite key consisting of both fks

  7. #7
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    No, no, no ...

    Not a concatenated monster field, a composite primary key. This is a key consisting of more than one field, NO concatenation into one field.

    In design view of this table, ensure you have both primary key fields from the other tables - clientid from tblclients and itemid from tblitems, then select both (hold ctrl while clicking the field selector at left), and hit the primary key button on the toolbar.
    Roy-Vidar

  8. #8
    Join Date
    Feb 2006
    Posts
    14

    Thankyou

    Hi Roy,

    Thanks for the clarification there, I see I've been reading too many articles on the net - I ended up confusing myself.

    I will have a play and see how it goes - thank you.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, would've answered sooner, but i didn't get the notification email

    what he said

    in Table Design View, highlight both fields, and click the little gold key on the toolbar
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by RoyVidar
    Some actually go so far as to say that a composite primary key violates the first normal form, and that the correct primary key for any table is an Autonumber.
    those people would be dead wrong about the 1NF, and terribly misguided about the autonumber

    and thanks for the very kind words, roy

    you are right, not a disaster

    but what happens if there's an autonumber pk in the junction table, how often do people also declare a unique constraint on the composite? rarely, and this leaves the door open for "duplicates"

    also, not everybody declares indexes on the FKs in the junctions table, and then they wonder why the joins take so long

    whereas with a composite pk, at least you are getting the benefit of an index at the same time
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Mar 2006
    Posts
    4
    Good point about the duplicates. Up to this point i've put it on the aplication to handle the constraint, but making the database do it should be easier and I assume quicker? You have me convinced, I'm pulling all the PKs and using the composite. What about when the table has a child like the article suggests...any opinions on that case?

    george

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    when the table has a child table, you can (usually should) still use the composite pk

    the child will have a composite fk

    a good example might be player-team

    players is a table, teams is a table, and player-team is the relationship (junction) table

    how would this table have a child table? player-team history

    example, antonio davis is now on his second tour with the toronto raptors

    the child table would have player-team as the fk, and maybe contract_date as an additional (3rd) column in the history table's pk, with termination date as a data column, which may be null

    of course, this structure is somewhat contrived, since you would then ask what is the purpose of the player-team parent table, when all the real data is in the history child

    anyhow, note that all the above was done completely without autonumbers
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2006
    Posts
    14

    A linking issue now

    I've reached that "arrrrggghhh!" hair-ripping point of the exercise, and I really need some further advice.

    I've been working with your suggestions, as you will probably see in the screen shot I've attached. My issue is that when I try to create a relationship between tblItems.ItemID and tblClientsItems.ItemID, it goes indeterminate on me. I've checked and double-checked the data types, to no avail - they are both set to Text, field size 10, input mask 0009999999 - so there appears to be no issues there. (NB: tblItems.ItemAN is an AutoNumber primary key)

    While I'm at it, if anybody could suggest any overall improvements (if needed) for the layout I currently have in place, that would be great.

    Thanks to all you dedicated db experts - you're lifesavers to the poor creative-not-lateral thinkers like me!
    Attached Thumbnails Attached Thumbnails relationships.png  

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you cannot relate tblItems.ItemID to tblClientsItems.ItemID because the latter is not the pk of its table

    perhaps you might be able to do it (i'm not all that experienced with access relationships, i do everything with sql) if tblClientsItems.ItemID were defined as unique
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Feb 2006
    Posts
    14

    Oh, dang.

    Hi again,

    Thanks for checking that. That would ordinarily be fine - except that I need to allow duplicates of ItemIDs, but prevent duplicate combinations of ClientIDs with ItemIDs. Eg, I need to allow combinations like:

    ClientID 181, with ItemID 001 (181.001) and
    ClientID 432, with ItemID 001 (432.001) and
    ClientID 678, with ItemID 001 (678.001)

    but not

    ClientID 181, with ItemID 001 (181.001) and a duplicate copy of that combination.

    Does this make any sense? I'm beginning to wonder if my plan of having a working database is slowly going awol. UNLESS there is a miracle way of developing a code to run an 'artificial' check between tables for inappropriate duplicates?

Posting Permissions

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