star join?

[ltofias]

Hi, Im pretty new at this so any help is greatly appreciated! I want to link a list of consultants and a list of contacts to a list of projects. So these are three seperate tables Projects, Consultants, and Contacts. If the keys are unique for each table, how do I link them? Ideally on one layout you would be able to view a selected project with the specific consultant company who worked on the project and the specific contact for that project. I have read a ton on the subject, but it would really help me if someone could point me in the right direction.

[Fenton]

Since there are multiple instances of Contacts on a Project, and/or multiple instances of Consultants on a Project, you will need "join" table(s) between your main tables. They would have at least the ID of each of entities, as well as other relevant info, such as the date when they "joined" the project.

What is not clear to me is whether "contacts" are "contacts at the consulting firm", or whether they are "clients". These are two very different things. In the first case, Contacts are children of Consulting. In the second they are completely independent.

If they are independent, then a join table would be between Consultants and Projects (ProjectConsultants), and another between Projects and Contacts (ProjectContacts).

[ltofias]

Thanks! Contacts could be from a consultant company or from a client company or anyone else involved in the project. For the Company ID in the join table, can I use company name? and how do I match up company name with a project? do I manually enter the Company ID in the join table that contains the project ID? any help is appreciated...

[Ender]

The process you use to set the foreign keys may vary depending on the actual workflow of the users.

For the Company ID in the join table, can I use company name?

I would caution against this. the Company ID should be something unique to each company, and something that won't change. Using names or phone numbers is bad because they do change, and they are not always unique. It's better to use auto-entered serial numbers for relational keys, and use value lists or selection portals to choose them (the user need not even see the Company ID, if you design it right.)

[ltofias]

point taken. thanks. in the actual join table how do I make the project info and the company info match up, so that a record in ProjectCompany shows the correct company that goes with that project? I think Im a missing a step somewhere just not sure where.

Edited March 16, 2006 by Guest