I'm having a hard time finding an answer related to this on google, maybe the masters in here can help.,..
where I work now, I see the db design without using foreign keys and prefer to create indexes on columns that are often querying,
so which one is better or is there any consideration in using it? because in the previous workplace always used foreign keys.
Top comments (1)
I think the problem is that the foreign keys and indexes are two different concepts. I don't think they are things to be used interchangeably.
Foreign keys are the keys indicating the relation from the column of one table to the column of another table.
Indexes are created to store the data in a way that the searching is faster.
I think the confusing part is that the foreign keys must be indexed (at least in MySql, it is so). However, while choosing which one you need to use, you need to think of that exact case and not pay too much attention to what was used throughout the app. I assume the use cases for the rest of the app may be different, that's why maybe coincidently, most of the parts of the app use indexes.
Using foreign keys while using a field connected to the other table may later take advantage of cascading operations and possibly avoid outdated garbage data.