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

 

[Pat Hartman]

Table2 is using a field that contains multiple attributes. In a relational database, the rule is 1 field, 1 attribute. So for example, you would never put both the first name and the last name in a single field. Once you do that you loose the ability to sort by last name (assuming that is the second attribute in the field) without writing a function to extract it. If you've ever worked with name data, you should understand that it is next to impossible to separate names with 100% accuracy once they have been mushed together. In your example the problem isn't as severe since the attributes seem to be fixed in length so you can always use the Left(), mid(), and right() functions to pull out parts of it.

Access (Jet/ACE) supports up to 10 columns in a key or index. There is no reason to mush things together into a single field to make a composite key.

 

[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