I need a little help deciding on a proper table design for some item code relations. I have a list of items that can be classified as either an Antigen, Blend or Family. Actually the Family codes aren't really item codes, but just the first 4 digits of the finished item codes, but I'm using Family codes instead of each finished item code because each finished item in a Family is the same product just packaged in different sizes (1dose, 5dose, 50dose, etc.). Each Family code is related to 1 to 3 Blend codes and 1 to 25 Antigen codes. What makes it tricky is that a Blend may be used in multiple Families. Same for Antigens. Several Antigens are in almost all Families. And an Antigen may be used in multiple Blends.

Here is an example of the data:
Family 1273 contains Blend 127300. Blend 127300 contains Antigen 127309.
Family 1295 contains Blend 127300 and 130900. Blend 127300 contains Antigen 127309. Blend 130900 contains Antigen 130907, 131009, 1310094K and 130907SUB.
Family 1299 contains Blend 127300, 130900 and 124504. Blend 127300 contains Antigen 127309. Blend 130900 contains Antigen 130907, 131009, 1310094K and 130907SUB. Blend 124504 contains 124509 and 124509J.

I need to be able to track it both directions. Say I pull up a Family, I need to see all Blends and Antigens. Or I pull up an Antigen, I need to see All Blends and Families.

What would be the best way to go about designing the table(s) and relationships?

Thanks!

Based on your description, you're going to probably want to use 2 separate junction tables since both blends and antigens are tied to a family but they are not hand in hand, one does not have anything to do with the other. You could have 15 blends but only 2 antigens for a given family. So, your table design could be something like this.

tblFamily - FamilyId, FamilyDescription
tblBlends - BlendID, BlendDescription
tblAntigens - AntigenID, AntigenDescription
tblFamBlends - FamBlendID, FamilyID, BlendID
tblAntiBlends - AntiBlendID, FamilyID, BlendID

To set this up as a form, you would just use tblFamily as your main form and create 2 sub-forms using tblFamBlends, and tblAntiBlends that would be in the main form and tied together by the FamilyID. You could also do the same concept with your reports where you would have 2 sub-reports.

Good luck with your project.

A Family can have many Blends, and a Blend can have many Families. Therefore it's a Many to Many relationship and you'll need a bridging or linking table to break up the M-M into two 1-M relationships.

Therfore you'll need a Family, FamilyBlend, and Blend tables.

The big question is whether an Antigen can be in more than one Blend. If not, you could have only 4 tables which includes the Antigen table. However if there's a M-M relationship between Antigen and Blend, then you'll need a 5th BlendAntigen table to break up the M-M relationship.

Thanks for the replies so far. Yes an Antigen can be in many different Blends.

I came up with a way to do it with one table before I checked for replies here. Could my one table idea work or should I go the multiple table route? Image of my one table idea attached.

Ok, let me make sure that I'm understanding this correctly. You have 3 separate areas your working with (Family, Blends, and Antigens). All the below holds true.

1. Each family can have both multiple blends and multiple antigens.
2. Blends and antigens are their own entities. If 3 blends are selected for a given family then this not mean that 3 antigens also need to be selected. None or an infinite number can be selected.
3. You need the ability to view all the blends or antigens for the different families.

If all this is correct then the design that I outlined in the first post will accomplish all of this. Doing it in one table would not be a good way of doing it. It would be confusing from a user standpoint first off where they could be cases where they have 35 blends they are entereing and only 2 antigens, so you're going to have one field with all these entries and the other one blank. Also for reporting, you would be displaying all this blank information. By doing it with separate tables, it's easier from a data entry standpoint, the reports are more condensed and concised, and you have more control over the data.

Ok, I just took a second look at your post. It's sort of a progression where your top level is Families>Blends>Antigens. Each family can have multiple blends and within each one of those blends in a family, they can have multiple antigens. So with that, the table design would be like below.

tbl_Family - FamilyID, Descirpiton
tbl_Blends - BlendID, Description
tbl_Antigens - AntigenID, Description
tbl_FamBlend - FamilyID, BlendID
tbl_BlendAnti - BlendID, AntigenID


So, your main form would be tblFamily. You would then create another form using "tbl_FamBlend" as your record source, you would then create another form using "tbl_BlendAnti" as the record source.

Now, you would set the form with "tbl_BlendAnti" as continuous or datasheet view and insert it as a sub-form into the "tbl_FamBlend" form and set the parent/child on the "BlendID" field. Next, you would add this form as a sub-form to the form that has "tbl_Family" and set the parent/child on the "FamilyID" field. Now, you can add as many blends to each family and also add multiple antigens for each one of the blends within that family. Hope that makes sense.

Multiple tables. The one table idea is easy to set up, but eventually you'll run into a nightmare of a headache with it and probably need to throw it all away and start over.

With the mulitple tables, you'll start out with the headache, but once properly created, will make your life easier in the future.

Thanks again Dan and Jerry. I appreciate the suggestions! I'm going to go with the multi-table setup you both suggested.

Glad we could help. Good luck with your project.