Re: table relationships

Reply embedded . .

On Mon, 23 Aug 2004 19:03:10 -0700, "Jeff" <idont_at_thinkso.com> wrote:

>"Ed Stevens" <nospam_at_noway.nohow> wrote in message
>news:ujoji0t6cnpbn4kfita4u5rf5re779g0ct_at_4ax.com...
>>
>> Ah
>>
>> There's nothing in Oracle, or any other dbms or pseudo-dbms that will
>> necessarily give you that information.
>
>MS Access will! HAHAHAHAHAHA Of course, that depends if the creator built
>the database properly.

Of course it "depends if" - and that's a huge 'if'.

>As for Oracle, I'm assuming that a major database
>application like the one I'm working with HAD do be designed properly with
>relationships defined somewhere. Or do I have too much faith?

You definately have too much faith. "You assume much, Grasshopper."

Bottom line is still -- regardless of the product -- MS-Access, Oracle, MySql, Rdb, DB2, IMS, VSAM, DBase, you-name-it, there is nothing inherent in the product that will guarantee that you can discover data meanings and relationships in any useful way. IF table and column names are meaningful, IF RI has been implemented, IF comment features have been used . . . THEN you have a chance of the database providing the information you want.
>
>> The closest thing you'll come
>> to is examining the foreign-key definitions -- if they exist.
>> Packaged apps are notorious for NOT defining them, preferring to
>> enforce RI in the app instead of the database. You can query
>> ALL_TABLES to get a list of tables, ALL_TAB_COLUMNS to get a list of
>> columns, and ALL_TAB_COMMENTS and ALL_COL_COMMENTS (if they have been
>> populated, and that's a big 'IF') to get table and column
>> descriptions. ALL_DEPENDENCIES might (or might not) reveal anything
>> of interest.
>>
>> Packaged apps are notorious for simply defining tables and columns,
>> and those with cyptic names. No RI, no comments. The only knowledge
>> of the data itself is in the app, not the DB. When this is the case
>> there is there is nothing within the database to indicated that table
>> MCOMITMAS is a child of table MCOMOTMAS nor that they are related by
>> columns MCOMITMAS.COSQFL and MCOMOTMAS.CISQGL. Nothing to indicate
>> that column SBEMFU.SBEMCJ, which is defined as a NUMBER is actually
>> used to store dates . . . .
>>
>> I'm afraid your best bet to the knowledge you seek is documentation
>> from the app vendor. Such documentation may or may not be made
>> available. Vendors tend to see such information as trade secret.
>
>That's very helpful. Thanks!
>
Received on Tue Aug 24 2004 - 08:14:07 CDT