2

My database teacher asked us to program a clone of Facebook using MySQL as the database management system, so I'm trying to represent a friends relationship in the corresponding database.

So far I have built the following tables by searching everywhere:

CREATE TABLE Users (
    UserID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    Nome VARCHAR (20) NOT NULL,
    Sobrenome VARCHAR (20) NOT NULL,
    Foto LONGBLOB,
    Cidade VARCHAR (180),
    PRIMARY KEY (UserID)
);

CREATE TABLE Friends (
    UserID INT UNSIGNED NOT NULL,
    FriendID INT UNSIGNED NOT NULL,
    PRIMARY KEY (UserID, FriendID),
    CONSTRAINT FK_UserID
    FOREIGN KEY UserID REFERENCES Users (UserID),
    CONSTRAINT FK_FriendID
    FOREIGN KEY FriendID REFERENCES Users (UserID)
); 

I pictured the Friends table as a self-relationship using UserID (declared as the Users table primary key).

Beyond some basic #1064 errors I'm working on: Is it possible to add a Block Friend option to this relationship? For example, if I don't want to talk with a person anymore, I just block them.

8

I would cover the “Block Friend” requirement introducing the concept of Friendship Status to the system—where “Blocked” is only one of the possible instances, since a Friendship may be also be, e.g., “Accepted” or “Declined”—, as I will detail as follows.

Business rules

When modelling a relational database, I highly recommend (a) identifying and (b) formulating all the rules that apply in the business environment with respect to the informational characteristics of relevance, and do so before (c) creating tables and declaring constraints. In this way you can delineate the corresponding conceptual schema with the needed precision, and then the logical-level declarations are much more easy and natural.

So let us start with some basic expository rules of a Friendships system:

  • A User is the Requester of zero-one-or-many Friendships
  • A User is the Addressee of zero-one-or-many Friendships
  • A Friendship is primarily identified by the combination of its RequesterId and its AddresseeId

Then the Status aspect comes into play:

  • A Friendship holds one-to-many FriendshipStatuses
  • A FriendshipStatus is primarily identified by the combination of its RequesterId, its AddresseeId and its SpecifiedDateTime
  • A User specifies zero-one-or-many FriendshipStatuses
  • A Status classifies zero-one-or-many FriendshipStatuses
  • A Status is primarily identified by its StatusCode
  • A Status is alternately identified by its Name

Expository IDEF1X diagram

After having formulated all the business rules with accuracy, one can then draw an IDEF1Xa diagram like the one expounded in Figure 1 to supply a graphical communication device:

Figure 1 - Friendship and Status IDEF1X Diagram

As shown, Requester and Addressee are labels that indicate the specific Role performed by each User taking part in a given Friendship.

The Friendship entity type depicts an association with a many-to-many (M:N) cardinality ratio that can involve distinct occurrences of the same entity type, i.e., User. This is an example of the classic conceptual construct known as “Bill of Materials” or “Parts Explosion”.

Status and Friendship are connected by way of the FriendshipStatus entity type, which portrays what may become a sequence of Status specifications for the involved Friendship instances.


a Integration Definition for Information Modeling (IDEF1X) is a highly recommendable modelling technique that was established as a standard in December 1993 by the U.S. National Institute of Standards and Technology (NIST). It is solidly based on (a) the early theoretical material authored by the sole originator of the relational model, i.e., Dr. E. F. Codd; on (b) the entity-relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.


Illustrative logical SQL-DDL design

Subsequently, I created the following logical-level arrangement in order to represent the conceptual aspects previously formulated (naturally, you will have to adapt it to your exact needs, as it may entail, e.g., several constraints not considered nor declared here and a different quantity of columns):

-- You should determine which are the most fitting 
-- data types and sizes for all the table columns 
-- depending on your business context characteristics.

-- At the physical level, you should make accurate tests 
-- to define the mostconvenient INDEX strategies based on 
-- the pertinent query tendencies.

-- As one would expect, you are free to make use of 
-- your preferred (or required) naming conventions. 

CREATE TABLE UserProfile ( -- Represents an independent entity type.
    UserId          INT      NOT NULL,
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    BirthDate       DATE     NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    Username        CHAR(20) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT UserProfile_PK  PRIMARY KEY (UserId),
    CONSTRAINT UserProfile_AK1 UNIQUE ( -- Composite ALTERNATE KEY.
        FirstName,
        LastName,
        GenderCode,
        BirthDate
    ),
    CONSTRAINT UserProfile_AK2 UNIQUE (Username) -- Single-column ALTERNATE KEY.
);

CREATE TABLE Friendship ( -- Stands for an associative entity type.
    RequesterId     INT      NOT NULL,
    AddresseeId     INT      NOT NULL, -- Fixed with a well-delimited data type.
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Friendship_PK            PRIMARY KEY (RequesterId, AddresseeId), -- Composite PRIMARY KEY.
    CONSTRAINT FriendshipToRequester_FK FOREIGN KEY (RequesterId)
        REFERENCES UserProfile (UserId),
    CONSTRAINT FriendshipToAddressee_FK FOREIGN KEY (AddresseeId)
        REFERENCES UserProfile (UserId)
);

--
CREATE TABLE MyStatus ( -- Denotes an independent entity type.
    StatusCode CHAR(1)  NOT NULL,
    Name       CHAR(30) NOT NULL,
    --
    CONSTRAINT MyStatus_PK PRIMARY KEY (StatusCode),
    CONSTRAINT MyStatus_AK UNIQUE      (Name) -- ALTERNATE KEY.
); 

CREATE TABLE FriendshipStatus ( -- Represents an associative entity type.
    RequesterId       INT      NOT NULL,
    AddresseeId       INT      NOT NULL,
    SpecifiedDateTime DATETIME NOT NULL,
    StatusCode        CHAR(1)  NOT NULL,
    SpecifierId       INT      NOT NULL,
    --
    CONSTRAINT FriendshipStatus_PK             PRIMARY KEY (RequesterId, AddresseeId, SpecifiedDateTime), -- Composite PRIMARY KEY.
    CONSTRAINT FriendshipStatusToFriendship_FK FOREIGN KEY (RequesterId, AddresseeId)
        REFERENCES Friendship  (RequesterId, AddresseeId), -- Composite FOREIGN KEY.
    CONSTRAINT FriendshipStatusToMyStatus_FK   FOREIGN KEY (StatusCode)
        REFERENCES MyStatus    (StatusCode),
    CONSTRAINT FriendshipStatusToSpecifier_FK  FOREIGN KEY (SpecifierId)
        REFERENCES UserProfile (UserId)      
);


Tested on MySQL 5.6 in this SQL Fiddle.


A Friendship row and its earliest FriendshipStatus related counterpart

With this arrangement, when a User requests a Friendship, the corresponding Friendship row has to be inserted along with the complementary FriendshipStatus counterpart, which in turn must contain a value of 'R' in StatusCode, denoting the fact that a User has simply requested the Friendship, and is waiting for the Addressee to accept it ('A'), decline it ('D'), or leave it as requested.

As usual, I would decidedly set up the multi-row operations that have to be executed as a single Unit of Work with the aid of ACID TRANSACTIONS. Needless to say, the assistance of the application program(s) component(s) accessing the database would as well be very helpful to facilitate the processing of the Friendships.

Later FriendshipStatus rows

Subsequently, every time that the Status of a certain Friendship has to be put up-to-date, the Users would only have to INSERT a new FriendshipStatus row, containing:

  • the respective RequesterId and AddresseeId values —taken from the concerning Friendship row—;

  • the new and meaningful StatusCode value —drawn from MyStatus.StatusCode—;

  • the value of the exact INSERTion instant, i.e., SpecifiedDateTime —preferably using a server function so that you can retrieve and retain it in a reliable manner—; and

  • the SpecifierId value that would indicate the respective UserId that entered the new FriendshipStatus into the system.

Sample data

To that extent, let us suppose that the MyStatus table, serving a “look-up” role, encloses the following data —with PK values that are (1) end user-, app programmer-, and DBA-friendly and (2) small and fast in terms of bytes at the physical implementation level—:

 +-——————————-+-—————————-+
 | StatusCode | Name      | 
 +-——————————-+-—————————-+
 | R          | Requested |
 +------------+-----------+
 | A          | Accepted  |
 +------------+-----------+
 | D          | Declined  |
 +------------+-----------+
 | B          | Blocked   |
 +------------+-----------+

So, the FriendshipStatus table may hold data like shown below:

 +-———————————-+-———————————-+-———————————————————————-+-——————————-+-———————————-+
 | RequesterId | AddresseeId | SpecifiedDateTime       | StatusCode | SpecifierId |
 +-———————————-+-———————————-+-———————————————————————-+-——————————-+-———————————-+
 |        1750 |        1748 | 2018-04-01 16:58:12.000 | R          |        1750 |
 +-------------+-------------+-------------------------+------------+-------------+
 |        1750 |        1748 | 2018-04-02 09:12:05.000 | A          |        1748 |
 +-------------+-------------+-------------------------+------------+-------------+
 |        1750 |        1748 | 2018-04-04 10:57:01.000 | B          |        1750 |
 +-------------+-------------+-------------------------+------------+-------------+
 |        1750 |        1748 | 2018-04-07 07:33:08.000 | R          |        1748 |
 +-------------+-------------+-------------------------+------------+-------------+
 |        1750 |        1748 | 2018-04-08 12:12:09.000 | A          |        1750 |
 +-------------+-------------+-------------------------+------------+-------------+

It can be said that the FriendshipStatus table serves the purpose of comprising a time series, which stands for the evolution of the Friendships of significance.

You can decidedly declare one or more views that comprise columns from several tables to facilitate, e.g., writing code for data retrieval.

Blocking a Friend

As demonstrated in the sample data of the FriendshipStatus table, having a row that holds a value of 'B' in the StatusCode column would represent that some User decided to block one of his or her Friends.

And, yes, you can use your application program(s) component(s) to access the database and check the latest FriendshipStatus row (via MAX(SpecifiedDateTime)) for a given (RequesterId, AddresseId) combination of values to control the flow of the applicable processes of the system.

Other option: Retaining only the most recent Status

In case that, for whatever reason, you have to avoid retaining the FriendShipStatus history in the fashion previously described, you can discard the creation of a FriendshipStatus table and simply set up Friendship so that it includes a FOREIGN KEY constraint making a reference to Status.StatusCode directly:

CREATE TABLE Friendship (
    RequesterId    INT      NOT NULL,
    AddresseeId    INT      NOT NULL,
    CratedDateTime DATETIME NOT NULL,
    StatusCode     CHAR(1)  NOT NULL,
    CreatorId      INT      NOT NULL,
    --
    CONSTRAINT Friendship_PK            PRIMARY KEY (RequesterId, AddresseeId),
    CONSTRAINT FriendshipToRequester_FK FOREIGN KEY (RequesterId)
        REFERENCES UserProfile (UserId),
    CONSTRAINT FriendshipToAddressee_FK FOREIGN KEY (AddresseeId)
        REFERENCES UserProfile (UserId)
    CONSTRAINT FriendshipToMyStatus_FK  FOREIGN KEY (StatusCode)
        REFERENCES MyStatus    (StatusCode),
    CONSTRAINT FriendshipToCreator_FK   FOREIGN KEY (CreatorId)
        REFERENCES UserProfile (UserId)      
);


Tested on MySQL 5.6 in this SQL Fiddle.


In this manner, (i) the INSERT of a certain Friendship row goes with a value of 'R' in the StatusCode column, and each time that the Status of a Friendship is modified you just have to (ii) UPDATE the values in StatusCode, SpecifiedDateTime and CreatorId —which must enclose the identifier value of the latest User who specified the Status of the concerning Friendship—.

Related posts

Various ideas presented in this post are based on this Q & A, where I propose a database design that meets the informational requirements of a similar business environment. I recommend reading those posts because, although the focus of that question is different from yours, that answer is more detailed and contains several points of relevance.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.