Re: D2K - Re-defining foreign keys?

In article <3447679b.19916075_at_69.0.9.9>, Tim Hall <tim.hall_at_spambuster.com> writes
>On Fri, 17 Oct 1997 14:06:11 +0100, Andy Hardy
><Andy_Hardy_at_camk.demon.co.uk> wrote:
>
>>I have a query regarding re-mapping foreign keys between the entities
>>and the tables.
>>
>>I'm trying to create a relationship showing that a mixed_material
>>consists of two other materials. Each material must exist within the
>>materials table.
>>
>>For example: mixed_material 'concrete' consists of materials 'cement'
>>and 'gravel'. All three materials are to exist within the materials
>>table.
>>
>>I want to be able to look at a list of materials and 'summarise' them
>>into the least number of materials that can be produced from that list.
>>
>>I have two tables:
>>
>>materials
>> R_ID (PK)
>> DESCRIPTION
>>
>>mixed_materials
>> SR_ID (PK)
>> material1 (FK to materials.R_ID)
>> material2 (FK to materials.R_ID)
>> resultant_material (FK to materials.R_ID)
>>
>>which is shown as mixed_materials with three many-to-one relationships
>>with materials.
>>
 [snip]
>
>What does your entity model consist of? To get the end result you
>describe, you should have something resembling the following:
>
>Entity MATERIAL
> Attribute ID (unique identifier)
> Attribute DESCRIPTION
>
>Entity: MIXED MATERIAL
> Attribute: ID (unique identifier)
>
>Relationships:
> MATERIAL -----< MIXED_MATERIAL
> MATERIAL -----< MIXED_MATERIAL
> MATERIAL -----< MIXED_MATERIAL
>
>You could leave off the IDs, and get the wizard to create surrogate
>keys. What you shouldn't do is create any foreign keys to the
>MIXED_MATERIAL entity.
>
>The wizard should use the unique ID of the MATERIAL entity and the
>relationships to generate the foreign keys on the MIXED_MATERIALS
>table.
>
>Does this help?

Tim,

[Quoted] I agree, but I get hideously named foreign keys generated along the lines of 'R_ID', 'first_material_is_a_R_id' and 'second_material_is_a_R_id' and 'resultant_is_a_R_id' i.e. the foreign keys are generated from a concatenation of the ERD foreign key name and the primary key at the other end. Not very pleasant!

Andy

Andy Hardy. PGP key available on request