Table Relationships

[alex44]

I’m trying to define a relationship between tables. However, the unique nature of my data doesn’t seem to be allowing typical relationships. Because of my inexperience, I’m not sure if I need to somehow create a relationship (junction table?) or just keep things as they are. Do I need a Foreign Key?
FYI, I’m only querying the data – no updates.

I have two tables that I’m able to join by using a field in Table1 tied to a portion of a field in Table2. No other columns in either table can reliably relate the two tables.
For example:

Table1.ColA has a 5 character string.
Table2.ColA has a 10 character string.
I need to match Table1.ColA to Table2.ColA where the first five characters in Table2.ColA match Table1.ColA.
This match, produces 1 to Many results (for every Table1.ColA string, there are 1 to many Table2.ColA records that match.


IMPORTANT FACTORS:
- Table1 contains data at a SYSTEM level.
- Table2 contains the parts that make up the ‘SYSTEM’ in Table1
- The PARTS in Table2 can be in 1 to many SYSTEMS from Table1
- Table1.ColA is not unique by itself (it’s part of a composite PK in Table1).
- Table2.ColA is not unique by itself (it’s part of a composite PK in Table2).


Because of the confidential nature of the database, I can’t provide samples. However, what I’m more interested in is the higher level design principles that I should or shouldn’t use.
As I said I’m new to this and want to make sure I build a solid foundation before the database begins to grow.

Any help/advice is greatly appreciated!

Thanks in advance,

Alex

 

[alex44]

Thank you Pat. That does make sense. And, with my particular field, it is easy to separate the string.
However, this still leaves me with the FK issue (See initial post); Because each tables PK only relates to the other table with a portion of it's characters, I can only achieve a Many-to-Many relationship.
For example, the first 5 of Table1's PK is present in Table2. The first 6 of Table2's PK is present in Table1. Furthermore, because of the hierarchical structure of the data, it isn't possible to match the Full PK of either table to the records in the other table. This is the only reliable relationship the two tables have.

QUESTION 1: Is this acceptable in the world of DB design? (BTW- these tables are only being queried, not updated).

QUESTION 2: Do I need to create a junction table or use some other process to correct this?

Thanks again for your help!

Alex