SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    table structure/keys question

    Hi,

    The old brain has fogged over so I need to ask for some help.

    I have two tables - as shown below. I need to make a third table for the many to many relastionship. should I use the coposite key from page_categories in the third table or should I create a surrogate one.

    I have tried so far to use data-driven keys (rather than surrogate), for ease of reading/following the data in child tables. However, I reckon such large data in a key is either inefficient or, it'll confuse me later into thinking this must be the parent, because it holds so much data/so many columns.

    so whachadidoo?


    Code MySQL:
    CREATE TABLE page_categories 
    ( business_id int NOT NULL
    , control_panel_section varchar(99) collate utf8_unicode_ci NOT NULL default ''
    , page_category varchar(99) collate utf8_unicode_ci NOT NULL
    , category_number int(11) NOT NULL
    , PRIMARY KEY  (business_id,control_panel_section,page_category)
    , KEY business_id (business_id)
    , KEY pageCategory_privilegeAreas_fk (control_panel_section)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
     
     
     
    CREATE TABLE website_nav_menus
    ( business_id int(11) NOT NULL
    , nav_menu_name varchar(99) collate utf8_unicode_ci NOT NULL
    , PRIMARY KEY  (business_id,nav_menu_name)
    , KEY nevMenu_navMenuStock_fk (nav_menu_name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
     
    create table pageCategories_navMenus
    (  business_id int NOT NULL
    , control_panel_section varchar(99) collate utf8_unicode_ci NOT NULL default ''
    , page_category varchar(99) collate utf8_unicode_ci NOT NULL
    , nav_menu_name varchar(99) collate utf8_unicode_ci NOT NULL
    , live_dev varchar(4) not null default 'dev'
    , on_off varchar(4) not null default 'off'
    , primary key( business_id, control_panel_section, page_category, nav_menu_name, live_dev, on_off )  
    , unique key (business_id, nav_menu_name, live_dev)
    )

    Having now written out the third table, I am wondering if I am over-engineering this because there is a lot of repetitive data - even though it's referencing from another table.

    bazz

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    please explain live_dev and on_off, and why you felt it necessary to include them in the primary key

    for simplicity's sake, let's say your two tables are called X and Y, and they have PKs bolded as follows --

    X ( bid, cps, pc, cn )

    Y ( bid, nmn )

    now you want your relationship table like this --

    XY ( bid, cps, pc, nmn, live_dev, on_off )

    if you include live_dev and on_off in the PK, this would allow the same { bid, cps, pc, nmn } multiple times in the XY table, whereas i would've thunk you'd only want one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks rudy,

    the live_dev column can hold one of two values - 'live' or 'dev'
    and the on_off column can hold 'on' and 'off'

    live_dev is to differentiate between a live environment and a development environment such as a development website to prepare for replacing the live one.

    on_off is for storing whether in either environment, that record is active or inactive.

    'Think' nav menu buttons for a live site and a dev site. I may want to disable a nav menu link when the dev site is being built but the live site may still require it to be 'on'.

    However, the answer you gave seems to suggest I was fumbling with it well.

    bazz

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    yeah, that's what i thought those columns were for

    so if this is your table: XY ( bid, cps, pc, nmn, live_dev, on_off )

    then you can have only one row per PK --

    ( 1, 2, 3, 4, 'dev', 'on' )

    and 1,2,3,4 cannot also exist for 'prod'

    but if this is your table: XY ( bid, cps, pc, nmn, live_dev, on_off )

    then you can have

    ( 1, 2, 3, 4, 'dev', 'on' )
    ( 1, 2, 3, 4, 'dev', 'off' )
    ( 1, 2, 3, 4, 'prod', 'on' )
    ( 1, 2, 3, 4, 'prod', 'off' )

    all 4 rows can exist simultaneously

    obviously you want to define the PK to meet your actual requirements


    okay, and now the coup de grace -- how would any of this differ if you use surrogate keys instead of the composite natural keys?

    see if you can do what i've done in illustrating the possible rows, but using surrogates...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I like your hint at using prod instead of live, especially when live can have two pronounciations. bit of a silly work, if you ask me

    So my cols are now

    ( bid, cps, pc, nmn, prod_dev, on_off )

    and primary key is

    ( bid, cps, pc, nmn, live_dev )

    I think. I won't want two records where one is for 'on' and the other for 'off'. they will be an either/or value but there will be a record each for 'prod' and 'dev'. There will always be a dev site and always a live site (certainly there will always be the need to support that eventuality), and, in respect of on_off, the values will either be 'on' or 'off' - not two records where one is on and the other off.

    thanks,

    bazz

  6. #6
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well I think I could use a surrogate pk but when querying, I would need to join the tables. I reckon that querying will be done much more often than record creation and so, it should be simpler not to require a join when querying.

    anyhoo, my surrogate suggestion would be

    ( bid, category_id, nmn, live_dev, on_off )

    | 1 | 3 | primary | dev | on |

    Is trying to reduce the need for a join, the main reason for using natural (foreign), keys?

    bazz

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    PRIMARY KEY( business_id, control_panel_section, page_category, nav_menu_name, live_dev, on_off )

    One day that is going to be a wonderful foreign key and URL reference to deal with - just saying.
    The only code I hate more than my own is everyone else's.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    we're well past that, oddz
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •