8
I don’t have much knowledge in database structuring. I have been trying to improve myself and I am seeking best practices on how to create and structure tables.
Currently I usually create link tables and combine the ID’s of each table turning them into a primary key (or composite key).
This is good practice or should I always create a primary key for each table?
10
The primary key has two main functions:
Although a simple primary key or a composite key serves this purpose, the simple primary key has the following advantages.
1 - It is easily created automatically by the database engine, no need to check if it exists before doing INSERT.
2 - Relationship definition is simplified because only one column is used.
3 - Uniqueness can be easily achieved by using a single index (UNIQUE INDEX) in the compound fields.
Note that "Identifying unambiguously" is different from "ensuring oneness". 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 good practice I don’t know, but the above reasons for me are enough for me to use simple primary keys(automatic).
7
Forget this "good practice" business. Learn all that is necessary (this site is great for clearing 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 unique solution for all cases and this does not work well.
Composite keys exist because they are useful. If there is a natural key that can 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 suitable.
It’s very common for people to think that a natural key is appropriate and actually not to be. It can be great at a given time and then show itself problematic. That’s why it is very common to choose a replacement key, such as the ID or something like that.
I particularly always analyze by default whether it is possible to use a natural key. Almost always the decision is that it cannot.
In some cases one may think of using a composite substitute key, although some will say that it actually ends up being natural. A key that is a ID (which functions as a foreign key as well) plus a sequential item number, is a key that can be considered substitute and be composed. This is common in order registers, for example.
The link tables are usually like this, almost always use foreign keys related to two or more tables and together form a composite primary key. Use well suited in most situations. I find it quite rare to need a replacement key for this, but need may occur.
I appreciate your reply bigown. But I disagree when you say it. "Forget this 'good practice' business. In the same way you say "Composite keys exist because they are useful". Good practices also exist because they are very useful. I think it’s only up to us developers to know how far to follow them according to our needs. I agree with you that we should not blindly follow good practice. But I believe that following good practices and standards is essential to develop well a project.
@Alan you may disagree, but good practices are not helpful, doing right is useful. Following what you don’t understand is very damaging to any project. And good practice is to say "do it" without explaining why. my extensive experience shows that they cause more problems than they help. I’ll say it again. Understanding what the problem is, what the solution is, why it happens and applying something according to the case is useful. Little rule like "do this, don’t do that" just creates problems. Each problem is unique. If the question referred to a specific case, then to give a specific answer.
Good. For me it has been very helpful. I agree with you on many things you have said. But I think we will each be on one side in this! kkkk! Good anyway. Thanks for sharing some of your experience with me!
@Alan I’ve been programming for many years, always trying to do better, and it’s been a while since good software practices are so useful to me. But I’m always learning new things for myself: play tennis, play guitar, build furniture, climb mountains... Every time I try to learn something outside of the software it is always evident to me that good practices exist in all fields, and what would become of us beginners without the instructors who show us good paths allowing us to do well already in the beginning, even before we still fully understand what we are doing? Long live the good practices! :-)
6
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 diffused as a precaution.
To be more clear, a practical example of using a primary key, even with a composite key, is the performance at the time of data replication (re-plication). When there is no explicit identifier index, replication takes longer to find a uniqueness.
Finally, it is not mandatory for a table to have a primary key, but it is recommended that it does. For the reason described above in the case study example with data replication and other diverse cases, obviously all related to performance and uniqueness that identifies "that" Row as unique.
Browser other questions tagged mysql sql database modeling
You are not signed in. Login or sign up in order to post.
related: http://www.agiledata.org/essays/keys.html, does not answer your question, but can help you understand some factors.
– Tobias Mesquita