Multiple many to many relationships within a heirarchical selection process
I have run into an interesting problem in having to develop a group of tables with an odd relationship set. I'll give you an idea of what it is:
- I have 4 tables (3 contain classifications and the 4th contains the record's information)
- the information for the end-user is taken from the 4th table based on selections made from the 3 tables
- assume that each of the three tables contains a many-to-many relationship with the other two, i.e. a member of the third class can be a member of multiple second and first classes
- the ideal selection process will go:
select first(if second choices exist then select second, else display results from 4);
select second(if third choices exist then select third, else display results from 4);
select third(dislay results from 4)
I am currently trying to implement this with 3 tables containing a class name and id then 2 linking tables between 1 and 2, and 2 and 3 referencing the class id's to denote the many to many relationships. What I'm not sure on is how many columns to use to categorize the records in the 4th table, or which to refrence them to, because they should be able to be accessed from any one of the three steps. If in fact the best idea is to have a foreign key in the 4th column for each of the other 3 tables then how would a many to many relationship actually be implemented? Also, it seems to me like there might need to be another linking table between 1 and 3 in case something from the third class resides under multiple first classes but only a single second class.
If you have any thoughts on how you might implement this I would greatly appreciate it because quite frankly this is blowing my mind.
Subject
Written By
Posted
Multiple many to many relationships within a heirarchical selection process
March 24, 2005 07:41PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.