#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2011
    Posts
    2
    Rep Power
    0

    Red face Easy question about primary key columns


    sorry for such a noob question.
    I have a table called Logins representing a person logging into a site.

    Logins Columns
    1) fk_sites_recid (int32, id of the site where person logs in)
    2) fk_user_recid (int64, id of person)
    3) fk_user_sites_recid (int32, site id of the person)
    4) LoginTime (datetime, time the person logged in)

    Each person is a member of a site, so RecId + fk_Sites_recid makes up the primary key in the user table.
    I usually put an identifier recid column in tables where there's likely to be a relationship on a right join, so I'd use recid + fk_sites_recid for primary key, but this time as there won't be one I thought I could save some disk space by leaving out recid.
    For my logins table, which columns should make up the primary key? Is it all 4 columns?
    thanks for your help
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    this is not an "easy" question at all


    Each person is a member of a site, so RecId + fk_Sites_recid makes up the primary key in the user table.
    i don't get it... are you saying the same person "recid" can be a member of more than one site?


    I usually put an identifier recid column in tables where there's likely to be a relationship on a right join
    i don't get it... relationships are relationships, and joins can be made left or right depending on which table you mention first in the FROM clause, so i'm not sure what you're trying to say here


    I'd use recid + fk_sites_recid for primary key, but this time as there won't be one I thought I could save some disk space by leaving out recid.
    i don't get it... so "recid", which we learned earlier is the userid, can be left out? you're saying the logins table contains data about logins by people who aren't identified?


    whoa, am i ever lost

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2011
    Posts
    2
    Rep Power
    0
    I'm sorry if my terminologies are sadly lacking.

    Single database shared by several sites. Individual user is defined by the following.
    CREATE TABLE `users` (
    `RecID` bigint NOT NULL,
    `fk_Sites_RecID` int NOT NULL,
    other columns...
    PRIMARY KEY (`RecID`,`fk_Sites_RecID`),
    CONSTRAINT `FK_users_Sites_fk_Sites_RecID` FOREIGN KEY (`fk_Sites_RecID`) REFERENCES `sites` (`RecID`) ON DELETE CASCADE
    );

    Sample query to select all user/logins at a particular site is
    SELECT * FROM users LEFT JOIN logins ON
    users.recid = logins.fk_users_recid
    AND users.fk_sites_recid = logins.fk_users_sites_recid
    WHERE logins.fk_sites_recid = ?SITEID

    Logins Table Columns
    1) fk_sites_recid (sites.recid of where user logs in)
    2) fk_user_recid (users.recid)
    3) fk_user_sites_recid (users.fk_sites_recid)
    4) LoginTime (datetime, time the person logged in)

    Which columns should I use as my primary key in the logins table?
    I hope that's clearer
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    Originally Posted by czeshirecat
    Logins Table Columns
    1) fk_sites_recid (sites.recid of where user logs in)
    2) fk_user_recid (users.recid)
    3) fk_user_sites_recid (users.fk_sites_recid)
    4) LoginTime (datetime, time the person logged in)
    so presumably a user (recid) can log in to a different site than the one he's registered at?

    may i ask where you are getting the value for the 3rd column?

    are you doing a lookup on the user?

    if so, how do you know which site a user belongs to if the same userid (recid) can belong to more than one site?

    i'm still not seeing the logic behind your structures
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo