Show Posts
1
General Board / Re: Inferring PK - FK Relationships
« on: June 16, 2006, 11:33:37 am »Quote
Just to strongly echo bruce's point, and (possibly to put some theoretical framework around it). Value based Relational Database systems (such as all the current) ones, support the relational model but also allow you to join "apples" with "oranges", if the values in the columns just happen to be the same datatype.
Thus, to describe the underlying data as relational without defining the formal relationships is actually a non-sequitur. It is exactly those relationships that give the model its power. In particular, if the RDBMS hasn't defined what are technically known as Inversion Indexes (the indexes on the local table that are the inverse end of the Foreign Key relation) then any set of local columns could be mappable, if the data type is correct.
One of the primary functions of a Data Model is to clarify communication and embed the business rules so that inconsistent data can't be entered into the database (because of the formal referential integrity). If the development team hasn't realised this thus far, then this is the time to document the model as NO ONE knows what's going on! Time and time again, I have found that the definition of a data model (and enforcing the referential integrity) has picked up errors that have passed every other test (the most recent and interesting included a system that had passed three levels of system and user testing and had been in production for over six months - no-one had picked up the fact that invalid data was being propagated.
If the argument is: "We can't have referential integrity will slow down the system"
I have two observations:
1) Ask the enquirer: "How quick do you want the wrong answer?"
2) I have also had association with systems that were designed to be fast (before being right). The system mentioned above (having full temporal referential integrity) ended up being, at times, 33 times faster than the alternate system - which like the one you mention had no relationships and did everything in memory to maintain "throughput". Every time a developer tells you you can't use referential integrity because of speed reasons - suspect a poor design. These assertions are often made with NO empirical evidence whatsoever.
The following quote from a topical historical figure may be pertinent:
Those who are enamored of practice without theory are like a pilot who goes into a ship without rudder or compass and never has any certainty where he is going. Practice should always be based on a sound knowledge of theory.[size=10]Leonardo da Vinci (1452–1519)[/size]HTH,
Paolo
Perhaps I should have clarified my intent and situation here. I'm not attempting to define foreign key relationships for the purpose of deploying those relationships back into the database. I am only attempting to understand 3 databases, that I have been made responsible for, that were acquired from another vendor.
The other vendor, having already transferred these assets to us, are now out of the picture and can not be called on to provide any assistance. Additionally, this vendor never documented the database thoroughly. As such, I am now attempting to analyze an existing set of systems that are already running in production.
I am looking for a way to reverse engineer the schemas with inferred relationships for the sole purpose of trying to understand the schema and how data "may" relate within. I obviously would not trust the inferred relationships, but at least it would give me a running start. I'm only looking for possible relationships from which I will then identify the probable ones and subsequently verify those.
FYI> Embarcadero's ER Studio has this functionality. However, I am an enthusiastic user of EA, and as such was hoping that EA may have someway to do this. Plus I already have a corporate license to EA, and am not looking forward to having to go to my management to explain that I need a $3000 license for ER Studio for this one task.
From: http://www.embarcadero.com/resources/faqs/erfaq.html#Q32
. ER/Studio can reverse engineer relationships even though relationships are not specified in a database. When you reverse-engineer a database you are given the option to Infer Primary Keys or to Infer Foreign Keys (based on indexes or unique constraints).
Again, allow me to reiterate. I am not looking to trust the results of any such inferrence at all. I'm just looking to leverage the results as clues in my investigation of the existing systems. That said, I appreciate your reply and your implications of my not understanding theory (btw: I have 9 years of experience in designing relational databases).
However, my original question still stands:
Does anyone know if EA can infer relationships when reverse engineering a database that only has unique indexes and primary keys? If not, does anyone know if there is a plugin for EA that can add that functionality.
Thanks.
Tyree Jackson