Relate one field to multiple fields (1 Viewer)

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:29
Joined
Feb 19, 2002
Messages
42,971
tblParts is the part master. It must include EVERY detail part. Part # 141 is missing from tblParts and that is what is causing the issue. Once you add that part to tblParts, you will be able to enforce RI. Not every part in tblParts will exist in tblAssemblyItems BUT every part in tblAssemblyItems WILL exist in tblParts - this is backwards from what you said. tblParts is the "parent" and tblAssemblyItems is the "child" in this relationship. The "parent" is the table where the related field is the primary key. The "child" is the table where the related field is a non-key field or one of the fields in a composite key PK. When tbl1.PK points to tbl2.PK, the relatioship is 1-1. This is an extremely unusual relationship. Normally, you would simply use a single table. There are a couple of reasons to use two tables in a 1-1 relationship.
1. Security. Some if the data is extra sensitive and you don't want to give permissions to that table to everyone. Depending on the RDBMS, you might be able to accomplish this by field level security bur not all RDBMS (such as Jet/ACE) support this feature.
2. Sparseness. Sometimes some of the data is rarely present and so purists will put the sparse fields in a second table.
3. Rare use. Sometimes there are large data items that are rarely referenced. It is more efficient to simply separate them so that the bulk of the table processing takes place against a much smaller physical data set.
4. Grouping of entities. When you have disparate entities such as students, faculty, vendors, guardians that have many common uses, it makes sense to define them in an entity table but keep their separate data types in individual tables. That allows you to use one address table and one contact info table for example and relate it to the entity "parent" rather than to each different "child".
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:29
Joined
Feb 19, 2002
Messages
42,971
I just noticed that tblAssemblyItems is not correctly defined. There MUST be a unique primary key. This is a combosite key and will be Assembly_FK + Part_FK. To do this, open the table in design view and using shift-click, select both fields. Then press the key button. As it stands, you will get an error because there are duplicates so you will need to fix the duplicates first.

Sorry, I should have clarified the PK when I made the suggestion.
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:29
Joined
Jan 23, 2006
Messages
15,364
Part 141 has been fixed in latest database.
There are 4 duplicates in tblAssemblyItems that have to be removed.

Code:
Assembly_FK Field	Part_FK Field	NumberOfDups
31	                             4	                 2
32	                             8                  2
46	                            87                 2
47	                            88                 2
 

Zydeceltico

Registered User.
Local time
Today, 13:29
Joined
Dec 5, 2017
Messages
843
I just noticed that tblAssemblyItems is not correctly defined. There MUST be a unique primary key. This is a combosite key and will be Assembly_FK + Part_FK. To do this, open the table in design view and using shift-click, select both fields. Then press the key button. As it stands, you will get an error because there are duplicates so you will need to fix the duplicates first.

Sorry, I should have clarified the PK when I made the suggestion.

Thanks Pat. I know I have said in the past that any assembly will always only be two parts, but as I am creating a unique composite key across two fields I immediately wonder how I will handle the day when we do in fact have an assembly that has three parts and my unique composite key is only across two fields.
 

Zydeceltico

Registered User.
Local time
Today, 13:29
Joined
Dec 5, 2017
Messages
843
I see 62 EndItems but only 61 Assemblies which I don't quite understand.
It's possible that EndItem (Assembly_PK 62) is planned, but doesn't yet exist.
My thinking is create an EndItem and then assign Parts ---completing the EndItems as you go so numbers are aligned (all enditems are assemblyItems).


My first thought was to copy the AssemblyItems to XXX, then delete all records from AssemblyItems, then restructure AssemblyItems to have composite PK(Assembly_PK + Part_PK). Then set up referential integrity, then repopulate AssemblyItems from XXX...
the idea is to let the database do the referential integrity checking.

Just some thoughts for consideration.

Cleaned it up. Thank you Jdraw.

After giving my "new parts and assemblies" question some more consideration, I realized I'll just do it the way it would be done in real life in the engineering department. I'll make two new unique parts records (or some new combination of parts) then I'll create a record for my new assembly. Then I'll go to tblAssemblyItems and make a new entry that combines the two. A simple logic but I think that it works.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:29
Joined
Feb 19, 2002
Messages
42,971
When an assembly has more than one part, there will be additional rows. One for each pair-
AssemblyID1 - part1
AssemblyID1 - part2
AssemblyID1 - part3
AssemblyID1 - part4
AssemblyID2 - part1
AssemblyID2 - part2
AssemblyID3 - part1

So, most assemblies in your case will have two rows in this table. One to connect each of the parts to the assembly.

The AssemblyID is NOT the name of a detail part. It is the name of the assembly. I think that is causing the confusion. You are thinking of these fields as part1-part2 but that is not what they are.

If you sell a detail part, it still gets an AssemblyID (which depending on your naming standards, may or may not be the same as a part #. I would not make them the same since it just causes confusion). So, think of the AssemblyID as the name of the part you are selling rather than the name of the part you are making.
 

Users who are viewing this thread

Top Bottom