Managing an individual piece of information
Assuming that, in your business domain,
- a User can have zero-one-or-many Friends;
- a Friend must first be registered as a User; and
- you will search for, and/or add, and/or remove, and/or modify, single values of a Friend List;
then each specific datum gathered in the Friendlist_IDs multivalued column represents a separate piece of information that carries a very exact meaning. Therefore, said column
- entails a proper group of explicit constraints, and
- its values have the potential of being manipulated individually by means of several relational operations (or combinations thereof).
Short answer
Consequently, you should retain each of the Friendlist_IDs values in (a) a column that accepts exclusively one sole value per row in (b) a table that represents the conceptual-level association type that can take place between Users, i.e., a Friendship —as I will exemplify in the following sections—.
In this way, you will be able to handle (i) said table as a mathematical relation and (ii) said column as a mathematical relation attribute —as much as MySQL and its SQL dialect permit, of course—.
Why?
Because the relational model of data, created by Dr. E. F. Codd, demands having tables that are composed of columns that hold exactly one value of the applicable domain or type per row; hence, declaring a table with a column that can contain more than one value of the domain or type in question (1) does not represent a mathematical relation and (2) would not permit obtaining the advantages proposed in the aforementioned theoretical framework.
Modeling Friendships between Users: Defining the business environment rules first
I highly recommend starting to shape a database delimiting —before anything else— the corresponding conceptual schema by virtue of the definition of the relevant business rules that, among other factors, must describe the types of interrelationships that exist between the distinct aspects of interest, i.e., the applicable entity types and their properties; e.g.:
- A User is primarily identified by his or her UserId
- A User is alternately identified by the combination of his or her FirstName, LastName, Gender, and Birthdate
- A User is alternately identified by his or her Username
- 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
Expository IDEF1X diagram
In this manner, I was able to derive the IDEF1X1 diagram shown in Figure 1, which integrates most of the rules previously formulated:
As depicted, Requester and Addressee are denotations that express the Roles carried out by the specific Users that take part in a given Friendship.
That being so, the Friendship entity type portrays an association type of many-to-many (M:N) cardinality ratio that can involve different ocurrences of the same entity type, i.e., User. As such, it is an example of the classic construct known as “Bill of Materials” or “Parts Explosion”.
1 Integration Definition for Information Modeling (IDEF1X) is a highly recommendable 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 of data, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.
Illustrative SQL-DDL logical design
Then, from the IDEF1X diagram presented above, declaring a DDL arrangement like the one that follows is much more “natural”:
-- 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),
CONSTRAINT FriendsAreDistinct_CK CHECK (RequesterId <> AddresseeId)
);
In this fashion:
- each base table represents an individual entity type;
- each column stands for a sole property of the respective entity type;
- a specific data typea is fixed for each column in order to guarantee that all the values it contains belong to a particular and well defined set, be it INT, DATETIME, CHAR, etc.; and
- multiple constraintsb are configured (declaratively) in order to ensure that the assertions in the form of rows retained in all the tables meet the business rules determined at the conceptual schema.
Advantages of a single-valued column
As demonstrated, you can, e.g.:
Take advantage of referential integrity enforced by the database management system (DBMS for brevity) for the Friendship.AddresseeId column, since constraining it as a FOREIGN KEY (FK for brevity) that makes a reference to the UserProfile.UserId column guarantees that every value points to an existing row.
Create a composite PRIMARY KEY (PK) made up of the combination of columns (Friendship.RequesterId, Friendship.AddresseeId), helping to elegantly distinguish all the INSERTed rows and, naturally, protect their uniqueness.
Of course, this means that the attachment of an extra column for system-assigned surrogate values (e.g., one set up with the IDENTITY property in Microsoft SQL Server or with the AUTO_INCREMENT attribute in MySQL) and the assisting INDEX is entirely superfluous.
Restrict the retained values in Friendship.AddresseeId to a precise data typec (which should match, e.g., the one established for UserProfile.UserId, in this case INT), letting the DBMS take care of the pertinent automatic validation.
This factor can as well help to (a) utilize of the corresponding built-in type functions and (b) optimize disk space usage.
Optimize data retrieval at the physical level by configuring small and fast subordinate INDEXes for the Friendship.AddresseeId column, as these physical elements can substantialy assist in speeding up the queries that involve said column.
Certainly, you can, e.g., put up a single-column INDEX for Friendship.AddresseeId alone, a multi-column one that encompasses Friendship.RequesterId and Friendship.AddresseeId , or both.
Avoid the unnecessary complexity introduced by “searching for” distinct values that are collected together inside the same column (very likely duplicated, wrongly typed, etc.), a course of action that would eventually slow down the functioning of your system, because you would have to resort to resource- and time-consuming non-relational methods to accomplish said task.
So, there are multiple reasons that call for analyzing the relevant business environment carefully in order to mark out the typed of each table column with accuracy.
As expounded, the role played by the database designer is paramount to make the best use of (i) the logical-level benefits offered by the relational model and (ii) the physical mechanisms provided by the DBMS of choice.
a, b, c, d Evidently, when working with SQL platforms (e.g., Firebird and PostgreSQL) that support DOMAIN creation (a distinctive relational feature), you can declare columns that only accept values that belong to their respective (fittingly constrained and sometimes shared) DOMAINs.
One or more application programs sharing the database under consideration
When you have to employ arrays in the code of the application program(s) accesing the database, you simply need to retrieve the relevant data set(s) in full and then “bind” it (them) to the concerning code structure or execute the associated app(s) process(es) that should take place.
Further benefits of single-valued columns: Database structure extensions are much more easy
Another advantage of holding the AddresseeId data point in its reserved and properly typed column is that it considerably facilitates extending the database structure, as I will exemplify below.
Scenario progression: Incorporating the Friendship Status concept
Since Friendships can evolve over time you might have to keep track of such a phenomenon, thus you would have to (1) expand the conceptual schema and (2) declare a few more tables in the logical layout. So, let us arrange the next business rules to delineate the new incorporations:
- 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
Extended IDEF1X diagram
Successively, the previous IDEF1X diagram can be extended in order to include the new entity types and interrelationship types described above. A diagram depicting the previous elements associated with the new ones is presented in Figure 2:
Logical structure additions
Afterwards, we can lengthen the DDL layout with the following declarations:
--
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),
CONSTRAINT FriendsAreDifferent_CK CHECK (RequesterId <> AddresseeId)
);
The creation of this DDL design, along with the previous excerpt, has been tested in this db<>fiddle that runs on SQL Server 2019.
Accordingly, every time that the Status of a given Friendship needs to be put up-to-date, the Users would only have to INSERT a new FriendshipStatus row, containing:
the suitable RequesterId and AddresseeId values —taken from the concerning Friendship row—;
the new and meaningful StatusCode value —drawn from MyStatus.StatusCode—;
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 —ideally, with the aid of your app(s) facilities—.
To that extent, let us suppose that the MyStatus table encloses the following data —with PK values that are (a) end user-, app programmer- and DBA-friendly and (b) 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 | 2016-04-01 16:58:12.000 | R | 1750 |
+-------------+-------------+-------------------------+------------+-------------+
| 1750 | 1748 | 2016-04-02 09:13:08.000 | A | 1748 |
+-------------+-------------+-------------------------+------------+-------------+
| 1750 | 1748 | 2016-04-02 11:02:06.000 | B | 1748 |
+-------------+-------------+-------------------------+------------+-------------+
As you can see, it can be said that the FriendshipStatus table serves the purpose of comprising a time series.
Responses to comments
Data manipulation
I know this is super old and maybe a dumb (sic) question but, I've been studying this and have been wondering how you'd go about simply getting a user's current friendship status with someone using the FriendshipStatus table. – @Timothy Fisher, on 2020-08-05 02:24:16Z
First of all, there is nothing wrong with your question and, although this is a design Q & A and data manipulation is a separate and subsequent task, explaining how to get the current or most-recent Status of a given Friendship can definitely assist in showing the advantages of the proposed layout (and therefore it can help a future reader).
Here the key is approaching the relevant data in terms of sets, since that is one of the main puroposes of adopting a relational frame of mind.
Deriving the current Status of a Friendship
Let us suppose that we are working with the following information in the FriendhipStatus table:
+-———————————-+-———————————-+-———————————————————————-+-——————————-+-———————————-+
| RequesterId | AddresseeId | SpecifiedDateTime | StatusCode | SpecifierId |
+-———————————-+-———————————-+-———————————————————————-+-——————————-+-———————————-+
| 1750 | 1748 | 2016-04-01 16:58:12.000 | R | 1750 |
+-------------+-------------+-------------------------+------------+-------------+
| 1750 | 1748 | 2016-04-02 09:13:08.000 | A | 1748 |
+-------------+-------------+-------------------------+------------+-------------+
| 1750 | 1748 | 2016-04-02 11:02:06.000 | B | 1748 |
+-------------+-------------+-------------------------+------------+-------------+
| 1825 | 4247 | 2016-05-11 16:28:03.000 | R | 1825 |
+-------------+-------------+-------------------------+------------+-------------+
| 1825 | 4247 | 2016-05-11 19:18:09.000 | A | 4247 |
+-------------+-------------+-------------------------+------------+-------------+
| 1748 | 5342 | 2016-06-11 11:13:05.000 | R | 1748 |
+-------------+-------------+-------------------------+------------+-------------+
| 1748 | 5342 | 2016-06-12 19:03:07.000 | D | 5342 |
+-------------+-------------+-------------------------+------------+-------------+
| 3691 | 1750 | 2016-06-20 16:28:05.000 | R | 3691 |
+-------------+-------------+-------------------------+------------+-------------+
| 3691 | 1750 | 2016-06-20 16:51:10.000 | A | 1750 |
+-------------+-------------+-------------------------+------------+-------------+
| 3691 | 1750 | 2016-06-21 08:05:05.000 | B | 1750 |
+-------------+-------------+-------------------------+------------+-------------+
And say we want to obtain the current Status of the Friendship between the User primarily identified by the UserId 1750 and the User primarily identified by the UserId 1748.
A very good option is employing what is commonly known as a subquery, i.e., a nested DML operation (in this case a SELECT):
SELECT FS.StatusCode AS CurrentStatusCode
FROM FriendshipStatus FS
WHERE FS.RequesterId = 1750 --(a)
AND FS.AddresseeId = 1748 --(b)
AND FS.SpecifiedDateTime = (
SELECT MAX(NestedFS.SpecifiedDateTime)
FROM FriendshipStatus NestedFS
WHERE NestedFS.RequesterId = FS.RequesterId
AND NestedFS.AddresseeId = FS.AddresseeId
);
-- (a), (b) Those “fixed” values can of course be replaced by parameters.
In this manner, we would be exclusively deriving the CurrentStatusCode —contextual alias for FriendshipStatus.StatusCode— in a scalar column in the main SELECT operation. The nested SELECT gets the latest FriendshipStatus.SpecifiedDateTime value —via the MAX() function— for the corresponding Friendship, i.e., the RequesterId and AddresseeId values assigned in the main SELECT, and these three values are in turn used as conditions in the main WHERE clause. As a result, we derive the following column and value for the Requester 1750 and the Addressee 1748:
+-—————————————————-+
| CurrentStatusCode |
+-—————————————————-+
| B |
+-—————————————————-+
Which means that one of the Friends involved blocked the other.
And if you want to obtain more information about that Friendship along with the current Status, you simply have to incorporate more relevant columns to the main SELECT:
SELECT FS.RequesterId,
FS.AddresseeId,
FS.SpecifiedDateTime,
FS.StatusCode AS CurrentStatusCode,
FS.SpecifierId
FROM FriendshipStatus FS
WHERE FS.RequesterId = 1750
AND FS.AddresseeId = 1748
AND FS.SpecifiedDateTime = (
SELECT MAX(NestedFS.SpecifiedDateTime)
FROM FriendshipStatus NestedFS
WHERE NestedFS.RequesterId = FS.RequesterId
AND NestedFS.AddresseeId = FS.AddresseeId
);
Having all those data points you would know, e.g., who “blocked” who and when.
Views
If you are interested in simplifying future code regarding Status data retrieval (and make it more “readable”), you can create a view (i.e. a derived table) that, e.g., “hides” the subquery, and you can subsequently SELECT directly from said view.
Physical-level settings
Of course, in order to speed up data manipulation performance you have to take care of the design settings at the physical level of abstraction; e.g., the involved tables should be supported by single- and/or multi-column INDEXes, taking into account, e.g., the order of the columns involved in WHERE conditions. Then you should evaluate the data manipulation tendencies with respect to all the relevant INSERT, UPDATE, SELECT and DELETE operations.
You could as well evaluate the implementation of “materialized” views.
All the DML code samples shown above, the sample data and other relevant aspects are included in this db<>fiddle so that you can see these exercises “in action”.
Data constraints
It seems this would allow Friendships to be made from a user to themselves, e.g. RequesterId and AddresseeId being the same. Is it possible to prevent this on the DBMS side without triggers? Also, can an ON DELETE CASCADE be introduced to both FKs in the Friendship table, e.g. deleting it if a Friend on one side is deleted? I tried creating such a table but at least SQL Server rejected the table creation due to possible cyclic / multiple cascade paths. – @Ray, on 2022-03-26 15:54:22Z
CHECK constraint
Yes, you certainly can prevent the values retained in the columns Friendship.RequesterId and Friendship.AddresseeId from being identical in the same row without triggers (which, as you know, should be left as a very last resort). In this case I would make use of a CHECK constraint (that i just added to the DDL logical layout shown in prior sections), which allows handling this requirement declaratively (in contrast to the sub-optimal procedural option with TRIGGERs):
CONSTRAINT FriendsAreDistinct_CK CHECK (RequesterId <> AddresseeId);
So, if you want to INSERT, e.g., the row that follows:
INSERT INTO Friendship
(RequesterId, AddresseeId, CreatedDateTime)
VALUES
(1750, 1750, '2022-03-31 16:58:12.000');
…the DBMS (in your case SQL Server) should reject that row and throw a message like this one:
Msg 547 Level 16 State 0 Line 2
The INSERT statement conflicted with the CHECK constraint "FriendsAreDistinct_CK". The conflict occurred in database "Foo", table "dbo.Friendship".
Msg 3621 Level 0 State 0 Line 2
The statement has been terminated.
Existence dependencies and TRANSACTIONs
An important aspect that should be treated carefully when creating the database structure and the related constraints has to do with (1) the order in the creation of the tables and (2) the order of the creation of the constraints, since these factors are instrumental in the representation of the existence dependencies that take place in the business context of concern, so please make sure that you first create the “independent” tables, then continue creating the ones that depend on the existence and constraints of those and so on. As demonstrated in this db<>fiddle running on SQL Server 2019, no issue came about when creating the database structure and constraints.
In that same regard, if you have to DELETE a row that has FK references, instead of using ON DELETE CASCADE actions (which tend to consume a lot of DBMS resources) I suggest that you carry out the required manipulation operations via TRANSACTIONs, and in order to optimize DBMS resources you also have to take into account the existence dependencies of the concerning data. In this way, you would first DELETE the “dependent” rows (in the conceptual hierarchy) and once all those “dependent” rows have been deleted then you carry out the DELETE of the “main” row of interest, all of this in a single TRANSACTION. Similar considerations apply for UPDATES, thus the data hierarchy also has to be respected in order to optimize the data manipulation processes at the physical level.
Let us assume that due to a certain reason you have to DELETE a certain UserProfile and all of its related info, so you would proceed deleting
- all the related rows in the
FriendshipStatus table, then
- all the related rows in the
Friendship table, and after that
- the exact row of concern in the
UserProfile table.
All of the above in that specific order, inside the same TRANSACTION, so as to guarantee that all the operations are either committed or discarded as a single unit of work by the DBMS.
Note: In agreement with the previous deliberations, it is very important to point out that all the INSERT, UPDATE and DELETE operations of a given database should be performed exclusively within TRANSACTIONs inside stored procedures which only some privileged database users can execute. This strategy optimizes data integrity protection in particular and database management in general.
Further constraints
Generally, in scenarios like the one discussed here, a designer has to set up further data constraints, but I am not going to address those aspects because it can be a very didactic endeavour for future readers, and as you know, constraints can change from one business context to the other.
Additional considerations on the Friendship and FriendshipStatus tables
Could you explain little bit more why do we need Friendship table? I imagine if user would like to request friendship with other user it would mean INSERT into FriendshipStatus table with status code R. Then if receiver accepts it then it is another insert with status code A. It seems all relevant data would be kept there - I am confused why do need to store combination of those user ids in another table? Isn't content of Friendship duplicated in FriendshipStatus? Basically if there is combination of userId1 and userId2 in FriendshipStatus it goes to Friendship table. Why? – @Piotr, on 2022-07-30 19:08:10Z
If I address your inquiries here in the answer body I hit the “limit of 30000 characters” within just a few more paragraphs, and the comment box is not well suited for a proper reply (comments are meant to be transient, offer very poor formatting options, permit even fewer characters, etc.), therefore I have uploaded my reply to your questions in this external file.
Relevant posts
You might as well be interested in:
- This answer in which I suggest a basic method to deal with a common many-to-many relationship between two dissimilar entity types.
- The IDEF1X diagram shown in Figure 1 that illustrates this other answer. Pay special attention to the entity types named Marriage and Progeny, because they are two more examples of how to handle the “Parts Explosion Problem”.
- This post that presents a brief deliberation about holding different pieces of information within a single column.
