Creating a Friendships database that includes a Block Friend option

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.

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:

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 Fri

StackExchange Database Administrators Q#219411, answer score: 10