Menu
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!
560
Another Database Design Mistake to Avoid is the Redundant Foreign Key. I've seen a redundant FK directly to the parent, of the parent. In this example, it is the FK relationship from table Three to One.
Data redundancy occurs when the same piece of data exists in multiple places, whereas data inconsistency is when the same data exists in different formats in multiple tables. Unfortunately, data redundancy can cause data inconsistency, which can provide a company with unreliable and/or meaningless information.
Having active foreign keys on tables improves data quality but hurts performance of insert, update and delete operations. Before those tasks database needs to check if it doesn't violate data integrity. This is a reason why some architects and DBAs give up on foreign keys at all.
Redundant data is a bad idea because when you modify data (update/insert/delete), then you need to do it in more than one place. This opens up the possibility that the data becomes inconsistent across the database. The reason redundancy is sometimes necessary is for performance reasons.
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.
154
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
