Tables and Relationships 2

[topgeek]

I am trying to create a database that will allow the user to Create new projects and assign them with a contact and organisation.

In other words, there should be Organisations that can have multiple contacts linked to it and each contact can have multiple conversations attached to it.

An organisation can also have multiple Projects attached to it whioch are in turn attached to a contact.

Can anyone help me with the table setup and relationships?

Right now I have four tables:

tblNotes
tblContact
tblOrganisation
tblProject

tblNotes with a foreign key (ContactID) is linked to primary key (ContactID)in tblContact.

tblContact with the foreign key (OrgID) is linked to primary key (OrgID) in the table tblOrganisation.

tblOrganisation with the foreign key (ProjectID) is linked to primary key (ProjectID) in the table tblProject

I can't seem to get the tables to link together for example I cannot get my tblOrganisation to see many projects asrequired.

Can anyone help wuth a suggested table layout and relationships?

Many Thanks

 

[TomCologne]

If an organisation can have multiple Projects but not vice versa, "OrgID" should be foreign key in tblProject.

If a project can have multiple participating organisations you'd need a join table with fields "ProjectID" and "OrgID".


TomCologne

 

[willir]

TopGeek

I am trying to create a database that will allow the user to Create new projects and assign them with a contact and organisation

Your design so far is something like...
tblNotes
NotesID - primary key
ContactID - foreign key to tblContact

tblContact
ContactID - primary key
OrgID - foreign key to tblOrganization
ContactLastName
ContactFirstName

tblOrganisation
OrgID - primary key
ProjectID - foreign key to tblProject
OrganizationName

tblProject
ProjectID - primary key
ProjectName

So here are some questions, for clarification:

Is a Project assigned to an Organization or to a Contact who works for an Organization? Or are you referring to the fact that an organization is the customer for the work to be done on a project. Your objective suggests that the Organization is the customer.

Can a person work for more than one organization? (We already know an Organization have many contacts.) Is the contact who belongs to an organization, the customer for the project, or the owner of the project? Can a contact work for more / be the contact for more than one project?

If the Contact is not the owner / manager of a project, you probably want to capture this information too.

...Moving on

I can't seem to get the tables to link together for example I cannot get my tblOrganisation to see many projects

Have you defined the relationships? From the menu, "Tools" -> "Relationships". Add your tables. Click on the primary key in one table and drag it to the foreign key in the other table. For example, click on tblOrganisation.OrgID and drag it to tblContact.OrgID. A window will popup "Edit Relationships". Select "Enforce referential integrity".

Hint:
Define the relationships using the Relationship GUI tool first before creating your main form / subforms. This way, Access will automatically link the form and subform using the LinkChild and LinkMaster fields.

Also, by creating your relationships first, the QueryBuilder will automatically link the tables correctly when you create a query using more than one table.

Comments on design:
Your notes only track comments per contact. This is fine if you have only one contact and one project and one organization.

However, I suspect more than contact can work on a project. For example, vacation or transition during a project. You should tie the note to a project, and perhaps to an organization...

tblNotes
NotesID - primary key
ContactID - foreign key to tblContact
OrgID
ProjectID
NoteDate
...etc

Richard

 

[topgeek]

Thank you for your detailed reply.

You are correct - the organisation is the customer, we can potentially have several organisations applying for the same projects. Similarly we can be dealing with multiple contacts(persons) at a single organisation. A person can only work for one organisation. The contact could be the owner of the project.

I have already tried to link the tables but I'm not sure if my table structure is right in the first place.

I have only worked on projects with two or three tables linked along with a few lookup tables before so any additional guidance would be really appreciated !!

Many Thanks

 

[willir]

Okay, you have

One organization to many contacts. OrgID, as the primary key, from the Organization table is linked to the OrgId, as the foreign key, on the Contact table.

You have a one-to-many relationship between the Organization table and the Projects. More on this later.

You seem to have a many-to-many relationship between Projects and Contacts. A Project can have many Contacts. And I strongly suspect a Contact can work on more than one Project. For example, a technical computer person can work in their capacity as IT to support / work on several Projects.

In addition to the above tables, tblContact, tblProject and tblOrganization, you should consider adding a table to capture the apparent M:M relationship between Contacts and Projects.

Note that in a way, the Project / Contact relationship is more important than Project / Organization...
- Because a Contact can only work for one Organization, you can figure out which Projects are being worked at an Organization.
- The Contact / Organization relationship includes the capacity the Contact supports the Project.
- Notes made by Contacts need to be related to the specific Project.

Here is one way to design the table to reflect the Contact / Organization relationship...

tblProjectContact
ProjectID - foreign key to tblProject
ContactID - foreign key to tblContact
Position - text (eg: owner, manager, technical, purchasing...)
DateActive - date
Active - boolean, yes / no

Primary key = ProjectID + ContactID

...Moving on

I have already tried to link the tables but I'm not sure if my table structure is right in the first place

Were you successful in creating the links? You should see a line with a "1" on the "one" side and an "infinity" symbol / loop on the "many" side. If you see a line but no 1:M indication, it suggests you have not setup the relationship to "enforce referential integrity". Enforcing referential integrity means that the parent record has to exist before you can create the "many" or "child" side.

Creating the relationship links is fairly easy since Access offers a nice GUI interface. The requirements are...
- the data types on both sides have to be the same
- it is much easier to create the proper relationships if there is no data in the tables.

Once you have created the relationships. Enter a small amount of test data and then create queries to test the relationships. The queries should display the results as expected.

After the testing confirms the relationships and design, create the forms, do some more testing, and then create the reports.

Then deleted any irrelavent test data and go for the gusto...

Richard