SitePoint Sponsor

User Tag List

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

    What Keys to use for Many-to-Many-to-Many?

    I haven't made my mind up yet, but am thinking that Articles on my website will follow this URL-structure...
    Code:
    www.mysite.com/finance/accounting/taxes/why-you-should-hire-a-cpa
    www.mysite.com/operations/manufacturing/production/is-just-in-time-the-way-to-go
    www.mysite.com/legal/type/litigation/does-suing-copyright-infringers-make-sense
    Here are the Entities and Relationships involved...

    Entities:
    - Category
    - Dimension
    - Sub-Category
    - Article


    Tables:

    CATEGORY
    - id
    - name

    DIMENSION
    - id
    - name

    SUBCATEGORY
    - id
    - name

    ARTICLE
    - id
    - slug
    - author
    - written_on
    - heading
    - body


    Relationships:
    CATEGORY -||-----|<- CATEGORY_DIMENSION ->|-------||- DIMENSION

    DIMENSION -||----------|<- SUBCATEGORY

    CATEGORY_DIMENSION -||-----------|<- SUBCATEGORY

    SUBCATEGORY -||-----------|<- SUBCATEGORY_ARTICLE ->|------------||- ARTICLE


    Questions:
    1.) Is the second Relationship needed?


    2.) For the CATEGORY_DIMENSION junction table, should I have a standalone Primary Key?


    3.) For the CATEGORY_DIMENSION junction table, what should I use for my Foreign Keys?

    - category.id and dimension.id ??

    - category.name and dimension.name ??


    4.) For SUBCATEGORY, what should I use for the Foreign Key to DIMENSION?

    - dimension.id ??

    - dimension.name ??


    5.) For SUBCATEGORY, what should I use for the Foreign Key to CATEGORY_DIMENSION?

    - category.id and dimension.id ??

    - category.name and dimension.name ??


    6.) For the SUBCATEGORY_ARTICLE junction table, should I have a standalone Primary Key?


    7.) For the SUBCATEGORY_ARTICLE junction table, what should I use for my Foreign Keys?

    - category.id and dimension.id and sub-category.id and article.id ??

    - category.name and dimension.name and sub-category.name and article.slug ??


    Over time, I always seem to change back and forth on which approach to take...


    In closing...
    - Should Primary Keys always be an auto-increment Integer?

    - Can Primary Keys be a Text field when a Table is really just a Look-up Table? (e.g. Category, Dimension, Sub-Category)

    - And is it bad for Performance and Referential Integrity if you use Text fields for the Primary and Foreign Keys?


    It appears there are several ways the above scenario could be handled, but I'm not sure what the *BEST* way is...

    Help needed!!!

    Sincerely,


    Debbie

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i'm not going to analyze your data model, but i will answer your questions

    - Should Primary Keys always be an auto-increment Integer?

    no, there's a good argument for using natural instead of surrogate keys (although there is an unusual amount of dissention on this topic)

    - Can Primary Keys be a Text field when a Table is really just a Look-up Table?

    by text i assume you mean VARCHAR (and not TEXT), and the answer is absolutely, yes

    And is it bad for Performance and Referential Integrity if you use Text fields for the Primary and Foreign Keys?

    bad for performance? i don't think so, except in rare situations

    bad for referential integrity? absolutely not
    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,756
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    - Should Primary Keys always be an auto-increment Integer?

    no, there's a good argument for using natural instead of surrogate keys (although there is an unusual amount of dissention on this topic)
    So what is your preference, and why?


    (I have always used ID for joins in the past, because it was beat into me when I learned databases.)


    However, when a table has 4 Keys, I must say that this...
    Code:
    finance + accounting + taxes + why-you-should-hire-a-cpa
    ...would be easier to read than this...

    Code:
    1 + 3 + 2 + 27

    - Can Primary Keys be a Text field when a Table is really just a Look-up Table?

    by text i assume you mean VARCHAR (and not TEXT), and the answer is absolutely, yes
    Okay.


    And is it bad for Performance and Referential Integrity if you use Text fields for the Primary and Foreign Keys?

    bad for performance? i don't think so, except in rare situations
    I thought one main reason people say to always use ID's is that they are Integers and therefore quicker for the database to find? (Think Indexes)


    bad for referential integrity? absolutely not
    I guess if you have "Cascade On Update" set, then there isn't anything to worry about if I changed a Category from "Small Business" to "Small Biz", right?


    On a Junction Table, should there always be a Primary Key in addition to the Foreign Keys? (Because technically all of the Foreign Keys in total make a PK by themselves?!)

    Thanks,


    Debbie

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    So what is your preference, and why?

    depends on the situation, i use both

    I thought one main reason people say to always use ID's is that they are Integers and therefore quicker for the database to find? (Think Indexes)

    i don't think the minuscule performance differences compensate for the obfuscation that surrogate keys introduce

    I guess if you have "Cascade On Update" set, then there isn't anything to worry about if I changed a Category from "Small Business" to "Small Biz", right?

    yes

    On a Junction Table, should there always be a Primary Key in addition to the Foreign Keys? (Because technically all of the Foreign Keys in total make a PK by themselves?!)

    yes
    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,756
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    So what is your preference, and why?

    depends on the situation, i use both
    Okay.


    I thought one main reason people say to always use ID's is that they are Integers and therefore quicker for the database to find? (Think Indexes)

    i don't think the minuscule performance differences compensate for the obfuscation that surrogate keys introduce
    That is the same conclusion that I am coming to...


    I guess if you have "Cascade On Update" set, then there isn't anything to worry about if I changed a Category from "Small Business" to "Small Biz", right?

    yes
    Will it create problems having Primary Keys or Foreign Keys that have spaces in them (e.g. "Small Business") or hyphens (e.g. "postage-meters-can-save-you-money") ?


    On a Junction Table, should there always be a Primary Key in addition to the Foreign Keys? (Because technically all of the Foreign Keys in total make a PK by themselves?!)

    yes
    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
  •