![]() |
Telelogic System Architect (steve huntington) | ![]() |
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 |
![]() |
![]()
|
![]() |
|
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? |
|
![]() |
|
![]() |
|
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 |
|
![]() |
|
![]() |
|
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.
|
|
![]() |
|
![]() |
|
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 |
|
![]() |
|
![]() |
|
Thanks for the info William, appreciate it !
|
|
![]() |
FuseTalk Standard Edition v3.2 - © 1999-2009 FuseTalk Inc. All rights reserved.