SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Can you use Unique Indexed Fields for Joins?

    I'm working on a new Data Model, and am leaning towards using "Natural Keys".

    This seems to be a good move, except in one area...

    I have an "Article" table, and I think it would be awkward to use Article Slug's as the primary keys.

    So could I have an "id" field using Auto Increment, and use that as my Primary Key. And then add a Unique Index to my "slug" field, and use that when I want to join to the "Article" table?

    For large tables, I just think its nice to have a numerical ID that you can easily refer to if needed...

    Sincerely,


    Debbie

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    if slug is "unwieldy" then an id makes sense

    joining other tables to the article table could be done on either the PK or UK

    defining the FK in other tables, though, i would use id, not slug
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    if slug is "unwieldy" then an id makes sense
    I wouldn't say "unwieldy". (An Article Slug should never change once it is created.)

    I just want an ID for easy reference.

    (It is easier to look for and refer to an Article by "ID=9327" than to search through 10,000 records looking for "SLUG = "does-outsourcing-make-sense-for-small-businesses.php")


    joining other tables to the article table could be done on either the PK or UK

    defining the FK in other tables, though, i would use id, not slug
    Okay, but the other issue I am trying to avoid is this...

    If I go the "Natural Keys" way, then I will do that almost entirely for readability.

    If I can take a Junction Table - and by using "Natural" Foreign Keys - and get this...
    Code:
    Legal		Featured Legal			Litigation		WhenASoleProprietorGetsSued.php
    ...then having "Natural Keys" just saved me some work.


    But - as above - if I insist on wanting an "ID" using AutIncrement to easily reference each Article, but I follow your advice of using an ID for the Foreign Key, then my Junction Table would degrade to this...
    Code:
    Legal		Featured Legal			Litigation		9327

    And then I'd have to do a join to get human-readable results, at which point I might as well scrap using "Natural Keys", and go with "Derived Keys" all the way around?! (I'm probably being extreme to make a point, but I'm sure you follow me.)

    Sincerely,


    Debbie

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    ...I'm sure you follow me.
    yes, i do

    okay, now consider this... you're preparing a list of articles (say, in response to a clickthrough on category or tag or whatever) and you want not only the slug but the date of last revision (or some other column from the articles table)

    you'll have to do the join after all, and in that case, the id is more efficient as an index
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yes, i do

    okay, now consider this... you're preparing a list of articles (say, in response to a clickthrough on category or tag or whatever) and you want not only the slug but the date of last revision (or some other column from the articles table)

    you'll have to do the join after all, and in that case, the id is more efficient as an index
    Okay, so I'll counter with an ERD, and you can tell me what you think...

    Code:
    SECTION -||----|<- SECTION_DIMENSION ->|------||- DIMENSION
    
    DIMENSION -||-----|<- DIMENSION_SUBSECTION ->|-----||- SUBSECTION
    
    SECTION_DIMENSION -||-------|<- ARTICLE_PLACEMENT
    
    DIMENSION_SUBSECTION -||-------|<- ARTICLE_PLACEMENT
    
    ARTICLE -||-------|<- ARTICLE_PLACEMENT
    (BTW, I spent 2 weeks ultimately coming up with this Data Model, it meets all of my fairly complex Business Rules, and it has been tested and works as it should!!)


    *IF* I chose to use "Natural Keys", it would save me joins above, and make junction tables like ARTICLE_PLACEMENT useful all by itself, because it would look like below which would be handy in building my Pretty URL...

    Code:
    legal		sole-proprietorship	when-a-sole-proprietor-gets-sued.php

    I am currently pouring over my Data Model and debating the whole "Natural Key" versus "Derived Key" thing now.

    But if I ever do use "Natural Keys", I still want an AutoIncrement ID in the field in the record, however I don't want to pollute a Junction Table with an "ID" because then it defeats a key purpose of "Natural Keys", which is to improve readability while reducing joins...

    Not sure if that helps?


    Debbie

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Okay, so I'll counter with an ERD, and you can tell me what you think...
    very nice, i'm sure... but you lost me at dimension

    anyhow, this whole surrogate-vs-natural key is really your call

    you certainly understand it well enough to make your own choice
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    very nice, i'm sure... but you lost me at dimension

    anyhow, this whole surrogate-vs-natural key is really your call

    you certainly understand it well enough to make your own choice
    Okay, thanks.


    Debbie


Bookmarks

Posting Permissions

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