I have a situation similar to the following simple example.

Table 1: telephone manufacturers

ID Name
001 Sony
002 Lucent
003 Motorola

Table 2: telephone models

ID Name
001001 KZY123
001002 KZY345
001003 ZZ9
002001 987Z
002002 988X
003001 Mr. Phone
003002 Portaphone

Obviously one can join the tables via SQL to determine full telephone model names e.g.

Sony KZY123
Sony KZY345
Sony ZZ9
Lucent 987Z
Lucent 988X
Motorola Mr. Phone
Motorola Portaphone

My feeling is that the above example is an appropriate data design. However, I believe that the join must be done on a partial field of table 2 (i.e. table1.id = SUBSTR(table2.id,1,3).

One could add a second key to enable joins on full fields (table 2 below). This is wasteful duplication and violates first normal form.

Table 2: phone models modified to enable joins using whole ID field in table 2 with table 1

Manu_id Model_id Name
001 001001 KZY123
001 001002 KZY345
001 001003 ZZ9
001 002001 987Z
002 002002 988X
003 003001 Mr. Phone
003 003002 Portaphone

One could split the ID field in table 2 (see table 2 below). However, that eliminates the primary key. I suppose that one could generate a different primary key but effectively -- even if it took a completely different form - it would functionally be the same as the two ID fields concatenated. Besides -- though I can't recall exactly - I believe there are problems operating on such a table but I can’t remember the problems I have encountered.

Table 2: modified to split the key fields

Manu_ID Model_ID Name
001 001 KZY123
001 002 KZY345
001 003 ZZ9
002 001 987Z
002 002 988X
003 001 Mr. Phone
003 002 Portaphone


What is the correct design choice?

Do you have to join on the partial field in the original example or am I missing something?

Are there disadvantages to joining on a partial field? Are such joins optimizable?

Thanks much.