2

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.

Improve this question
0

You should start by writing all the relationships between those tables. From your description, I could not find out how Partner would be related to Client, Program or User. However, the diagram below is a start based on the information provided. Note that you can't use 1 Link table to link all table from what I understand in this case.

EDIT - New ERD based on new requirements

enter image description here

Improve this answer
6
  • OK, I would reply to this, but as it turns out this StackEchange won't let me post pictures so I can't share an ERD I made up... ugh, your ERD makes some sense, but I may just be over my head here.
    – Steve
    Jul 17 '12 at 13:00
  • Sent you the ERD.
    – Steve
    Jul 17 '12 at 13:12
  • Good, will take a look and communicate further here.
    – NoChance
    Jul 17 '12 at 13:13
  • Your diagram is possible, however, I see that it does not tie users to reports and treats partners as a different entity from users. We need to be very clear about the relationships. Also, the link table between reports and programs is probably not correct because it says that one report from program A could be (used, shared, generated, etc.) from program B - This is possibly not the case.
    – NoChance
    Jul 17 '12 at 13:21
  • Alright, so in terms of Users and Partners, Partners would be other companies and Users would be "members of" Partners essentially. As for the Reports, it's LIKELY and I stress LIKELY that a Report will be unique to a Program, but this may not be the case. It may in fact be possible that Program A and B share Report C.
    – Steve
    Jul 17 '12 at 13:27
2

It is very clear but you should probably first read some "introduction to relational databases" before you proceed, because from the way you are asking I feel it would be beneficial for you to get some basics first. No offence, it may save you lot of time later.

To give you few fast rules of a thumb

  • You never ever connect more then two tables with one link. Try to connect tables step by step. Always take a pair of them and try to join them. Do not try to solve the whole thing at once.
  • you only need the link table if there is so called many to many relationships, i.e. if a client can have many programs and at same time one program can have many clients. It seems to me that it might not be needed in your case.
  • In all other cases you just place "anothertableID" to one of the tables. In your example it would be good to start with putting ProgramID to ReportTable, ClientTable, PartnerTable. Maybe it is what you need.

To give an more elaborated answer you should give more information about structure of your problem.

Improve this answer
5
  • Thank you. I have some understanding of the basics, my issue really is more to your 3rd point. Each Program will have multiple Clients, Partners, and maybe Reports, but Reports may be a 1 to 1 in 90% of the cases. When I designed ERDs in grad school I never had a table that would require multiple values for so many of it's columns. Laying this out without creating duplicate columns has me with like 4 link tables and I getting nervous with that.
    – Steve
    Jul 16 '12 at 20:10
  • To add some additional info, each program would eventually have methods, duration, evaltyoes, and a whole other list of attributes that all have multiple values. The only approach I am familiar with to normalize this, is to break everything into its own table to the relationship is between the primary key of the primary table to that attribute. This is leaving me with a rather large amount if linking tables, I am just unsure with this.
    – Steve
    Jul 16 '12 at 20:32
  • Raports are 1 to 1 to what? Steve - I feel you are missing one basic point: You never ever connect more then two tables with one link. Do not try to connect 4 entities with one link, it won't work.
    – gorn
    Jul 17 '12 at 22:46
  • @Steve Start with this: Program[id] Client[id,program_id] Report[id,program_id] Partners[id,program_id]
    – gorn
    Jul 17 '12 at 22:51
  • @Steve - yes if you have more values for some attribute (for example many methods for project), than it is best to do new table Methods having project_id in everyrow in addition to information about that method. Yes, you can have lots of tables.
    – gorn
    Jul 23 '12 at 16:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.