SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Joins and table linking made easier?

    There are 3 types sets of tables :
    a) for registering new users there are 3 "temporary tables" (that exist permanently - not temporarily generated, only named this)
    b) update table (maintains edit histories by using Inserts)
    c) and permanent tables (live tables)

    Please note the (a) temporary tables are linked by a primary id. (of the temporary table that contains the username/passwords.)

    Rather than try to link the update tables and permanent tables with new sets of primary ids -- wouldn't it be best to always link tables throughout by the same single ID from the temporary table? (also I could trace back if needed) Can't I link with the same primary Id of the temporary table containing the username/password throughout, as long as it is Inserted into each table?
    (if so, should this column thoughout be indexed?)

    When joining tables, they don't need to reference table primary id's do they?
    Can't two or three way joins be done via any columns that share values in agreement? Wouldn't this make JOINS extremely easy and make the database JOINS more flexible throughout by leaps and bounds?

  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)
    the first part of your post is pretty hard to understand, but i wanted to comment on this --
    Quote Originally Posted by datadriven
    When joining tables, they don't need to reference table primary id's do they? Can't two or three way joins be done via any columns that share values in agreement? Wouldn't this make JOINS extremely easy and make the database JOINS more flexible throughout by leaps and bounds?
    no, they don't, yes, they can, and maybe they would



    i guess it depends on whether the columns used to join the tables actually made sense

    i mean, if you joined shirts to penguins by shirt_id = penguin_id, it might work, but i dunno about it being very useful
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I joining on columns outside of primary id's, should these columns be indexed?

  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)
    be careful about going down this road

    if you're joining on columns that aren't primary or foreign keys, you have to ask yourself why

    and yes, of course they should have indexes -- otherwise you'll do a lot of table scans
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if you're joining on columns that aren't primary or foreign keys, you have to ask yourself why
    But would these be considered foreign keys?(keys pulled from original temporary registration table - which is where username/password resides)

    Why -- to make linking structure much more easy throughout -- by placing the same primary id from temporary table -- instead of creating a whole new link structure/foreign keys among the permanent set of tables. (not to mention "update tables" -- these are really insert tables for edit histories)

    This way I can filter by session variable -- the original primary id acting as session variable.

  6. #6
    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)
    just because you can join on some column doesn't mean it has to be a foreign key

    "foreign key" is a relational integrity concept that means --

    1. you cannot insert a row with a value in the foreign key that doesn't exist in a primary key in the referenced table

    2. you cannot delete a row from the table with the primary key as long as it has related rows in the referencing table (unless you specified ON DELETE CASCADE when declaring the foreign key)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    then I would like to ask, r937, how would you generally handle this?
    (generally being the operative term!)

    -would you join tables (temp/permanent/etc) by different sets of foreign keys while filtering by a session variable throughout (from the temporary table primary id having username/password)?

    -or would you join on this indexed column containing the primary id throughout?

    Big question that binds the website database. I'm just trying to be sure before linking 35+ tables.

  8. #8
    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 sorry, i don't understand your specific database design, especially the part about your temporary-that-are-really-permanent tables

    i would declare foreign keys wherever i wanted to enforce relational integrity

    in my queries, i would join on whatever columns necessary -- but as i suggested earlier, i'd have a hard time coming up with an example of joining on columns that aren't pk/fk related
    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
  •