Database and Relationships

[daveydrake]

Hey. I am working on some sort of client + project management system. Each client can have many projects, many projects can have many staff. The Link (Junction) table allows for many staff members to be linked to the projects. I have attached the relationships which have been put together in Access 2007. Firstly, is this correct?

In order to display the data in my VB forms, I am making use of OleDb, by having a OleDbDataAdapter and DataSet. I am using SQL queries to select, insert etc data from the original database tables.

My issue; I can't work out the best way to link the data together so it can be viewed on one form. So once I access Client 1, I can see the projects they have with us, and then within that, all the staff they have working on that project.

I am not looking for the code, but suggestions for the best way to handle this in VB.Net.

I am using 2008 Express Edition.

Thanks

[hericles]

For your database I would have another linking table that links staff to projects (project_id, staff_id) so that staff members can be tracked by project ID. I know you have client ID, project ID and staff ID in the one table but two tables would be better (clients to projects, projects to staff).
For displaying the data a drop downlist of clients can be selected, after that a second dropdown is populated with their projects and once a project is selected then the staff members can be displayed. that would give you 3 queries against the database as you moved through the client-project-staff tables and it would prevent the user simply getting possibly irrelevant info dumped on screen (staff of a project they're not interested in).

[daveydrake]

Thanks mate. I appreciate it. A colleague received similar advice via another forum about the layout of the relationships. This has now been put into practice. Also, just personally tested your suggestion of the three queries and they produce a clear progression of information. Now to put this into practice. Thank you.