I do not have much knowledge in database design. I have tried to hone myself better and am looking for best practices on how to create and structure tables.
I currently create binding tables and combine the ID's of each table by turning them into a primary key (or compound key).
Is this a good practice or should I always create a primary key for each table?
The primary key has two main functions:
Although a simple primary key or composite serves this purpose, the primary key has the following advantages.
1 - It is easily created automatically by the bank engine, and it is not necessary to check if it exists, before doing the INSERT.
2 - Setting relationships is simplified because only one field is used.
3 - Uniqueness can be easily obtained by using a unique key (UNIQUE INDEX) in compound fields.
Note that "Uniquely identifying" is different from "ensuring uniqueness". The first is a function of the primary key while the second is a function of an index declared as UNIQUE INDEX .
If it's a good practice I do not know, but the reasons above for me are sufficient for me to use simple (automatic) primary keys.
Forget about this "good practice" business. Learn everything that is necessary (this site is great for clarifying your doubts), analyze the specific problem and apply the best technique for the case. Good practice is to follow cake recipe blindly, is to look for a single solution for all cases and this does not work well.
Composite keys exist because they are useful. If there is a natural key that may be suitable and if the natural key is formed by more than one column, you can use it without problem. Just make sure this key is really appropriate.
It is very common for people to think that a natural key is appropriate and not actually be. It can be great at a certain time and then prove problematic. So it's very common if you opt for a substitute key, such as ID or something like that.
I am always parsing by default if it is possible to use a natural key. Most often the decision is that you can not.
In some cases one might think of using a compound substitute key, although some will say that it actually turns out to be natural. A key that is a ID (which functions as a foreign key as well) plus a sequential number of the item, is a key that can be considered substitute and composed. This is common in order registrations, for example.
Connection tables are usually like this, they almost always use foreign keys related to two or more tables and together form a composite primary key. Use well in most situations. I find it very rare to need a replacement key for this, but need may occur.
Good practices exist to prevent future problems.
They are recommended by those who go through experiences that others might someday experience. Usually something inevitable.
When it becomes something common and inevitable to occur, then it is widespread as a precaution.
To be clearer, a practical example of using primary key, even with compound key, is performance at the time of replication. When there is no explicit identifier index, replication takes longer to find a uniqueness.
Finally, a table with a primary key is not required, but it is recommended that you have one. For the reason described above in the example of the case study with data replication and other miscellaneous cases, obviously all related to performance and uniqueness that identifies "that" row as unique.
