If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"Circular" Relationship
I am currently restructuring the database at my company to allow for more
efficient use. I have the restructuring almost completely done, but I am stuck on one design issue. Here are the basics of what I have: We have fabricated parts, which are linked in a many to many relationship in a junction table to the weld assemblies that they are a part of. This is because many fabricated parts can be used in many different weld assemblies. It was recently brought to my attention that there are weld assemblies that are used in other weld assemblies. I tried to create a junction table with two primary keys that relate back to the same weld assembly table, however, I cannot enforce referential integrity with this relationship. Has anyone solved a problem similar to this before? I have seen several examples online of employees who manage other employees, but I am not sure how to implement this kind of relationship into an Access environment. |
#2
|
|||
|
|||
"Circular" Relationship
We do that. The linked fields in junction tables should be FK's, not primary keys. This is one case where it is particularly useful to recognize that a junction table is more than an administrative tool to do a M to M relationship. Each record in it is a record of a relationship between two records(in your case, between two parts) . For BOM's, it's typically a record of the fact that that "A" is used to make "B" You'll put all of your parts into one parts table (presumably PK = Part Number) , and link "field A" and "field B" in your junction table to the part number field in your parts table. . This isn't your classic "circular" relationship where a table is linked to itself, but I guess you could this two-table loop circular. |
#3
|
|||
|
|||
"Circular" Relationship
I think you need what is known as a 'Self-join'. An example would be an
employee list that included a Foreign Key field for Supervisor. Supervisors would themselves be employees and be in a one-to-many relationship with employees. In the Relationship window add the table twice. Access adds a sufix of '_1' to the table name of the second object. Click on the Primay Key field of first table and drag to the Foreign Key field of the second table. Select Referential Integerity and Cascade Updates. "PeteyPueblo" wrote: I am currently restructuring the database at my company to allow for more efficient use. I have the restructuring almost completely done, but I am stuck on one design issue. Here are the basics of what I have: We have fabricated parts, which are linked in a many to many relationship in a junction table to the weld assemblies that they are a part of. This is because many fabricated parts can be used in many different weld assemblies. It was recently brought to my attention that there are weld assemblies that are used in other weld assemblies. I tried to create a junction table with two primary keys that relate back to the same weld assembly table, however, I cannot enforce referential integrity with this relationship. Has anyone solved a problem similar to this before? I have seen several examples online of employees who manage other employees, but I am not sure how to implement this kind of relationship into an Access environment. |
#4
|
|||
|
|||
"Circular" Relationship
I think that this is a many-to-many relationship. One part can be used to
make many different parts, and one part can be made up of several different parts. So, in my opinion, Karl's good & detailed advice applies, but an with the addition of an intervening junction table. |
Thread Tools | |
Display Modes | |
|
|