Combining two tables of multiple data

[SueBK]

I have two sets of data - a list of roads and a list of issues. For each road, each of the issues must be addressed.

I have previously designed a database that had a table for each of the data sets, and used an update query (from memory) to create a third table that combined the two - creating a record for each issue for each road (ie if I had 10 roads and 10 issues, the third table ended up with 100 records).

I have a new projects, with some different parameters to the original, so a completely new database is required.

Initially this project only required a yes/no response, so I used the roads table with a field for each issue. It looks like they'll be wanting a comments field after all, so I'm tempted to reinvent my previous design.

I'm just wondering, if the 'two merged tables' design I used last time is the best way to approach this issue.

 

[jdraw]

You seem to have the traditional Many to many situation.

One Road has many Issues
One Issue could apply to Many Roads.

The common approach to resolving (handling) Many to Many is to create a junction table.

Roads ------>RoadhasIssue<---------Issues

for more info see these free videos

http://www.youtube.com/watch?v=7XstSSyG8fw
https://www.youtube.com/watch?v=JBG4SzNhA9A

Also note: The PK of the junction table can be:

-the combined keys of the original tables (as per the above videos)

or
- a separate autonumber field (then have a unique composite index composed of the keys from the original tables to prevent duplicates). [as shown in the video below]

http://www.youtube.com/watch?v=nszRT3nRUMU&feature=related

Good luck with your project.