Unsure what this relationship is called Essentially, I have googled around but I am unsure what would this type of relationship be called. Table 3 contains a field [Labelled as STRING], that has multiple 1 to many relationships to Table 2 [Labelled as STRING and STRING2, STRING connects to both]. However, Table 1 has a 1 to many relationship with Table 2 through STRING2.
I am thinking that Table 3's STRING is erroneously linked to Table 2's STRING2, but I am unsure if this is intended, and whether if these types of relationships do exist. I am thinking that STRING could be a composite of STRING and STRING2 in table 2, but I am sure that breaks a few db rules (e.g.atomicity), does anyone have any pointers I should read up on?
What you're describing looks like a many-to-many relationship between table1 and table3 through table2.
Direct many-to-many relations between two tables are illegal and require an intermediate table.
Typically, many-to-many relations are usually verbs in natural language with T1 and T3 being nouns, and T2 being a verb - e.g. Customer (T1) Buys (T2) Books (T3). T2 should have both the PK of T1 and T3 as FKs in its own table to ensure the data matches across the three tables.
The design you have is problematic as strings are not unique, and therefore you can end up joining MJones in T1 to MJones in T3 with no assurance that the two individuals are the same person.
This type of error is acceptable in say a marketing database where some errors may be non-critical, and typically this is a good use in a non-relational database e.g. Big Data. However, in a database where accuracy is paramount e.g. a database recording medicines administered to patients this type of error would have disastrous consequences.
If you use a unique key in your design you will always ensure data consistency - strings are potentially problematic.