SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Hybrid View

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

    got my self in bother?

    I have a set of three tables, grandchild, parent and child - all was well.

    But having found this set of tbales very difficult to integrate with the db, I have now realised I can't set up a PK/FK constraint. Some of the values in my (hoped_for) PK, must be null - unless I create imaginery values for them.

    It seems my composite PK cannot have any column null even though, as a composite, the value is still unqiue.

    Whats a guy meant to do lol

    Should I create dopey data - eg setting a specifically NULL value?
    should I resort to a surrogate key (oh pulleeeze lol),
    or can I just be happy with a unique key being set.

    Here's my data structure in case I have got it all wrong.


    Code MySQL:
    CREATE TABLE control_panel_data_groups
    ( control_panel_section varchar(99) collate utf8_unicode_ci NOT NULL
    , cp_page_grouping varchar(32) collate utf8_unicode_ci NOT NULL default ''
    , PRIMARY KEY  (control_panel_section,cp_page_grouping)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
     
     
    CREATE TABLE control_panel_data_categories 
    ( control_panel_section varchar(99) collate utf8_unicode_ci NOT NULL default ''
    , cp_page_grouping varchar(32) collate utf8_unicode_ci default NULL
    , page_category varchar(99) collate utf8_unicode_ci NOT NULL
    , KEY pageCategories_categoriesGroups_fk (control_panel_section, cp_page_grouping),
      KEY business_id_2 (control_panel_section, page_category)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
     
    ALTER TABLE `control_panel_data_categories`
      ADD CONSTRAINT data_categories_data_groups_fk
        FOREIGN KEY (control_panel_section, cp_page_grouping) 
        REFERENCES control_panel_data_groups (control_panel_section, cp_page_grouping);
     
     
    CREATE TABLE control_panel_business_datagroups 
    ( business_id int(11) NOT NULL
    , control_panel_section varchar(99) collate utf8_unicode_ci NOT NULL
    , cp_page_grouping varchar(32) collate utf8_unicode_ci default NULL
    , page_category varchar(99) collate utf8_unicode_ci default NULL
    , UNIQUE KEY business_id ( business_id, control_panel_section, cp_page_grouping, page_category ),
      KEY control_panel_section ( control_panel_section, cp_page_grouping )
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
     
    ALTER TABLE `control_panel_business_datagroups`
      ADD CONSTRAINT control_panel_business_datagroups_ibfk_1 
      FOREIGN KEY (control_panel_section, cp_page_grouping) 
      REFERENCES control_panel_data_categories (control_panel_section, cp_page_grouping),
      ADD CONSTRAINT cpBusinessDataGroups_businesses_fk 
      FOREIGN KEY (business_id) 
      REFERENCES businesses (id);

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    two suggestions --

    first, forums threads are not the place to practice "link bait"

    a thread title like "got my self in bother?" gives no clue as to what the thread is about, forcing people to actually open the thread to find out, and you can be sure that some percentage of potential repliers will not bother, whether purposefully, or because they simply missed it because they're scanning the list of thread titles looking for certain words like query, primary key, and so on

    second, you neglected to point out ~why~ some of your columns need to be NULL, leaving the onus on the person reading your post to try to guess what's going on

    helps?

    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)
    oops. sorry. was very frustrated and forgot about making the title meaningful.

    I think I have made progress by making the nulls sensible values. Just goes to show how when I think I have the db structured well, there is room for improvement.

    bazz

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    unfortunately, "dopey" values (as you said earlier) don't usually mean anything, and can lead to more trouble than they're worth

    e.g. "9999-12-31" as expiry date

    what kind of "sensible" values did you settle on?
    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 don't know why I got so exasperated.

    All it took was for me to split the data into an extra table so that those which FK'd were primary keys.


  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    looks like my work here is done
    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
  •