Is there a performance problem in using many foreign keys (FK)? If so, is it bad to have foreign attributes without being FK?

Asked

Viewed 148 times

1

To clarify how much can be "many foreign keys", I will explain the context:

In a system, I want to store by whom a certain record has been edited ( updatedBy) or raised (createdBy). This can happen by:

  1. A user, authenticated in the system;
  2. By the system, in an automatic action, for example.

The tables (which today are 8, but I imagine this situation in any system) seem something like:

 _____________________________________________________
| ... | createdBy | createdAt | updatedBy | updatedAt |
|     |           |           |           |           |
|_____|___________|___________|___________|___________|

I thought of three alternatives about as store the fields createdBy and updatedBy:

  1. Store the user name;
  2. Store some user identifier, not necessarily the primary key, and without creating foreign key;
  3. Store primary key by creating a foreign key.

The problems that I see in every alternative:

  1. User can change name, then data would become inconsistent;
  2. It does not seem correct to have an attribute that works as a foreign key but is not a foreign key, but I have no knowledge to elaborate this point and is the option that I am using now (in development environment);
  3. It seems the most correct way, but all tables would have connection with the table Usuario (including her with herself), createdBy and updatedBy would be optional foreign keys.

And then we go to the questions:

  • On Alternative 2: There is reason to say that it is bad use attributes that function as foreign keys but are not foreign keys?
  • On Alternative 3: There is a performance problem in using many foreign keys?

If there is a better alternative that I haven’t mentioned, it will surely add to the answer.

If the database choice is relevant to the response (due to DBMS), I am using Mysql.

mysql sql database modeling foreign-key

  • "Is it right to say that it is bad to use attributes that function as foreign keys but are not?" , are you sure you meant what you wrote? If they are not foreign keys it is completely wrong to qualify them as such. It is obvious that the use of foreign keys affects performance but you will have a more robust model and with coherent data, it remains for you to assess how important this is in your system.

  • These attributes, in practice, would not be FK, but queries would be made as for example SELECT * FROM Usuario JOIN Roupa WHERE Usuario.username = Roupa.updatedBy, That’s what I meant by "they work like foreign keys but they’re not"

  • It does not seem to me a very common requirement that the user who created/modified a record needs to be saved (and what he deleted too, at least in many places this is only marked in the record, and not deleted effectively). Could you tell me why you need this? The alternative to my view would be that the schema itself already allows to have this information, depending on how it is modeled.

  • I worked on a project that they solved that way, so it was the way I "learned". This type of information would also be displayed on client, in some situations, so I imagine you need to be in the comic book. I don’t understand what you mean by "the schema itself already allows me to have this information," so I don’t know if it would meet my need. If you have any reference you can share that I read :), or if you want to come up with an answer with this, I believe it is also valid as a "better alternative", as I mentioned at the end of the question.

  • 1

    I would say create an audit table, move these fields there and do FK for this audit table... At the end of the day, without a history, these camps are useless...

  • @Rafaeltavares I meant that the modeling itself already indicates which user has made the change (in another table, different from the one that has the record in question). But I don’t have an example at hand.

Show 1 more comment

1 answer

4


The first option is really not very good, but there are cases for use. The second decreases the field size but has the same problem as the first. It does not guarantee the canonicity of information, but can be done manually.

The third usually makes more sense in most cases, but we don’t know if it makes sense in your case. We have no details, it may be that all this is not the most appropriate.

There will be some performance cost in keeping foreign keys, but it’s usually nothing too big. There are cases that there may be gain, but in general only compared to doing the same manually.

In general, you do what you need to do, which is more correct, and if you have performance problems you start doing optimizations, if possible without harming the operation. Measure to make the decision, don’t make it because it feels best. This is premature optimization because the intuitive doesn’t always work, especially in databases.

I realize that many people are worried about performance without even getting anywhere near that being a problem.

If you need performance more than anything else then do it fast and don’t worry about anything else, but it can complicate development, not everyone knows how to handle it to really make it fast. Don’t go manually doing what the database does automatically when you have FK, then it’s worse.

It is not so common but some people prefer to use foreign keys and make validations only when it is necessary. Depending on how the application is developed can be a good and a viable optimization. It’s not the end of the world not having Fks, as long as you know what you’re doing.

Obviously I assume that everything is configured properly, mainly there are suitable indexes to facilitate the life of SGDB checking foreign tables. I will not consider the error because it makes any option unfeasible. Done this the cost is low.

There is no cake recipe that always works, knowing how to make each case can work. You have to understand what happens in each situation, the commitments of each choice within its context.

Browser other questions tagged mysql sql database modeling foreign-key

You are not signed in. Login or sign up in order to post.