Welcome to Telelogic Product Support
  Home Downloads Knowledgebase Case Tracking Licensing Help Telelogic Passport
Telelogic System Architect (steve huntington)
Decrease font size
Increase font size
Topic Title: Data Modeling 101 - Unique Index Identification
Topic Summary: Unique Index Identification
Created On: 22-Feb-2006 19:09
Status: Read Only
Linear : Threading : Single : Branch
Search Topic Search Topic
Topic Tools Topic Tools
Subscribe to this topic Subscribe to this topic
E-mail this topic to someone. E-mail this topic
Bookmark this topic Bookmark this topic
View similar topics View similar topics
View topic in raw text format. Print this topic.
 22-Feb-2006 19:09
User is offline View Users Profile Print this message


Jane Stevens

Posts: 17
Joined: 21-Feb-2006

I have been tasked with explaining why a data modeler should be involved in the identification of primary keys (which we refer to as unique indexes here in our DB2 world).  I know this is elementary to all of us, but stating it professionally is sometimes difficult for someone that doesn't really understand data modeling.  Some of the ideas I have so far include, security, enforcement of business rules and data integrity, keeping data in one place, etc.  We strongly believe in tokens (or artificial keys) here since our natural keys are typically > 4 columns and we do not like to migrate that many columns over and over.  We usually try to find a one column natural key whenever we can otherwise we use an artificial key.  Thoughts?

Report this to a Moderator Report this to a Moderator
 23-Feb-2006 09:42
User is offline View Users Profile Print this message


George Brennan

Posts: 7
Joined: 25-Jan-2006

It might depend where you are identifying the primary key as opposed to implementing it.
This might be seen as minor differences in one company but cause religous wars in another.

For my part, we model the data to understand it and to provide a structure for it within a relational model. The relational model has two components, logical and physical and both require that we provide a means of uniquely identifying rows within their enclosing entity or table generally termed the primary ke, this is not the same as a primary key index.

The actual primary key may be different across the logical and physical for a number of reasons. e.g. the logical model may specify a compound business key, the physical might specify an numbered surrogate key.



Edited: 23-Feb-2006 at 09:43 by George Brennan
Report this to a Moderator Report this to a Moderator
 24-Feb-2006 16:45
User is offline View Users Profile Print this message


Jane Stevens

Posts: 17
Joined: 21-Feb-2006

Religious wars - yes that would be a good way to describe my daily discussions with our development staff.  Thanks for the information, I'll add that to our notes.
Report this to a Moderator Report this to a Moderator
 24-Feb-2006 18:27
User is offline View Users Profile Print this message


William Wimsatt

Posts: 17
Joined: 7-Nov-2005

If you do not capture the keys (business/natural) then you do not have a fully developed model. Identification of the natural keys will assure that the entity being identified is a single component and is not derived from some other entity or it is not a combination.

The entity should be uniquely identify by -- the key, the whole key, and nothing but the key. Notice that I am speaking of entities and not tables. Denormalization is a detailed design/build decision based upon specific implementation requirements.

Note that relationships should be considered part of the key components of an entity. NOT FKs as implemented in the logical modeller in SA. This is a problem with the current data modelling toolset in SA - separation of logical from physical modelling.

So, why do we need to have key identification? So we can be assured that we are manipulating the specific object that we expect to be manipulating. I have an application where only surrogate keys are identified and the "object business rule layer" is supposed to assure that we have unique rows in tables. Unfortunately, the application can find a "red fire truck" but cannot be certain that it has a specific "red fire truck". That would be fine for an enterprise EDW dashboard application but not for an operational application that is send emergency equipment out to locations.

I can also give horror stories where the development team used only object ids stored in the tables and did not rely upon business keys. I have seen cases where keys are partially implemented (only a portion of the key is used in the FK) and they mutate over time. A partially implemented mutating foreign key is a very bad thing!!!

Yes, Virginia (Jane), Data Modellers need to identify the keys for entities/tables. There are trade offs in physical design, but make sure that the exceptions are well documented (in code, in QA docs, in meeting notes), because you or some poor DBA will have to deal with the duplicate data and corrupted FKs.

Bill

Edited: 24-Feb-2006 at 22:54 by William Wimsatt
Report this to a Moderator Report this to a Moderator
 27-Feb-2006 19:06
User is offline View Users Profile Print this message


Jane Stevens

Posts: 17
Joined: 21-Feb-2006

Thanks for the info William, appreciate it !
Report this to a Moderator Report this to a Moderator
Statistics
20925 users are registered to the Telelogic System Architect forum.
There are currently 0 users logged in.
The most users ever online was 16 on 30-Oct-2008 at 14:46.
There are currently 0 guests browsing this forum, which makes a total of 0 users using this forum.
You have posted 0 messages to this forum. 0 overall.

FuseTalk Standard Edition v3.2 - © 1999-2009 FuseTalk Inc. All rights reserved.