I am building a database that is based around a business project, in this case called a "Program." Each Program will have multiple Reports, Clients, and Partners.
My first thought was to place each Program, Report, Client, and Partner into their own table and then use a linking table for the Report, Client, and Partner tables to the Program table's primary key.
Can I link all of them with a single table by just making repeat entries? I am not sure if this even makes sense, I am kind of lost as to how one descirbes an ERD in a post like this.
ProgramTable
ProgramID
ReportTable
ReportID
ClientTable
ClientID
PartnerTable
PartnerID
LinkTable
ProgramID (FK) ReportID ClientID PartnerID
My concern is that because a Program may have multiple Reports, Clients and Partners, that I cannot do this in one link table and istead need 3 link tables, one for each. Any help would be great... sorry if I am not to clear on this.