I deem that there are multiple (very important) aspects that you need to consider before deciding which tool you are going to employ to develop your project.
The primary objective should be to manage the pertinent data as it is, a quite valuable organizational asset, and a reliable manner to achieve said objective is by way of technical means that are supported on sound theory.
In this regard, it is worth mentioning that the success of a determined database does not only depend on the database management system (DBMS) of choice but also on a number of factors, such as:
- Its logical model
- Its physical implementation settings
- Its qualified administration
Since you are considering a SQL platform as a tentative DBMS, this fact suggests the intention to implement a relational database, so I will focus on this respect throughout the present answer.
Although Dr. E. F. Codd (a Turing Award recipient) published his seminal paper A Relational Model for Large Shared Data Banks back in 1970, I really consider that his exceptional work remains unparalleled and state-of-the-art because, e.g., it is solidly based on first-order logic and set theory.
When implemented in a SQL platform, a well designed database permits obtaining many of the advantages proposed by relational theory. In contrast, a poorly designed database can easily become inoperative. Having said that, it is important to be aware that the development of a relational database demands a firm understanding of the specific business domain of interest. Therefore, you have to analyze and classify all the things of concern, and these tasks require strong data modeling skills. In this way, if you have a clear knowledge of the business context and good modeling abilities, you will be able to create a strong logical database structure which represents the bussines context with precision and can be easily extended and modified.
Once you have developed a stable database (taking into account the particulars of the DBMS you decided to use) and launched your system, it is time to concentrate your efforts in managing the server and, as one would expect, the data, and here is where database administration skills are particularly critical.
So, as you know, all of these requires a certain amount of experience, which you can only gain by embarking on several projects, preferably under the supervision of a specialized colleague or team.
Aspects to take into account
So, in order to make an informed decision, you should:
- Continue asking good questions.
- Take the time to learn about relational theory.
- In this regard, I highly recommend Dr. Codd bibliography, so that you learn directly from the originator of the relational paradigm.
- Enhance your data modeling skills.
- You might find IDEF1X of interest. It is a powerful and expressive technique that was defined as a standard in 1993 by the United States National Institute of Standards and Technology (NIST).
- In this meta post I discuss some elementary modeling points, and in this answer I deal with a basic database structure, in case you are interested.
- Evaluate the capabilities and limitations that pertain to MySQL.
- Asses other SQL systems and compare them with MySQL.
- It is worth noting that the major platforms have been heavily optimized over the years (or even decades).
- There are different open source alternatives that are very interesting.
- Study the different SQL dialects available.
- Get SQL hands-on experience following the theoretical stipulations so that you can see their value in action.
- Find out the theory that serves as a base for MongoDB.
- Study the tools that are similar to MongoDB.
- Compare MongoDB (and other resembling tools) with SQL software (and also with pre-relational technology).
The Profile table Primary Key definition and Indexes
One part of your question that called my attention in a particular way was the fact that you defined all the columns of the
profile table as the PRIMARY KEY, which you explained in the following comment:
[…] yeah they are primary keys, I make them
pk because as far as I know that makes them index and can speed the SELECT operation on that table. And I meant we may have to add more indexes (
pk) to the
profile table. I have read that doing ALTER table with lots of rows can last very long and can be complicated, also I will like to do sharding or whatevery technique may help on performance.
Thus, there are some fundamental (and very relevant) points about relational keys and index structures that need to be clarified.
PRIMARY KEY (PK) represents a logical element, and it is a column (or a combination of columns) that holds values that uniquely identify a given row in the respective table. A table cannot be set with more than one PK.
At the physical level, a PK usually has a subordinate
INDEX that, apart from speeding up the data retrieval (as you have rightly mentioned), also helps to ensure the uniqueness of a determined row (so said
INDEX is, in fact,
A table can have one or more
ALTERNATE KEYs (AK), which are logical constituents as well. An AK is a column (or combination of columns) that retains values that uniquely identify a certain row in the corresponding table, but was not chosen as the PK.
An AK can be established via a
UNIQUE CONSTRAINT, which is commonly assisted by a physical
INDEX that enhances retrieval speed and, naturally, protects row uniqueness.
Indexes on columns that are not (or are not part of) Primary or Alternate Key definitions
Columns that are not (or are not part of) PKs or AKs can also be
INDEXed if such approach accelerates some of your queries. As a consequence, you do not need to add new columns to a PK in order to obtain the physical advantages, you just have to incorporate them to a composite non-unique
INDEX (or create a non-unique
INDEX for each corresponding column, when necessitated) without adding them to the PK, since by doing so you would devoid the PK definition of its contextual meaning.
Entity types, Keys and meaning
If the people involved in a given context has determined that a certain kind of thing, i.e., an entity type has organizational significance, then each instance of said entity type must be differentiated by the value (or values) of one (or more) of its attribute(s), hence PKs and AKs are essential qualities of the data and they depend exclusively on semantic aspects. Each entity type should be set as a table in a database structure; every entity type instance should be
INSERTed as a row in the appropriate table.
So, I deem relevant to state that, just like creating a database and tables inside a server does not necesarily mean that such database and tables are relational, labeling columns as keys does not necesarily mean that they are, in fact, keys. Thus, since keys are an intrinsic characteristic of the data, their identification depends on the modeler competence, and their correct implementation in a server depends on the modeler proper declaration.
Logical and physical
As you can see, it is very important to distinguish logical from physical elements. Summing up, a logical (or abstract) component depends directly on the meaning of the data; in contrast, a physical (or concrete) construct is a mechanism that is used “under the hood” so that a DBMS can —for instance— facilitate data retrieval, support the logical definitions made by a database creator, or both.
Base and derived tables (or relations)
With a SQL system, you can define base tables (via the DDL
CREATE TABLE statements) that shape the structure of the database, but that is not all there is to it, since you can as well obtain multiple derived tables once you need to retrieve result sets which combine columns from different tables, e.g., by virtue of a
SELECT statement that
JOINs said tables. You can define said derived tables as
VIEWs, and also query them directly if necessary. This is just one good example of the versatility that is offered by SQL platforms, since you would always be working with the same kind of structure, a table (or relation).
Of course, you can also make use of the built-in server functions in order to make different kinds of calculations, create computed and concatenated columns, obtain statistiscs and keep on creating queries that you did not even imagine at design time.
If, as time passes, the data users define new contextual things of interest, you can perfectly cover their needs by adding new tables to your database and, yes, you can combine the previously existing ones with the fresh tables and produce brand new derived relations.
As you can see, the possibilities offered by a relational approach are huge.
JOINs might seem a bit cumbersome, in case you face a problem with a specific query, you can come to DBA.SE and ask for help. There is a good amount of users that are very skilled and experienced, and quite probably more than one might like to offer their valuable assistance.
In this regard, I should say that this kind of operation has been highly optimized at the physical level by multiple SQL vendors. So, in the suitable conditions (i.e., performed in a well designed database)
JOINs are decidedly fast.
A relational database stores assertions about real world facts, and an exact fact happens one single time. So from a logical perspective, storing the same fact more than once is unreasonable and unnecesary.
Redundancy eventually leads to inconsistencies. For example, suposse that:
- Someone has retained the same piece of information twice in a certain database.
- Later, someone else comes and
UPDATEs only one occurrence of the duplicates. As a consequence, the other occurrence is not up to date anymore.
- Successively, another person
UPDATEs the occurrence that had not been modified so far. In this manner, both duplicates have undergone different changes at distinct points in time.
- Then, when someone is interested in retrieving the piece of information in question, he or she can find two different versions of it.
- Which version can be considered the correct, reliable one?
- Which one reflects the real world accurately?
As you know, this phenomenon can even have legal implications, a situation that surely is of enormous importance.
Furthermore, the time and effort that has to be employed to handle such inconsistencies (perhaps by some kind of update synchronization) should be better devoted to tasks that actually produce value for your organization. So, I recommend avoiding their storage by design and keeping the logical consistency of your database intact.
Tables with big amounts of rows
There are multiple database instances retaining billions of rows across numerous tables that serve their users at really high speeds, but this, again, is a result of a proper design made by qualified practitioners. So, the problem is not the amount of information stored, but the way in which said information is managed.
Multiple applications working with the same database
A relational database is meant to serve multiple application programs at the same time. So you can have, e.g, one or more web apps, one ore more desktop apps and one or more mobile apps, all working toghether with your database simultaneously.
So —using programming jargon— one must make sure not to couple the database with the code of any of the apps; keep each software component separated from the others but, at the same time, connected.