Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

Join table with multiple/many linked (parent) tables - update error

I have 2 tables with a many-to-many relationship (tbl_Item1 & tbl_Errors).  Because of the many-to-many relationship I have created a join/junction table called tbl_Item_Errors.  Using the Access relationship diagram I created the relationships between the 3 tables.  Everything is fine so far.

I have another table, tbl_Item2, which also has a many-to-many relationship with tbl_Errors.  I would like to use the same join table, tbl_Item_Errors, with tbl_Item2.

However, when I try to update the join table with errors relating to tbl_Item2 I get a message saying that a ‘matching record’ for tbl_Item1 must exist in order to write to the table.  I thought it might be an issue with the compound key on the join table, so I got rid of that and just made the key field on the join table an Access auto number field.  However, I still get the same error.

Can someone identify what is causing the error and a possible work around?  I would like to add another tbl_Item3 to match up with tbl_Errors in the same fashion.  I don’t want a join table for each tbl_Item table because I would like all of my errors in one table (tbl_Item_Errors).

Thanks,
Pete
0
PeterWink
Asked:
PeterWink
  • 2
1 Solution
 
harfangCommented:
Hello PeterWink

What you need seems at first illogical. As you stated yourself, you needed the table tbl_Item_Errors to materialize the many-to-many relationship between the first two tables. If you establish a new many-to-many relationship, you will quite naturally need a new link table. Hence, the only way to create the new relationship to tbl_Item2 is to create a link table, probably called tbl_Item2_Errors.

Think about it in another way: there is a one-to-many relationship between tbl_Item1 and tbl_Item_Errors. This means that for each record in the second table, you need a matching key in the first. When you try to add errors from another table to the link table, this is no longer true, and you get the quite logical error message. Although you can potentially use the same table for several relationships (this has severe limitations), you cannot use the same field to refer to different keys.

If you feel upset about this, it's probably because you perceive that tbl_Item1, tbl_Item2 and tbl_Item3 are somehow connected and should share not only the same errors table, but also the same link table. Formally, there are two solutions:

A) All things are items.

Let's say you have things, gizmos and stuff, but these are all items. You can then have one table called tbl_Items, with an ItemID. You can then create three related tables (one-to-one relationships) where you will find the additional fields needed to describe each type of items: tbl_Things, tbl_Gizmos, and tbl_Stuff. That way, each Thing is also an Item. The many-to-many link between Items and Errors can then work for each item type.

In modelling language, you would say that "a Thing is a kind of Item", "a Gizmo is a kind of Item", etc.

B) Errors can occur for Packs.

Imagine that Item1 are computers, Item2 are docking stations, and Item3 are wireless connections. All can have errors, and are thus collectively related to the errors table. What's more, errors occur for specific combinations of these three item types. You would then create tbl_Items_Errors with four fields: Item1ID (or computerID), Item2ID (or dockingID), Item3ID (or connectionID), and ErrorID (required), materializing the four one-to-many relationships to the four tables. You would then either add an autonumber or three compound keys (depending if the same error can occur more than once for each item).

You will notice that this creates secondary many-to-many relationships between the various Items tables, showing which combined for an error, but with limitations.

C) Item1, Item2, and Item3 are totally distinct objects

In that case, go with three link tables. Why should this be a problem?


Hope this helps

(°v°)
0
 
PeterWinkAuthor Commented:
Thanks for the thorough response.  It seems I was trying to break a basic fundamental of database design.  However, I thought that by making the key field on the join table an AutoNumber field I would be able to work around any constraints of compound keys.  (Access allows you to bend the rules in some cases) However, it seems that creating a relationship in the relationship diagram creates an inherent requirement similar to that of any normal junction table compound key???

At this point the ‘Item’ tables are already in place, so it seems the proper way to do this is to have separate join tables (Option C).  The items tables (utility equipment) are quite unique; however the errors (rust, lock broken, date inspected) are all similar.  Therefore, it just seemed odd to me to have 3 separate tables (the join tables) in the database with the exact same design.  Three identical tables would seem to defy rules of normalization.

In hindsight it may have been better to have a parent items table and design the database accordingly (Option A).  Unfortunately many of the requirements for this project are being defined as the project evolves which makes it difficult to maintain fundamental principles of design.  If someone new has to support the application in the future I am sure they will be scratching their heads & wondering . . .

Thanks again for the response, I really appreciate it.
Pete
0
 
harfangCommented:
Pete,

Thanks for the grade and for the feedback.

The basic building blocks of a relational database are tables and relationships. As such, the many-to-many relationship isn't: it has to be created with another table. A relationship has a "primary key" side (which is the one side in a one-to-many relationship) and a "foreign key". I believe the fundamental rule you were trying to bend was that you wanted a "foreign key" to have several "primarky keys" in different tables. I.e. the ItemID in tbl_Item_Errors should relate to ItemID in one of three primary tables. That is not possible.

So, yes, a perfectly normalized model would probably go with "X is a kind of Y" rules, implemented as one-to-one relationships (option A). This being said, event if it would be the "best" representation structurally, it is not necessarily the most efficient.

Anyway, good luck with your project!
(°v°)
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now