9

Say I have a table called User_FriendList, which has the following characteristics:

CREATE TABLE User_FriendList (
    ID ...,
    User_ID...,
    FriendList_IDs...,
    CONSTRAINT User_Friendlist_PK PRIMARY KEY (ID)
);

And let us suppose that said table holds the following data:

 +----+---------+---------------------------+
 | ID | User_ID | Friendlist_IDs            |
 +----+---------+---------------------------+
 | 1  | 102     | 2:15:66:35:26:17:         |
 +----+---------+---------------------------+
 | 2  | 114     | 1:12:63:33:24:16:102      |
 +----+---------+---------------------------+
 | 3  | 117     | 6:24:52:61:23:90:97:118   |
 +----+---------+---------------------------+

Note: The “:” (colon) is the delimiter when exploding in PHP into an array.

Questions

So:

  • Is this a convenient way to “store” the IDs of a FriendList?

  • Or, instead, should I have individual rows with only one single FriendId value in each of them and, when I need to retrieve all the rows of a given list, simply perform a query like SELECT * FROM UserFriendList WHERE UserId = 1?

  • 3
    I recommend you read this: stackoverflow.com/questions/3653462/… – tombom Apr 20 '16 at 15:17
  • It is a somewhat convenient way to store the IDs, as long as you are not planning to do anything with them and don't particularly care about data quality. – mustaccio Nov 22 '18 at 15:54
16

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:

Figure 1. User Friendships IDEF1X Diagram

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)
);

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 (1) the logical-level benefits offered by the relational model and (2) 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 (i) expand the conceptual schema and (ii) 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:

Figure 2. Friendship Status IDEF1X Diagram

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)      
);

Consequently, 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          | Bloqued   |
 +------------+-----------+

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:12:05.000 | A          |        1748 |
 +-------------+-------------+-------------------------+------------+-------------+
 |        1750 |        1748 | 2016-04-04 10:57:01.000 | B          |        1750 |
 +-------------+-------------+-------------------------+------------+-------------+
 |        1750 |        1748 | 2016-04-07 07:33:08.000 | R          |        1748 |
 +-------------+-------------+-------------------------+------------+-------------+
 |        1750 |        1748 | 2016-04-08 12:12:09.000 | A          |        1750 |
 +-------------+-------------+-------------------------+------------+-------------+

As you can see, it can be said that the FriendshipStatus table serves the purpose of comprising a time series.


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.

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.