Linking multiple tables to one???

[jtanis]

I have 3 tables: Findings, Inspections, Complaints.
I want to have them to work as follows:
I want both of the Inspections and Compaints table to store their findings
in only one common table, Findings.

How do I set up the relationships and primary / foreign keys?

Of course it can be like this:

inspections Findings
1 00

complaints Findings
1 00

But this solution requires one field for each foreign key, in the findings
table. But I have around 7 more tables that need to store their findings.
Is there a way to cut out all these fields? (further more, since each field
will have only one of these 9 fields filled, it will result in data
denormalization)

 

[KARL DEWEY]

I would add a field to identify which type the records were then append into
one table.

 

[Pat Hartman\(MVP\)]

Should Inspections and Complaints be a single table instead of two tables?
Are the columns very different?

If it turns out that Inspections and Complaints are sufficiently different
to justify individual tables then you need to add a fourth table to your
schema. This fourth table would be the parent table of Inspections and
Complaints. It would have an autonumber primary key and include any common
fields. It would also include a field that identified an individual record
as an Inspection or a Complaint. The Inspection/Complaint tables would have
long integer primary keys and be related 1-1 with the new parent table. The
Findings table would be related to the parent table rather than the
Inspection/Complaint tables so you can enforce referential integrity.

 

[jtanis]

Yes, that is what I had in mind, but that will allow an incident (the forth
table) to be entered as both an inspection and a complaint, won't it??

 

[Pat Hartman\(MVP\)]

No, it is either an incident OR a complaint.

Yes, that is what I had in mind, but that will allow an incident (the
forth table) to be entered as both an inspection and a complaint, won't
it??