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!