Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2009
    Posts
    66

    Unanswered:

    Members can have friends and some friends can be "top friends". The "retrieve query" should first retrieve from the top_friends.

    Is this the best way to do this?

    Here is my DDL:

    -- The members table
    CREATE TABLE Members
    (
    mem_email VARCHAR(255) NOT NULL PRIMARY KEY
    );

    -- The friends table
    CREATE TABLE Friends
    (
    friend_requestor_email VARCHAR(255) NOT NULL REFERENCES dbo.Members(mem_email),
    friend_to_email VARCHAR(255) NOT NULL REFERENCES dbo.Members(mem_email),
    friend_created DATETIME NOT NULL = CURRENT_TIMESTAMP,
    friend_approved DATETIME NOT NULL = CURRENT_TIMESTAMP
    PRIMARY KEY(friend_requestor_email, friend_to_email) -- There is a problem here because a member can insert friend_to_email, friend_requestor_email in that order which the DRI will not validate, I've used to a Trigger below so that it automatically invalidates it
    )

    -- This table is for members that want to select "top" friends. That is, a member might have a "preferred" number of friends that they want to display.
    CREATE TABLE Friends_Top
    (
    mem _email VARCHAR(255) NOT NULL REFERENCES dbo.Members( mem_email),
    friend_requestor_email VARCHAR(255) NOT NULL REFERENCES dbo.Friends( friend_requestor_email),
    friend_to_email VARCHAR(255) NOT NULL REFERENCES dbo.Friends(friend_to_email ),
    PRIMARY KEY(friend_requestor_email, friend_to_email),
    friend_top_order INT NOT NULL = 0,
    CHECK(mem_email = friend_requestor_email OR mem_email = friend_to_email) -- Check to make sure that this member is in the friends table and is managing a row that belongs to them
    )


    Referential integrity will prevent friend_requestor_email, friend_to_email combination BUT IT WON'T prevent friend_to_email, friend_requestor_email combination (in that order). Since there can only be one friend row, I wrote a trigger to prevent any additional rows.

    [EDIT]: I can't post the Trigger because the post times-out when I do. Basically the Trigger checks on insert that there isn't already a friend (friend_to_email, friend_requestor_email order is invalid. The DRI will automatically enforce the OTHER order friend_requestor_email, friend_to_email).


    Retrieve query:
    SELECT * FROM (
    SELECT b.*, b.friend_top_order AS actual_order FROM friends_top a INNER JOIN dbo.friends b -- etc etc
    UNION -- Filter out the duplicates
    SELECT c.*, 99999 AS actual_order FROM dbo.friends c --- the "9999" is so that the top_friends are ordered on the top) AS d ORDER BY d.actual_order ASC;
    Last edited by sqlguru; 06-22-09 at 09:18.

  2. #2
    Join Date
    Jun 2009
    Posts
    66

    How to model this?

    double post
    Last edited by sqlguru; 06-22-09 at 09:17.

  3. #3
    Join Date
    Jun 2009
    Posts
    66
    double post
    Last edited by sqlguru; 06-22-09 at 09:17.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    -- The members table
    CREATE TABLE Members(
    	id		BIGINT	IDENTITY (1, 1)	NOT NULL,
    	mem_email	VARCHAR(255)				NOT NULL,
    	CONSTRAINT PK_Members PRIMARY KEY (id)
    );
    
    -- The friends table
    CREATE TABLE Friends(
    	Member_id	BIGINT NOT NULL REFERENCES dbo.Members(id),
    	Friend_id	BIGINT NOT NULL REFERENCES dbo.Members(id),
    	IND_best_friend	CHAR(1)	NOT NULL DEFAULT 'N'
    		-- members can tag their top friends, 
    		-- 'Y' for best friend, 'N' otherwise
    		CONSTRAINT IND_best_friend_YN CHECK (IND_best_friend IN ('Y', 'N')),
    	friend_created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    	friend_approved DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    	CONSTRAINT PK_Friends PRIMARY KEY(Member_id, Friend_id) 
    	-- (*) There is NO problem here, A from ACID you know ...
    )
    
    SELECT M.mem_email as member, MF.mem_email as friend, IND_best_friend
    FROM Friends AS F
    	INNER JOIN  Members as M ON
    		F.Member_id = M.id
    	INNER JOIN Members as MF ON
    		F.Friend_id = MF.id
    ORDER BY M.mem_email, IND_best_friend DESC, MF.mem_email
    I wouldn't make PK's from VARCHAR(255) columns.

    (*) An INSERT will insert ALL the provided values at once. They are marked as NOT NULL, so they both have to be supplied. No need for a trigger or whatever.

    No need for a third table to store the "top friends".

    I wouldn't start column names with the table name.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jun 2009
    Posts
    66
    Surrogates have no place in RDMS (other than for data scrubbing) and length of datatypes does not matter either according to Celko (minimal hashing algorithms, teradata vldbs etc).

    If Member A becomes a friend of Member B, can member B become a friend of Member A? If so, friend_approved becomes denormalized because both Member A and Member B have the same approved dates.

    Also, I was considering a rank instead of a flag. This will require count and synch queries when a friend is added/removed.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by sqlguru
    Surrogates have no place in RDMS (other than for data scrubbing) and length of datatypes does not matter either according to Celko (minimal hashing algorithms, teradata vldbs etc).
    Physical implementation will depends on the rdbms, not Celko. You can buy more disks, and so not have a storage volume issue, however that extra information still needs to be read from and, written to, disk, as well as take up RAM so it isn't quite as simple as throwing disks at it. Then there are the logical considerations.

    Personally, I generally prefer natural keys but I would not describe surrogates as having no place in an RDBMS. there are plenty here that would argue the exact opposite of your stance.

    In any event, I would not consider a column of length 255 for a column that would participate in foreign keys, especially one likely to change.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You started heading down the wrong road when you created a separate table for preferred friends.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Jun 2009
    Posts
    66
    I didn't create a separate table for preferred friends.

    Preferred friends normalizes the column "friend order" and it references directly to the friends table (cascaded). Some members may not have preferred friends, but they would have regular friends. This would make "friend order" not normalized.

    Also, there should only be one friendship between two users. Member A (start) can become Member B's friend. How can Member B (start) be Member A's friends? This is sort of a logical error. This would also require two rows per friendship AND it would make the "approve date" not normalized.

    However, having two rows per friendship can make querying faster. For one friendship, you would have to do a complex union query (to pick from preferred first, then from friends etc).

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Friends_TOP?
    You have two tables with identical primary keys. That to me suggests a problem.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Also, there should only be one friendship between two users. Member A (start) can become Member B's friend. How can Member B (start) be Member A's friends? This is sort of a logical error. This would also require two rows per friendship AND it would make the "approve date" not normalized.
    In a way you are right about this only 1 friendship between 2 people. But you will have to look at it from the point of view of your members: Supposer you and me are members. I may enlist you as a (best) friend, while you may may not even be inclined to call me a friend of yours at all.
    How would you put that in your database? I think that's what the "approve date" is for. To indicate that the friendship is mutual.
    This looks more like a dating thing, where you want/need the approval of both parties.
    If you have both a 'SQLguru'-'Wim' friends-record AND a 'Wim'-'SQLguru', you can deduct the friendship is mutual and thus approved. I would drop the "approve date" thing.

    For one friendship, you would have to do a complex union query (to pick from preferred first, then from friends etc).
    Code:
    SELECT M.mem_email as member, MF.mem_email as friend, IND_best_friend
    FROM Friends AS F
    	INNER JOIN  Members as M ON
    		F.Member_id = M.id
    	INNER JOIN Members as MF ON
    		F.Friend_id = MF.id
    
    UNION ALL
    
    SELECT MF.mem_email as member, M.mem_email as friend, IND_best_friend
    FROM Friends AS F
    	INNER JOIN  Members as M ON
    		F.Member_id = M.id
    	INNER JOIN Members as MF ON
    		F.Friend_id = MF.id
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  11. #11
    Join Date
    Jun 2009
    Posts
    66
    Good point but approval date is temporal logic. It is better to have a WHEN than a IS when storing data (you can deduce the IS from the WHEN but not the other way around, WhenHired vs IsHired).

    I have another question.

    Since members have "account attributes" such as email, password, username...should those be separated into an accounts table?

  12. #12
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Also, there should only be one friendship between two users. Member A (start) can become Member B's friend. How can Member B (start) be Member A's friends? This is sort of a logical error. This would also require two rows per friendship AND it would make the "approve date" not normalized.
    In a way you are right about this only 1 friendship between 2 people. But you will have to look at it from the point of view of your members: Supposer you and me are members. I may enlist you as a (best) friend, while you may may not even be inclined to call me a friend of yours at all.
    I think that's why you added the "approve date" column. To indicate that the friendship is mutual.
    This looks more like a dating thing, where you want/need the approval of both parties.
    If you have both a 'SQLguru'-'Wim' friends-record AND a 'Wim'-'SQLguru', you can deduct the friendship is mutual and thus approved. I would drop the "approve date" thing from the model.

    For one friendship, you would have to do a complex union query (to pick from preferred first, then from friends etc).
    Code:
    SELECT M.mem_email as member, MF.mem_email as friend, IND_best_friend
    FROM Friends AS F
    	INNER JOIN  Members as M ON
    		F.Member_id = M.id
    	INNER JOIN Members as MF ON
    		F.Friend_id = MF.id
    
    UNION ALL
    
    SELECT MF.mem_email as member, M.mem_email as friend, IND_best_friend
    FROM Friends AS F
    	INNER JOIN  Members as M ON
    		F.Member_id = M.id
    	INNER JOIN Members as MF ON
    		F.Friend_id = MF.id
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •