Re: D2K - Re-defining foreign keys?

[Quoted] 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.
>
>I am having trouble getting D2K to map the foreign keys on the resultant
>tables - I don't see how to do this in the 'create database wizard' and
>so alter the tables foreign keys once they have been generated (by
>adding the correct key components and deleting the columns D2K added for
>the FKs). If I then generate and run the resultant .SQL I see that the
>index created for these re-mapped keys contains a blank entry and so is
>not generated.
>
>Am I following the wrong modeling approach?
>
>Am I using D2K incorrectly?
>--
>

[Quoted] 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?

http://www.tswi.com
http://www.indusgroup.com

Replace "spambuster" with "tswi" before replying via email Received on Fri Oct 17 1997 - 00:00:00 CEST