CODEWITHSUNDEEP
database-design
Yuval Karmi
Yuval Karmi

Reputation: 26713

Is it bad to have "foreign key redundancy" in a database?

I am designing a database structure with the following simplified example:

Team has many members
Member has many clients
Client has many projects

Assuming my objects have these parameters:

Team: id, type
Member: id, team_id, name
Client: id, member_id, email
Project: id, client_id

It's simple enough to find a project's client, or a client's member, or a member's team.

However, assuming I want to find a project's team, for example, I have to first find a project's client, then a client's member, and then the member's team.

I could add a team_id directly to the project, like this:

Project: id, client_id, team_id

I realize, however, this adds a certain level of redundancy since that information is available by "going up the relationship tree." Is that a bad idea?

Thanks!

Upvotes: 12

Views: 2348

Answers (2)

Erik Funkenbusch
Erik Funkenbusch

Reputation: 93424

It's not like you have to do 4 queries here. You just do a join that joins all tables in a single query. This does not add a lot of complexity, but it does add a little. I would just go with what you have.

Upvotes: 1

Eric J.
Eric J.

Reputation: 150108

Whether this is a bad idea or not depends on the typical use cases for the database.

Adding additional foreign keys increases the cost of modifying the structure (INSERT, UPDATE if modifying relationships, DELETE).

Not having the additional foreign keys increases the cost of queries that would otherwise benefit from their presence.

If the project structure does not change very much but you do query the structure frequently, the extra foreign key is likely to be a net positive. If in doubt, create the structure with reasonable test data and benchmark some queries you think will be typical.

Upvotes: 2

Related Questions