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).
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—.
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.
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.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:
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:
AddresseeId values —taken from the concerning
the new and meaningful
StatusCode value —drawn from
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
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 |
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.
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.