Relationships look right?

[Sketchin]

I have a project management database that includes a projects table, and a companies table. In the projects table, I need to track the company that the project is for, and I need to track what ASP (Approved Service Provider) is assigned to the project. This requires me to have CompanyID and ASPID both related to CompanyID in tblCompanies, as shown in the attached picture.

Does this logic violate relational integrity rules?

 

[CJ_London]

You may want to check your circular one to many relationships between your project, HQPProject, Contacts, contactscompanies and companies. I'm not sure without testing but you may find you can't append one or more of the tables - I suspect HQPProject.

There has been a recent thread about circular relationships on this forum here http://www.access-programmers.co.uk/forums/showthread.php?t=260420 which has some relevant references.

Also not sure why you have the ContactCompany table? why not just link to the companyid field in contacts?

 

[Sketchin]

Ahh...sorry about that, I was in the midst of re-designing so that I can assign more than 1 company to a single person. The tblcontactscompanies table will be removed and tblcontacts will have a 1 to many with tblcompanies.

 

[Sketchin]

So I have read the material that you posted and still have no idea how I could possible get rid of the relationship between tblproject and tblJoin_HQP_Project. For the record, HQP stands for highly qualified person.

My problems are that:

1) each project must be associated to a company
2) each company has people
3) each project also has multiple HQP's that are people.

I just don't see any other way to indicate that multiple people are associated to this project without using a join table.

:banghead::banghead::banghead:

 

[Sketchin]

Here is an updated, clearer jpg of my relationships

 

[CJ_London]

I'm not saying it won't work, just not sure if it will.

Suggest you populate the tables with some test data and see if you experience any problems.

In one to many relationships the one part needs to exist before you can complete the many part.

The new layout is easier to read so tblcompanies is completed first, then tblproject and tblcontacts and finally the HQP table so probably not a problem. Compare this with your original design which is what I based my comment on.

The relationships are there to enforce a structure. If you were for example to leave out the join between project and HQP, there is still nothing to stop you having a combo box on a form for your hqp projid field with a rowsource which limits the projects that can be assigned to the record to those belonging to the company. By building it into your relationships, this is enforced and you would not be able to save a record without assigning both a project and a contact.