Help setting up tables and relationships based on XML

[JMW]

I've mentioned this before.  My background is in programming in a CAD/manufacturing environment.  So, while I am familiar with coding and have been learning to write and develop Filemaker scripts for several years database setup and relationships are a weakness.  That said, I am creating a system to submit lens orders for eyeglasses to a lab.  The lens catalog is sent to each doctors office from the lab in xml format.  Each catalog can have different content but the XML  format is the same.  I know how to write the xslt to import the catalog.  What I'm struggling with is setting up the tables in a file.  I created a file named after the vendor and in it I have created several tables to match the structure of the xml as much as I can.  What I am struggling with is setting up a relationship for lens treatments and exclusions based on treatment chosen.  Here is a small sample of the xml from a catalog.

    <TREATMENT Description="Crizal Alize UV" VwCode="AR-ALIZEUV">
      <INCOMPATIBLE Description="Standard A/R" VwCode="AR-AR" />
      <INCOMPATIBLE Description="Clearsight Claris" VwCode="AR-CLARIS" />
	</TREATMENT>
	<TREATMENT Description="Crizal Avance UV" VwCode="AR-CRZAVCUV">
      <INCOMPATIBLE Description="Crizal Alize UV" VwCode="AR-ALIZEUV" />
      <INCOMPATIBLE Description="Standard A/R" VwCode="AR-AR" />
	</TREATMENT>
    <TREATMENT Description="Standard A/R" VwCode="AR-AR">
      <INCOMPATIBLE Description="Crizal Alize UV" VwCode="AR-ALIZEUV" />
      <INCOMPATIBLE Description="Clearsight Claris" VwCode="AR-CLARIS" />
      <INCOMPATIBLE Description="Clearsight AR" VwCode="AR-CLEAR" />
	</TREATMENT>
    <TREATMENT Description="Clearsight Claris" VwCode="AR-CLARIS">
      <INCOMPATIBLE Description="Crizal Alize UV" VwCode="AR-ALIZEUV" />
      <INCOMPATIBLE Description="Standard A/R" VwCode="AR-AR" />
      <INCOMPATIBLE Description="Clearsight AR" VwCode="AR-CLEAR" />
      <INCOMPATIBLE Description="Crizal Avance UV" VwCode="AR-CRZAVCUV" />
	</TREATMENT>
    <TREATMENT Description="ESColor Emerald Blue" VwCode="TINT-EMERALDBLUE" />
    <TREATMENT Description="ESColor EmrldBlu Grd" VwCode="TINT-EMERALDBLUEGRAD" />	

I want to set up a table and/or relationship between treatments and incompatibilities so that when a user chooses a treatment for a lens the incompatible options do not display.  But an incompatible option is also a treatment.

I am contemplating two tables one for treatments and one for incompatibilities connected by a join table since it is a many to many relationship but that requires duplicate data and I'm trying not to duplicate data.  Is there another way to do this?  If so, what might it be?

          Treatment                             JoinTable                                                           Incompatible

          Description       +---------  TreatmentFK                                                         VwCode
          VwCode            |                 IncompatFK  ---------------------------------- IncompatId
          TreatmentId --+

Hopefully, this makes sense.

Thanks in advance for all your help.  This forum has been a life saver over the years.  I have learned a lot from it.

[comment]

AFAICT, your XML snippet has the structure of a parent/child relationship - but some of the child INCOMPATIBLE elements are repeated. This suggests that the real-life relationship between Treatments and Incompatibles is actually many-to-many. If so, the structure you are contemplating would be the correct one.

I am not sure what "duplicate data" you are referring to; the very purpose of having a join table is to eliminate the duplication of data. However, you must make sure that the Incompatibles table imports only unique records - either by field validation or by constructing the XSLT stylesheet accordingly.

 

[JMW]

Thanks for verifying my setup as correct.  I am still pretty new at setting up tables and relationships from scratch.

What I meant by duplicate data is that a treatment is also an incompatible treatment so while there is data in the treatment data, that same data will also be in the incompatible table.  Thus, duplicate data(?)  Hopefully, that makes sense.

And, thanks for pointing out that I should only import unique records!  Without thinking it through, I'd have imported all the data.

[comment]

2 hours ago, JMW said:

What I meant by duplicate data is that a treatment is also an incompatible treatment

Oh, now I see. I would have never figured this out from the XML sample. In such case, you only need two tables: Treatments and Incompatibilities, where Incompatibilities is a join table between two occurrences of Treatments - IOW a many-to-many self-join of Treatments.

I should also mention that Filemaker allows you to implement a many-to-many relationship without a join table. In this case, you could use a checkbox field in the Treatments table for storing the incompatible treatments. However, you need to consider carefully if you want to go that route, because it puts severe limitations on your reporting ability.

 

Edited September 24, 2021 by comment

[comment]

An afterthought:

You will still have some duplicate information with this arrangement, because presumably an incompatibility is symmetrical: If A is incompatible with B, then also B is incompatible with A - but the join table will have two entries to express this. There is a way to eliminate this duplication, but if you are only importing the data, I am not sure you need to bother. The problem is more severe when users are entering this information; in such case you want to make sure a single entry establishes the relationship in both directions. 

 

[JMW]

Comment,

Thanks for the update.  As for the XML, it took me awhile to figure it out.  The catalog's XML leaves much to be desired.

So, I am going to repeat back what I got from your post to make sure I understand it.  I will need to duplicate the Treatment table and create a join table named Incompatibilities.  This will create a self join for treatments and set up the relationship between treatments and incompatible treatments.  I am assuming setting up treatments and incompatibilities this way will allow me to display only the valid treatments and leave out incompatible treatments.

I have created many to many relationships before between existing tables without a join table.  But, I am designing this "module" from scratch and am trying to create a proper database structure with no duplicate data.  It seems join tables are the way to do this.

There are a lot of treatments and a lot of incompatible treatments so I'm not sure how a well checkbox would work.  One treatment I looked at has something like 67 incompatible treatments.  (Although, maybe I don't understand what you mean by checkbox.)  So, I am thinking the join table will be the best solution.

I also think it would be best based on your comment about reporting.  The data in the catalog is going to be presented to users for creating lens and eyeglass orders that can be submitted to a lab.  So, displaying the data in the catalog accurately is important.  I am guessing that is also considered reporting.

I do not know if users will add data.  That is something I'll find out later.  Hopefully, not often if they do.

Thanks for all your help!

 

Edited September 28, 2021 by JMW

[comment]

4 minutes ago, JMW said:

I will need to duplicate the Treatment table

Not exactly. You only need one Treatments table. This table needs to have two occurrences on the relationships graph, so that you can join them using the join table.

11 minutes ago, JMW said:

I am assuming setting up treatments and incompatibilities this way will allow me to display only the valid treatments and leave out incompatible treatments.

Yes, but it will require a little more work. You will need a calculation field in the Treatments table that lists the codes of all incompatible treatments. This will be the match field to yet another occurrence of the Treatments table using ≠ as the relational operator.

 

[JMW]

On 9/28/2021 at 8:48 AM, comment said:

You will need a calculation field in the Treatments table that lists the codes of all incompatible treatments. This will be the match field to yet another occurrence of the Treatments table using ≠ as the relational operator.

I am sorry but I don't understand what you're trying to tell me.  Does "calculation field in the treatment table"
mean the field contains an actual list?  I'm guessing it's not the case but I'm not understanding what you mean
by list.

I included a picture of the portion of my relationship graph we are talking about.  I am also guessing that I will
need to modify the VWIncompat 2 (it is a 2nd occurrence of VWTreatment) to have Incomp Id linked to Incompat FK and
then set to not equal.

Thanks for all your patience and help.  I truly appreciate all the help I've been given over the years.

[JMW]

On 9/28/2021 at 8:48 AM, comment said:

You will need a calculation field in the Treatments table that lists the codes of all incompatible treatments. This will be the match field to yet another occurrence of the Treatments table using ≠ as the relational operator.

I am slowly beginning to understand what you are trying to say above.  I will need a calculation field in the Treatments table (which I created an named Incompat Id) with the List() function in the calculation.  I am still not sure which field it will contain or how to display the list on a layout.

Thanks for all your patience and help while we learn.

[comment]

48 minutes ago, JMW said:

Does "calculation field in the treatment table"
mean the field contains an actual list?

It means a field whose type is Calculation, using a formula like this:

List ( VWIncompat::Treat Id )

and the result type is Text. Then you will use this field as the match field in a relationship defined as:

VWTreatment::cIncompatIds ≠ VWCompat::Treat Id

where cIncompatIds is the calculation field and VWCompat is the 3rd occurrence of the Treatments table 

 

(I am somewhat confused by your having both a Treat Code and a Treat Id field. I thought you were importing the data from the XML which has only the codes.)
 

Now, at the risk of confusing you even further, I need to say this: in this arrangement, the relationship between treatments and compatible treatments will be many-to-many without a join table. That means you will face the same limitations on reporting as mentioned earlier WRT incompatible treatments. Technically, you should have a giant join table listing all possible treatment pairs, with a field denoting compatibility as true or false. However, this would be both impractical and redundant. The detailed reporting, if necessary, could be produced using a technique known as "Virtual List".

Why am I mentioning this? Because if you're going to rely on virtual list for reporting on compatible treatments, you might just as well use it for reporting on incompatible treatments too. Which brings me back to my earlier suggestion to implement the relationship to incompatible treatments without a join table too.

 

[JMW]

1 hour ago, comment said:

(I am somewhat confused by your having both a Treat Code and a Treat Id field. I thought you were importing the data from the XML which has only the codes.)

I will take some time and absorb what you said in your post but to help clarify your confusion ...

Treat Id is a number (functional key) and used to map a record to the JoinTreatIncomp table.
Where as Treat Code is a string that is used in the catalog along with a description of the treatment.  The xml I
will create to submit a lens/eyeglass order requires both their Treat Code and Treat Description.

Here is an example of the data in Treatments

Treat Id   Treat Code        Treat Description      Incompat Id
1          AR-ALIZE          Crizal Alize           is the calculation field for the incompatible list
2          AR-ALIZEUV        Crizal Alize UV
3          AR-AR             Standard A/R
4          AR-BLUELIGHTAR    Empire Blue Light AR
5          AR-BSIDE          Back Side A/R

JoinTreatIncomp

So my join table contains numbers referencing a treatment and it's corresponding incompatible treatment
Based on the above data and example is:

TreatmentFK  IncompatFK
1            3
1            5
2            1
2            4
3            5

Being an intermediate FM scripter and a new FM database "designer" I set up the table and fields based on my
current understanding of how this should work.  If it's wrong, I can change it.

[comment]

I am not saying it's wrong. I am just puzzled how do you populate the FK values in the join table if you import into in. This goes back to the question whether users will be adding data. If import is the only source, and their codes are unique, I would not bother with adding my own keys.

[JMW]

I will have to find out how the catalog is going to be used.  It is a completely new module for us.  Our current implementation uses a subset of the catalog in an old file containing lots of different data and new lenses are added by each office by hand.  (Each customer has it's own copy of our FM solution.)  So ... as soon as I say users won't update the catalog ... I know they'll just want to enter the few new lenses by hand instead of updating the whole catalog.  But, I could allow that using the many to many relationship too.

You mentioned reporting.  Do you mean creating FM reports or listing and selecting data that is displayed?  Sorry. Just trying to understand exactly what you're saying.

[comment]

28 minutes ago, JMW said:

You mentioned reporting.  Do you mean creating FM reports or listing and selecting data that is displayed?  

I mean that without a join table you cannot produce a report like this:

Treatment A
• Incompatible: Treatment B
• Incompatible: Treatment D
• Incompatible: Treatment E
Treatment B
• Incompatible: Treatment A
• Incompatible: Treatment C
Treatment C
• Incompatible: Treatment B
Treatment D
• Incompatible: Treatment A
Treatment E
• Incompatible: Treatment A

from the data you have. You need to have 3 records of Treatment A in order to produce the 3 rows that show this value. 

You can only show the incompatibles for each individual treatment, one treatment at a time.

 

[JMW]

Thanks for the explanation.

Using the following example, what I need to do is eliminate incompatible options from a list of treatments so that
only the possible treatments are available for selection.

Crizal Alize UV
    Incompatible: Standard A/R
    Incompatible: Clearsight Claris
    Incompatible: Crizal Avance UV
Crizal Avance UV
    Incompatible: Crizal Alize UV
    Incompatible: Standard A/R
    Incompatible: Clearsight Claris
Standard A/R
    Incompatible: Crizal Alize UV
    Incompatible: Clearsight Claris
    Incompatible: Clearsight Claris
Clearsight Claris
    Incompatible: Crizal Alize UV
    Incompatible: Standard A/R
    Incompatible: Clrizal Avance UV
ESColor Emerald Blue
ESColor Emerald Blue Gradient
Polish Edge
    Incompatible: Edge roll and polish
Edge Roll and Polish
    Incompatible: Polish Edge

If I choose Crizal UV the list would be narrowed to:
EsColor Emerald Blue
EsColor Emerald Blue Gradient
Polish Edge
Edge Roll and Polish

And if I choose Polish Edge the list would be narrowed to:
EsColor Emeral Blue
EsColor Emeral Blue Gradient

The treatments chosen for the lens are Crizal UV and Polish Edge.  I need to save these in the lense order and export the choices with the lens order in XML (different than the catalog shown above)

Each choice reduces the list of possible choices.

I am assuming I'll have to figure out how to use the JoinTable.  

Or am I going about this wrong way?  I am basing the design off of the catalog xml but it could be totally off base.

Edited October 8, 2021 by JMW

[comment]

So far you had only one entity - Treatments. Now it seems you also need to have some kind of a list of lenses (an Order?) and each lens on this list has its own list of selected treatments. And the list of selected treatments (for an individual lens) should not contain mutually incompatible treatments. Is that a correct understanding? 

[JMW]

Yes, that is a correct understanding.  What I am asking about is a small part of a larger design.
The relationship and XML I asked about is a subset of a lens catalog that will need to be imported.
It is an XML file that contains, lens type (bi-focal, etc.), material (glass, plastic, etc.), lenses, and addons.
Addons consist of a list treatments that can be used on a lens and the corresponding list of incompatible treatments. 

I was asked to create the XML to submit an order to a lens lab.
That requires that I create the XML for a SOAP request and the XML for a lens order.

I soon realized that to create the XML I would have to redesign how lenses are dealt with in our current system.
It works fine for orders submitted on paper but not so well with orders created and submitted electronically.
If the redesign is done well, it should working with multiple vendors electronic order systems.

Below is my database redesign for lenses.

It appears that one problem I will have to solve is how to populate the join tables after importing data.

[comment]

On 10/9/2021 at 12:16 AM, JMW said:

Each choice reduces the list of possible choices.

The answer depends on how you intend to make a choice.

If you want to use a value list (e.g. select from a drop-down menu) you will need to construct a so-called "dwindling" value list (look it up, there are many examples here such as this one). In this case, you would need to have an unstored calculation field in the Lenses table listing the IDs of all incompatible treatments related to the current lens via the already selected treatments. Then use this as the match field to an occurrence of the Treatments table using the ≠ relational operator. Then define the value list to show only related values from this occurrence.

Alternatively you could select from a card window showing a list of compatible treatments. This would be accomplished by using Go to Related Record to create a found set of all incompatible treatments related to the current lens via the already selected treatments, then showing omitted records. This has the advantage of not requiring any additions to your schema beyond the relationship of treatments to incompatible treatments as given by the original XML.

 

Edited October 10, 2021 by comment

[JMW]

On 10/5/2021 at 4:27 PM, comment said:

I am not saying it's wrong. I am just puzzled how do you populate the FK values in the join table if you import into in. This goes back to the question whether users will be adding data. If import is the only source, and their codes are unique, I would not bother with adding my own keys.

Ok. I am back to working on the setup for this catalog. I found out two things since we talked. Users should not be adding lenses, etc. to the database. All the input should come from importing. And, there are multiple catalogs to be imported. There will be regular updates. You mentioned "not bother adding my own keys." So ... would should there just be many to many relationships between tables? Sorry ... just trying to learn how to do database design correctly?

[comment]

6 hours ago, JMW said:

would should there just be many to many relationships between tables?

I am afraid I still don't have a clear picture of what you have and what you want do with it. So far you've only shown us a list of treatments with a sub-list of incompatible treatments (drawn from the same list). That is a many-to-many relationship between the treatments table and itself - and as I said, I am not convinced that you do need a join table to resolve it.

Regarding other relationships, we need to have more details.

 

Edited November 10, 2021 by comment

[JMW]

On 11/9/2021 at 10:27 PM, comment said:

Regarding other relationships, we need to have more details.

If you scroll up in the "conversation" in my post on October 9th there is a picture of the current setup of the tables and relationships.

I am trying to create an interface between our software and an optical lab. Users of our software will create an order and send it to a lab to order lenses and/or have lenses cut and put in frames. To create the interface I need to redo the way lenses are setup and stored in the system.

We receive the lens data in multiple XML files that will need to be imported into our system and made available for use. The high level xml elements correspond to generic lens categories. There are lens types, designs (of the lens itself), lenses available in that design, add-ons or treatments for a lens, then a corresponding list of incompatible treatments for given treatments and finally lens materials.

Below is a simple example of the data we receive (and attached as pictures is the XML with the options chosen highlighted in the example):

  <LENS_TYPES>
    <LENS_TYPE Description="BiFocal" Code="BFF" />
    <LENS_TYPE Description="Progressive" Code="PAL" />
  </LENS_TYPES>
  <DESIGNS>
    <DESIGN ParameterID="0" Description="SOLA Access" VwCode="ACCESS">
      <LENSTYPE Code="PAL" />
    </DESIGN>
    <DESIGN ParameterID="0" Description="SOLA Access 125" VwCode="ACCESS125">
      <LENSTYPE Code="PAL" />
    </DESIGN>
    <DESIGN ParameterID="0" Description="Executive Bifocal" VwCode="EX">
      <LENSTYPE Code="BFF" />
    </DESIGN>
    <DESIGN ParameterID="0" Description="Flat Top 25" VwCode="FT25">
      <LENSTYPE Code="BFF" />
    </DESIGN>
    <DESIGN ParameterID="0" Description="Flat Top 28" VwCode="FT28">
      <LENSTYPE Code="BFF" />
    </DESIGN>
  </DESIGNS>
  <LENSES>
    <LENS ADD_ID="0140AD0016" Description="ACCESS-50-NONE-NONE-00" DesignCode="ACCESS" MaterialCode="PL-50-NONE-NONE-00" />
    <LENS ADD_ID="0140AD0016" Description="ACCESS-50-NONE-NONE-16" DesignCode="ACCESS" MaterialCode="PL-50-NONE-NONE-16" />
    <LENS ADD_ID="0140AD0016" Description="ACCESS125-50-NONE-NONE-00" DesignCode="ACCESS125" MaterialCode="PL-50-NONE-NONE-00" />
    <LENS ADD_ID="0140AD0016" Description="ACCESS125-50-NONE-NONE-16" DesignCode="ACCESS125" MaterialCode="PL-50-NONE-NONE-16" />
    <LENS ADD_ID="0140AD0014" Description="FT25-G7-NONE-NONE-00" DesignCode="FT25" MaterialCode="GH-70-NONE-NONE-00" />
    <LENS ADD_ID="0140AD0014" Description="FT25-G5-NONE-NONE-00" DesignCode="FT25" MaterialCode="GL-50-NONE-NONE-00" />
</LENSES>
 <ADDED_VALUE Description="AD0016" ADD_ID="0140AD0016">
    <TREATMENT Code="AR-AR" Description="Standard AR" Type="Selectable" />
    <TREATMENT Code="AR-BSIDE" Description="Back Side AR" Type="Selectable" />
    <TREATMENT Code="AR-ENDURA" Description="RF Endura" Type="Selectable" />
    <TREATMENT Code="AR-KAR" Description="Kodak CleAR" Type="Selectable" />
    <TREATMENT Code="AR-KCNC" Description="Kodak Clean N CleAR" Type="Selectable" />
    <TREATMENT Code="EDGE-EP" Description="Polish Edge" Type="Selectable" />
    <TREATMENT Code="EDGE-ER" Description="Roll Edge" Type="Selectable" />
    <TREATMENT Code="SR-SRC" Description="Scratch Coat" Type="Selectable" />
    <TREATMENT Code="UV-UV" Description="Lab UV Coat" Type="Selectable" />
    </ADDED_VALUE>
</ADDEDVALUES>
<TREATMENTS>
    <TREATMENT Description="Standard AR" VwCode="AR-AR">
      <INCOMPATIBLE Description="RF Endura" VwCode="AR-ENDURA" />
      <INCOMPATIBLE Description="Kodak CleAR" VwCode="AR-KAR" />
      <INCOMPATIBLE Description="Kodak Clean N CleAR" VwCode="AR-KCNC" />
    </TREATMENT>
</TREATMENTS>
<MATERIALS>
    <MATERIAL Description="1.5 Plastic" VwCode="PL-50-NONE-NONE-00" />
    <MATERIAL Description="1.5 Plastic SRC" VwCode="PL-50-NONE-NONE-16" />
    <MATERIAL Description="Glass 1.70 Clear" VwCode="GH-70-NONE-NONE-00" />
    <MATERIAL Description="Glass" VwCode="GL-50-NONE-NONE-00" />
    <MATERIAL Description="Polycarbonate SBF LUX" VwCode="PO-58-BLUD-NONE-00" />
</MATERIALS>

Given the example data above if I start with a list of:
    Bifocal
    Progressive
and choose Progressive, the list of available designs should display:
    SOLA Access
    SOLA Access 125
Choosing the design SOLA Access would display the lenses:
    ACCESS-50-NONE-NONE-00
    ACCESS-50-NONE-NONE-16
Choosing ACCESS-50-NONE-NONE-00 would list the compatible treatments corresponding to the lens addon id:
    Standard AR
    Back Side AR
    RF Endura
    Kodak CleAR
    Kodak Clean N CleAR
    Polish Edge
    Roll Edge
    Scratch Coat
    Lab UV Coat
Choosing Standard AR would remove the incompatibles and present the remaining treatments:
    Polish Edge
    Roll Edge
    Scratch Coat
    Lab UV Coat

The material is selected when the design is chosen. All the choices lens type, design, material, lens and treatments would be retrieved and put in the XML to create the order.

Hopefully, this clarifies what I am trying to accomplish. My goal is to set up a well designed database that allows me to create a lens order. I am open to choosing data to create the lens order in the easiest clearest way whatever that may be.

[comment]

4 hours ago, JMW said:

If you scroll up in the "conversation" in my post on October 9th there is a picture of the current setup of the tables and relationships.

I am afraid you greatly overestimate my abilities... Esp. the ability to infer what you should have from what you have. 

 

4 hours ago, JMW said:

My goal is to set up a well designed database that allows me to create a lens order. I

I think you should start with an ERD. Based on what I understood (or misunderstood?) from your two XMLs, combined with your explanations, it might look something like this:

 

As you can see, I wasn't able to figure out how Treatments fit into the picture - specifically, what is the relationship between the treatments listed under ADDED_VALUE and treatments listed under TREATMENTS.

One more note: your tables and relationships shouldn't be dictated by the structure of the data you receive. Your structure should be based on what you intend to do with the data. Nowhere in this picture is an entity that shows the lens you have selected, the material you have selected for it, and the add-ons and/or treatments you want to have applied to it. 

 

 

Edited November 17, 2021 by comment

[comment]

On 11/17/2021 at 3:01 AM, JMW said:

Given the example data above if I start with a list of:
    Bifocal
    Progressive
and choose Progressive, the list of available designs should display:
    SOLA Access
    SOLA Access 125
Choosing the design SOLA Access would display the lenses:
    ACCESS-50-NONE-NONE-00
    ACCESS-50-NONE-NONE-16

I gave this part some thought and came up with a simple solution that you could implement with very little effort. I posted a demo file here:
https://fmforums.com/topic/108410-using-a-card-window-instead-of-conditional-value-lists/?_rid=72594

 

 

[JMW]

On 11/19/2021 at 2:20 PM, comment said:

I gave this part some thought and came up with a simple solution that you could implement with very little effort. I posted a demo file here:
https://fmforums.com/topic/108410-using-a-card-window-instead-of-conditional-value-lists/?_rid=72594

As I am always eager to learn I will look at the card window solution you posted. 

Thanks for your patience and help. It is truly appreciated!

On 11/16/2021 at 10:13 PM, comment said:

As you can see, I wasn't able to figure out how Treatments fit into the picture - specifically, what is the relationship between the treatments listed under ADDED_VALUE and treatments listed under TREATMENTS.

You asked about the relationship between addons and treatments. Hopefully, what you are asking is what I am going to explain. It is my understanding that the attribute ADD_ID=”001” in the <LENS ..> element  is used identify which treatments are included or incompatible with a lens. They are identified by the attribute ADD_ID=”001” in the <ADDED_VALUE ..> tag.

The diagram you created is how I have it designed with a many to many relationship between Addons and Treatments. (I also have a join table between Addons and Treatments. Yet, it presents the join table problem you mentioned with importing the data.) I am not tied to this if there is a better way.

On 11/16/2021 at 10:13 PM, comment said:

One more note: your tables and relationships shouldn't be dictated by the structure of the data you receive. Your structure should be based on what you intend to do with the data. Nowhere in this picture is an entity that shows the lens you have selected, the material you have selected for it, and the add-ons and/or treatments you want to have applied to it. 

Sorry, I don't understand what you are trying to say here. I am guessing it is because I don't know how to create or use Entity Relationship Diagrams. Database design is new to me. Do you know where I can learn more about it?

I've written lots of code but mostly code using existing database structures or code outside of databases that doesn't require what you're talking about.

Edited December 14, 2021 by JMW
Clarify quoted data and my response