Re: Designer 2000, use of foreign keys

[Quoted] [Quoted] On Thu, 2 Oct 1997 14:19:10 +0100, Andy Hardy <Andy_Hardy_at_camk.demon.co.uk> wrote:

>
>At the ERD level, the foreign key relationship is expressed as a
>relationship between tables with no reference to the attributes involved
>- the primary key is inferred.
>
>At table level, after running the database wizard, D2K uses the ERD to
>determine that a foreign key is required and, therefore, a new column
>must be added to the created table to facilitate this. You end up with a
>new column called something like 'FE_FIRST_KEY'.
>

The column should be a copy of whatever forms the unique identifier of the second entity. You should define the UIDs of entities before you start using the database wizard.

>
>If you retrofit from a table to an entity (as you would after reverse
>engineering a database), D2K removes any column entries mentioned in the
>foreign keys. In this way it is consistent with the above, i.e ERD
>relationships do not have attributes.
>
>This has some unfortunate consequences:
>
>1. At the entity creation level, you do not enter those attributes which
>are foreign keys as the relationships will produce 'something'
>attributes in their place.
>

[Quoted] That's not really a big problem as it might look. The reason you don't specify foriegn keys is so you can define relationships before the unique identifiers need to be specified.

>a) How can you then reliably refer to these attributes which will only
>exist when you perform a database creation - which can/should only occur
>once and at quite a late stage?

Strictly speaking, they're not attributes of the entity at all. You should be referring to the relationship in this case.

[Quoted] Unfortunately there's no way to associate a relationship with a business function, you can only imply it by referring to the both entities in the same function. 99% of the time it's perfectly clear what it means.

>2. After reverse engineering a database, you cannot produce an ERD
>diagram which shows all the attributes associated with an entity - those
>relating to the foreign keys are removed.

If you want the keys, draw a physical schema diagram instead - that's based on the tables rather than the entities. I suspect this is what you actually want here.

>a) How can you produce 'database' documentation in which a percentage of
>the columns/attributes will not be displayed?

If you want a 'database' documentation showing all the columns/ attributes, you should produce a report based on the tables and columns rather than on the entities and attributes

>b) Having reverse engineered a database and related forms, how could
>they possibly re-generate correctly again when some of the attributes
>are now missing?

As I said earlier, they're not actually missing, they're defined as relationships.

>3. Is D2K not to be used for reverse engineering legacy systems?
 

>4. Is D2K not to be used where development using other tools occurs
>concurrently and entity attributes need to be known?
>
>As you can tell, I'm totally confused by this tool.
>
>I guess that what I'm asking is: 'Should the entity relationships
>include references to the key components?'
>
>Please explain where I'm going wrong - I suspect that I'm thinking along
>implementation lines too early in the process.
>
>Andy
>--
>Andy Hardy
>Senior IT Systems Engineer
>Cegelec AEG
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>Opinions are mine and do not necessarily reflect those of Cegelec AEG

[Quoted] I think you're confusing the physical and logical data models. Although they both represent the same system, they're defined differently, because they're used for different purposes.

The Database Schema (or physical data model) represents what you're physically implementing. It contains all the physical components such as primary and foreign keys, constraints, indexes etc. It includes things such as surrogate keys and denormalised columns that have been implemented for performance.

The Entity Model (or logical data model) represents the underlying business information which must be held in the database. It doesn't, nor should it, specify how the data is physically stored. It's typically produced during the analysis phase of a project, and deliberately doesn't attempt to preempt any design decisions.

[Quoted] Reverse-engineering a physical data schema to produce a logical model is intended for when you want to re-engineer an existing system, either to take advantage of new database technology, or to clean up a old legacy system that might not have been fully normalised.

Hope this helps.

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

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