How do I subtable?

[blippy]

I have 3 tables: Projects, Systems, Issues.



There is a many-to-many relationship between projects and systems. One project has many issues. Crucially, each issue belongs to one project. Incidentally, each issue belongs to one system.

My problems revolve around how to structure tables in a sensible way.

Presumably I need to set up another table to record the relationships between projects and systems. Lets call it ProjectsSystems.

Question 1: how do I ensure that a user can't duplicate project-system combinations?

Question 2: should the schema in the Issues table link to Systems table, or to the ProjectsSystems table?

Question 3: how do I set things up so that when the user creates an issue, he can only select systems that are in the ProjectsSystems table for the particular project to which the system relates?

[jzwp11]

Welcome to the forum!

Presumably I need to set up another table to record the relationships between projects and systems. Lets call it ProjectsSystems.

That is correct, so your structure might look like this:

tblProjects
-pkProjectID primary key, autonumber
-txtProjectName

tblSystems
-pkSystemID primary key, autonumber
-txtSystemName

tblProjectSystems
-pkProjSysID primary key, autonumber
-fkProjectID foreign key to tblProjects
-fkSystemID foreign key to tblSystems

Question 1: how do I ensure that a user can't duplicate project-system combinations?

You would create an index for tblProjectSystems that includes both fkProjectID and fkSystemID and set the unique property to yes.

Question 2: should the schema in the Issues table link to Systems table, or to the ProjectsSystems table?

It is hard to answer this question, because these statements are unclear:

There is a many-to-many relationship between projects and systems. One project has many issues. Crucially, each issue belongs to one project. Incidentally, each issue belongs to one system.

If you have a many-to-many relationship between projects and systems, then that implies that a project has many systems and that a system can be associated with many projects. So the question is whether an issue pertains only to the system and is independent of the project (issues would be tied directly to the system and not the combination of project/system), or do the issues pertain to a project independent of the system (issues tied directly to the project and not the combination of project/system), or the issues are related to the combination of project & system. Only you can resolve this since it is your business process. Perhaps if you could provide some examples of what you are dealing with we may be able to help.

Question 3: how do I set things up so that when the user creates an issue, he can only select systems that are in the ProjectsSystems table for the particular project to which the system relates?

There are ways to do this with forms but the table structure has to be worked out first.

[blippy]

You would create an index for tblProjectSystems that includes both fkProjectID and fkSystemID and set the unique property to yes.

OK. Thanks. I got that bit working.

Perhaps if you could provide some examples of what you are dealing with we may be able to help.

The important thing is that issues belong to projects. "Systems" is really just for informational purposes. It's likely that that there is a one-to-many relationship from projects to systems - but I can envisage scenarios where one day there might be many-to-many. So that's how I've set it up.

I now have tblProjectsSystems set up. I am trying to create a form for the issues. The project is available as a drop-down box. Now, the question is, how do I create a drop-down box for the user to select the system, such that it only displays systems which are available for the project selected in the drop-down box; rather than all project-system combinations?

[jzwp11]

You just said this:

The important thing is that issues belong to projects.

Then this:

I now have tblProjectsSystems set up. I am trying to create a form for the issues. The project is available as a drop-down box. Now, the question is, how do I create a drop-down box for the user to select the system, such that it only displays systems which are available for the project selected in the drop-down box; rather than all project-system combinations?

Having to select the system in addition to the project implies that the issues are related to the combination of project and system.

So, what is the correct relationship for the issues?

If issues are only applicable to the project, then this is the structure you need:

tblProjects
-pkProjectID primary key, autonumber
-txtProjectName

tblSystems
-pkSystemID primary key, autonumber
-txtSystemName

tblProjectSystems
-pkProjSysID primary key, autonumber
-fkProjectID foreign key to tblProjects
-fkSystemID foreign key to tblSystems

tblProjectIssues
-pkProjIssueID primary key, autonumber
-fkProjectID foreign key to tblProject
-txtIssue

You would base your main form on tblProjects and in the main form you would have a subform based on tblProjectIssues. If you wanted to show systems related to the project, you would have another subform based on tblProjectSystems

[blippy]

I think I might have explained it wrong. Each issue belongs to a project and a system. Different issues can belong to the same project, but different systems. But there are only certain permissable project-system combinations.

Actually, what I think I need to do is, in the issues form, create an event so that changing the project causes a requerying of the combo box data of the system. I think that's probably the way forward.

[jzwp11]

OK, then your structure would look more like this:

tblProjects
-pkProjectID primary key, autonumber
-txtProjectName

tblSystems
-pkSystemID primary key, autonumber
-txtSystemName

tblProjectSystems
-pkProjSysID primary key, autonumber
-fkProjectID foreign key to tblProjects
-fkSystemID foreign key to tblSystems

tblProjectSystemIssues
-pkProjIssueID primary key, autonumber
-fkProjSysID foreign key to tblProjectSystems
-txtIssue


In terms of your forms, I would create a query based on tblProjectSystems, tblProjects and tblSystems, (make sure to include the pkProjSystID field as the bound field of the combo box) create a "main" form based this query. Disallow editing of data in this form. Create a subform based on tblProjectSystemIssues and put it on the main form. The join should be made between the pkProjSystID and fkProjSystID.

As to selecting the project/system, create two combo boxes in the header of the main form one for the project and one for the systems. The system combo box will be based on a query that uses both the system table and the tblProjectSystems table (again make sure to include pkProjSystID). You will need to use the technique of cascading combo boxes to show only the applicable systems for the selected project (this site has example databases that illustrate cascading combo boxes).

In the after update event of the second combo box, you would use the pkProjSystID chosen to move the main form to that particular record. The code for doing that is shown below. The user would then enter the pertinent issue info in the subform.

'clone the form's table/query into a recordset
Dim myrecset As Object
Set myrecset = Me.RecordsetClone.Clone
'find first matching record in the recordset
myrecset.FindFirst "pkProjSystID=" & Me.NameOfSecondComboBox
'set the form's record to the found record
Me.Bookmark = myrecset.Bookmark

[blippy]

Actually, I don't want to do that either. What I need to do is, I think, set up some kind of index in the issues table with a constraint that the system id is restricted to the permissible project-system combinations.

[jzwp11]

I apologize, but I really do not understand what you are after.