Lots of tables to put together

[cstanley]

Hello all,

I have a problem - I have about thirty or so different tables which contain data that needs to be associated with data from one other table. I don't want to have thirty different join tables. What can I do?

Chris

 

[cstanley]

Each of the thirty tables contains a list of requirements from a different document. The one table contains a list of numbers which are assigned to systems which have those requirements associated with them. It's possible to have any given requirement to be associated with more than one system.

It would be possible to add another field to the requirements identifying them and combine the thrity tables into one, and then make a single join table. I was wondering if there was some more elegant solution in the offing....

Thanks!

Chris

 

[The_Doc_Man]

The way I would approach this is, you have your tables that list your possible requirements. Merge them all into a single requirements list table. Include the original document number in the list so you can reference the correct document when you are pulling this up.

Then build another table that will stand between your master systems documents and your master requirements lists.

The only two fields in this table will be the number from the master systems list and a number from the master requirements list. This table will have one entry for each requirement in each system. So if you have an entry like, 1026, 59 then..

system # 1026 has requirement 59.

Now, if you want to list requirements for a single system, you run the select query on this linking table for a single system number and find all the requirements. If you want to find systems having the requirement, run the select query on a single requirement number and find all the systems.

Your relationships between this linking table and the requirements tables and systems table will be

one (system) to many (links) and
one (requirement) to many (links)