Results 1 to 9 of 9

Thread: database schema

  1. #1
    Join Date
    Jul 2003
    Posts
    16

    Unanswered: database schema

    I am working on a datamodel that is used for 2 separate applications with some common tables.

    Currently, the common tables exist with a normal name (TABLE_NAME)but the application tables exist with a prefix designating the application before the table (AP_TABLE_NAME).

    Is there a better design that can be used with schemas in Oracle to logically group application tables in a way that does not affect the actual names of the tables?

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Wink

    Create a different schema for each application!!!

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jul 2003
    Posts
    16
    But then I would need to replicate the common tables in each schema. Is there a way to tie the applications to the common tables without replicating the common tables?

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Question

    NO, common tables could reside in common schema!

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Jul 2003
    Posts
    16
    So if I have a database structure as follows:

    ap1.TABLE_NAME1
    ap2.TABLE_NAME2
    common.TABLE_NAME3

    then I could perform the following query?:

    select TABLE_NAME1.FIELD_NAME
    from TABLE_NAME1, TABLE_NAME3
    where TABLE_NAME1.unique_id = TABLE_NAME2.unique_id

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Smile

    First you have to create public synonyms if you do not want to prefix the tables with the schema name, otherwise you would need to write the query as:

    select TABLE_NAME1.FIELD_NAME
    from app1.TABLE_NAME1, common.TABLE_NAME3
    where TABLE_NAME1.unique_id = TABLE_NAME2.unique_id
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Jul 2003
    Posts
    16
    What if two tables in different shemas have the same table name?

  8. #8
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198
    Originally posted by aciccarelli
    What if two tables in different shemas have the same table name?
    Then if you want to query them at the same time you have qualify them with the schema name. You can make a public synonym for them with different names.

  9. #9
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198
    Originally posted by aciccarelli
    What if two tables in different shemas have the same table name?
    Then if you want to query them at the same time you have qualify them with the schema name. You can make a public synonym for them with different names.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •