Home -> Usenet -> comp.databases.theory -> Re: Physical data modeling books

Re: Physical data modeling books

From: Van Messner <vmessner_at_bestweb.net>
Date: Sat, 19 May 2001 04:09:19 GMT
Message-ID: <PlmN6.2139$gA.733934_at_monger.newsread.com> 







I do a lot of physical design work, but I'm an Oracle DBA.  There really
isn't much written about this.  You need to learn what options are available
to you in Oracle, what the tradeoffs are, and what makes sense in a given
set of circumstances.  Here is a simple physical table creation script using
locally managed tablespaces for tables and global temporary tables for
session information.



--



    
  
  •   This table receives a foreign key from the ADDRESS_TYPES_LOOKUP table.
  
  •   This table receives a foreign key from the LOCATIONS_MSTR table.
  
  •   LOCATIONS_MSTR holds district, city, county, state and nation info.
  
  •   It is intended that the value for ADDRESS_KEY will be drawn from a
  
  •   sequence - SEQ_ADDRESSES_KEY.  If there is any possibility of ad hoc
  
  •   inserts this should be done through a trigger!!

    
--

    



  
      
    
    •   SEQUENCES -----------------------------------------------------


      
--

      DROP SEQUENCE LD.SEQ_ADDRESSES_KEY;

CREATE SEQUENCE LD.SEQ_ADDRESSES_KEY

   INCREMENT BY 1  START WITH 1  MAXVALUE 99999999;

GRANT SELECT on LD.SEQ_ADDRESSES_KEY to ldc


    
    •   TABLE ---------------------------------------------------------


      
--

      Create Table LD.ADDRESSES_MSTR (
ADDRESS_KEY   NUMBER(8)  NOT NULL,

ADDRESS_TYPE_NAME  VARCHAR2(40) NOT NULL, --fk1
ADDRESS_STREET_LINE1  VARCHAR2(50) NULL,

ADDRESS_STREET_LINE2  VARCHAR2(50) NULL,

ADDRESS_STREET_LINE3  VARCHAR2(50) NULL,

LOCATION_KEY   NUMBER(8) NOT NULL, --fk2
ADDRESS_POSTAL_CODE  VARCHAR2(20) NULL,

ADDRESS_DIRECTIONS  VARCHAR2(1000) NULL,

ADDRESS_NOW_ACTIVE  VARCHAR2(5) DEFAULT 'YES' NOT NULL CHECK

(ADDRESS_NOW_ACTIVE IN ('YES', 'NO')),

ADDRESS_DATE_MADE_INACTIVE DATE  NULL,

ADDRESS_CREATE_DATE  DATE  NULL,

ADDRESS_CREATE_PERS  VARCHAR2(40) NULL,

ADDRESS_CREATE_WEBPAGE  VARCHAR2(200) NULL,

ADDRESS_LAST_MODIFY_DATE DATE  NULL,

ADDRESS_LAST_MODIFY_PERS VARCHAR2(40) NULL,

ADDRESS_LAST_MODIFY_WEBPAGE VARCHAR2(200) NULL,

ADDRESS_DESC   VARCHAR2(200) NULL,

  constraint ADDRESS_MSTR_PK primary key (ADDRESS_KEY)
  deferrable initially immediate
  using index tablespace COMMON_MEDIUM
  storage (initial 256K next 256K pctincrease 0 minextents 1 maxextents 249)
)
tablespace COMMON_LARGE
storage (initial 2M next 2M pctincrease 0 minextents 1 maxextents 249)
;



    
    •   INDEXES AND FOREIGN KEYS---------------------------------------


      
--

      PROMPT Creating Index ADDRESS_MSTR_FK1_I on Table ADDRESSES_MSTR
CREATE INDEX ADDRESS_MSTR_FK1_I ON ADDRESSES_MSTR

  (ADDRESS_TYPE_NAME)

  COMPRESS 1

  TABLESPACE COMMON_MEDIUM

    STORAGE(INITIAL 256K NEXT 256K PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS

249)
  PCTFREE  40

;
PROMPT Adding FOREIGN Constraint To ADDRESSES_MSTR Table
ALTER TABLE ADDRESSES_MSTR ADD (

  CONSTRAINT ADDRESS_MSTR_FK1

  FOREIGN KEY (ADDRESS_TYPE_NAME)

  REFERENCES  ADDRESS_TYPES_LKUP (ADDRESS_TYPE_NAME)

);


  
    





PROMPT Creating Index ADDRESS_MSTR_FK2_I on Table ADDRESSES_MSTR
CREATE INDEX ADDRESS_MSTR_FK2_I ON ADDRESSES_MSTR

  (LOCATION_KEY)



  COMPRESS 1



  TABLESPACE COMMON_MEDIUM



    STORAGE(INITIAL 256K NEXT 256K PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS

249)


  PCTFREE  40



;


PROMPT Adding FOREIGN Constraint To ADDRESSES_MSTR Table
ALTER TABLE ADDRESSES_MSTR ADD (



  CONSTRAINT ADDRESS_MSTR_FK2



  FOREIGN KEY (LOCATION_KEY)



  REFERENCES  LOCATIONS_MSTR (LOCATION_KEY)

);





    
  
  •   TRIGGERS ------------------------------------------------------


    
--

    --

    
  
      
    
    •   This trigger fires when you try to insert a row into the
    
    •   ADDRESSES_MSTR table.  Its purpose is to make sure the value you
    
    •   use for the table's key (ADDRESS_KEY) is drawn from the proper
    
    •   sequence (SEQ_ADDRESSES_KEY) rather than from some other source.
    
    •   However if the user sends what could be a legitimate key value we
    
    •   allow him to use that.  The presumption is that he got his sent
    
    •   value by selecting nextval (of the sequence) from dual.  This
    
    •   way the user can know what value he has used.
    
    •   The trigger looks at the usual two columns: ADDRESS_CREATE_DATE,
    
    •   ADDRESS_CREATE_PERS and inserts appropriate values.

      
--

          
    •   Van April 10, 2001

      
--

      CREATE OR REPLACE TRIGGER T_ADDMST_ROWBI1

  BEFORE INSERT ON ADDRESSES_MSTR FOR EACH ROW

DECLARE

  userholder varchar2(100); -- holds current user
  dateholder date;  -- holds sysdate
  holder number;  -- used to prevent comparing null with value
  fromval varchar2(40); -- the old value for the key
  toval  varchar2(40); -- the new value for the key

      
--

          
    •   Main Trigger Body

      
--

      BEGIN

  SELECT sysdate INTO dateholder FROM DUAL;
  SELECT user INTO userholder FROM DUAL;
  --

    
        
      
      •   If the user passes a valid key then use it.
  --
  IF :new.ADDRESS_KEY IS NOT NULL AND :new.ADDRESS_KEY > 0
    AND :new.ADDRESS_KEY < 99999999 THEN
    holder := :new.ADDRESS_KEY;
    GOTO GLOTEMP;

  END IF;

  --
      
      •   If the user passes an invalid key get one from the sequence instead.
  --
  SELECT SEQ_ADDRESSES_KEY.NextVal INTO holder FROM DUAL;
     <<GLOTEMP>>
     --

      
          
        
        •   Put the key into a global temp table so the user has access to it.
     --
     INSERT INTO SEQUENCE_VALUES_GLOTMP (

       SV_TABLE_NAME, SV_TRIGGER_NAME, SV_SEQUENCE_NAME, SV_SEQUENCE_VALUE )

     values ('ADDRESSES_MSTR', 'T_ADDMST_ROWBI1',
       'SEQ_ADDRESSES_KEY', holder);
  :new.ADDRESS_KEY := holder;
  --

      
        
      
      •   For address_create_date always use sysdate.
  --
  :new.ADDRESS_CREATE_DATE := dateholder;
  --
      
      •   For address_create_pers insert whatever the user wanted.  If
      
      •   the user did not choose anything, then insert the user
      
      •   I selected from dual at the beginning of this trigger.
  --
  IF :new.ADDRESS_CREATE_PERS IS NOT NULL THEN
    userholder := :new.ADDRESS_CREATE_PERS;
  END IF;

  :new.ADDRESS_CREATE_PERS := userholder;
END T_ADDMST_ROWBI1;

/



    
      
    
    •   This trigger fires when you try to update a row in the
    
    •   ADDRESSES_MSTR table.  Its purpose is severalfold.
    
    •   If you are trying to change the key value for the row, the change is
    
    •   prevented.
    
    •   If you are changing the ADDRESS_NOW_ACTIVE column from yes to no
    
    •   then we want to insert sysdate into the ADDRESS_DATE_MADE_INACTIVE
    
    •   column.  We also want to cascade similar changes to all intersection
    
    •   tables that intersect with this one.
    
    •   If you are changing from no to yes we will not reactivate
    
    •   the intersections (for now) In the future should we ever change to
    
    •   reactivating the intersections we will want to insert a
    
    •   null into the ADDRESS_DATE_MADE_INACTIVE column.
    
    •   If you are trying to change the ADDRESS_CREATE_DATE or the
    
    •   ADDRESS_CREATE_PERS columns we disallow the changes.
    
    •   We will always use sysdate for ADDRESS_LAST_MODIFY_DATE,
    
    •   no matter what value the user sends.
    
    •   For ADDRESS_LAST_MODIFY_PERS we will use whatever the user
    
    •   sends unless that is NULL.  In that case we will select user from
    
    •   dual and use that value.

      
--

          
    •   Van April 10, 2001

      
--

      CREATE OR REPLACE TRIGGER T_ADDMST_ROWBU2

  BEFORE UPDATE ON ADDRESSES_MSTR FOR EACH ROW

DECLARE

  holder number;  -- used to prevent comparing null with value
  fromval varchar2(500); -- the old value for address_now_active
  toval  varchar2(500); -- the new value for address_now_active
  dateholder date;  -- holds sysdate so all updates will

    
        
      
      •   be for exactly the same time
  userholder varchar2(100);  -- holds current user
  keyholder number;  -- holds the address_key value for the
      
      •   row being worked on
  ERR20004      EXCEPTION;

  ERR20005 EXCEPTION;

  ERR20006 EXCEPTION;


        
--

        
    
      
    
    •   Main Trigger Body

      
--

      BEGIN

  SELECT sysdate INTO dateholder FROM DUAL;
  SELECT user INTO userholder FROM DUAL;
  --

    
        
      
      •   Disallow any changes to the ADDRESS_KEY column.
  --
  fromval := f_testchar(to_char(:old.ADDRESS_KEY));
  toval := f_testchar(to_char(:new.ADDRESS_KEY));
  IF fromval <> toval THEN
    :new.ADDRESS_KEY := :old.ADDRESS_KEY;
    INSERT INTO SESSION_ERRMSGS_GLOTMP (

      SERR_TIMESTAMP, SERR_STORED_CODE_TYPE, SERR_STORED_CODE_NAME,

      SERR_ERROR_NUMBER, SERR_ERROR_MESSAGE)

    VALUES (DATEHOLDER, 'TRIGGER', 'T_ADDMST_ROWBU2',

      -20004, 'For the update case, on table Addresses_Mstr you tried to
change the value for the key column ADDRESS_Key.  This is not allowed.');
  RAISE ERR20004;

  END IF;

  --
      
      •   Disallow any changes to the ADDRESS_CREATE_DATE column.
  --
  fromval := f_testchar(to_char(:old.ADDRESS_CREATE_DATE,'MM/DD/YYYY'));
  toval := f_testchar(to_char(:new.ADDRESS_CREATE_DATE,'MM/DD/YYYY'));
  IF fromval <> toval THEN
    :new.ADDRESS_CREATE_DATE := :old.ADDRESS_CREATE_DATE;
  INSERT INTO SESSION_ERRMSGS_GLOTMP (

      SERR_TIMESTAMP, SERR_STORED_CODE_TYPE, SERR_STORED_CODE_NAME,

      SERR_ERROR_NUMBER, SERR_ERROR_MESSAGE)

    VALUES (DATEHOLDER, 'TRIGGER', 'T_ADDMST_ROWBU2',

      -20005, 'For the update case, on table Addresses_Mstr you tried to
change the value for the column ADDRESS_Create_Date.  This is not
allowed.');
  RAISE ERR20005;

  END IF;

  --
      
      •   Disallow any changes to the ADDRESS_CREATE_PERS column.
  --
  fromval := f_testchar(:old.ADDRESS_CREATE_PERS);
  toval := f_testchar(:new.ADDRESS_CREATE_PERS);
  IF fromval <> toval THEN
    :new.ADDRESS_CREATE_PERS := :old.ADDRESS_CREATE_PERS;
  INSERT INTO SESSION_ERRMSGS_GLOTMP (

      SERR_TIMESTAMP, SERR_STORED_CODE_TYPE, SERR_STORED_CODE_NAME,

      SERR_ERROR_NUMBER, SERR_ERROR_MESSAGE)

    VALUES (DATEHOLDER, 'TRIGGER', 'T_ADDMST_ROWBU2',

      -20006, 'For the update case, on table Addresses_Mstr you tried to
change the value for the column ADDRESS_Create_Pers.  This is not
allowed.');
  RAISE ERR20006;

  END IF;

  --
      
      •   For the ADDRESS_LAST_MODIFY_DATE column always use sysdate.
  --
  :new.ADDRESS_LAST_MODIFY_DATE := dateholder;
  :new.ADDRESS_LAST_MODIFY_PERS := GET_USERNAME;
  --
      
      •   Finally, work with any changes to the ADDRESS_now_active column.
  --
    fromval := f_testchar(:old.ADDRESS_NOW_ACTIVE);
    toval := f_testchar(:new.ADDRESS_NOW_ACTIVE);
    IF (fromval = 'YES' and toval = 'NO') THEN
      :new.ADDRESS_NOW_ACTIVE := 'NO';
      SELECT sysdate INTO dateholder FROM DUAL;
      :new.ADDRESS_DATE_MADE_INACTIVE := dateholder;
      keyholder := :old.ADDRESS_KEY;
        --

      
          
        
        •   Change intersection tables
        --
        UPDATE LD.ADDRESSES_PERSONS_INT

          SET

            ADDRESS_PERSON_NOW_ACTIVE = 'NO',

            ADDRESS_PERSON_DATE_MADE_INAC = dateholder
          WHERE ADDRESS_KEY = keyholder;
 UPDATE LD.ADDRESSES_ORGANIZATIONS_INT

   SET

     ADDRESS_ORG_NOW_ACTIVE = 'NO',

            ADDRESS_ORG_DATE_MADE_INACTIVE = dateholder
   WHERE ADDRESS_KEY = keyholder;
 UPDATE LD.APPLICATIONS_ADDRESSES_INT

   SET

     APPLICATION_ADDRESS_NOW_ACTIVE = 'NO',

            APPLICATION_ADDRESS_DATE_INAC = dateholder
   WHERE ADDRESS_KEY = keyholder;
    END IF;


          
--

          
      
        

    
      
    
    •   For now we are being conservative.  If an object is reactivated we don't
    
    •   reactivate the intersection tables.  In the future if we decide we
    
    •   should reactivate them, then we'd set the inactive date to null.

      
--

          
    •   IF (fromval = 'NO' and toval = 'YES') THEN
    
    •   :new.ADDRESS_NOW_ACTIVE := 'YES';
    
    •   :new.ADDRESS_DATE_MADE_INACTIVE := null;
    
    •   keyholder := :old.ADDRESS_KEY;
    
    •   --
    
    •   -- Change intersection tables
    
    •   --
    
    •   UPDATE LD.ADDRESSES_PERSONS_INT

    
    •   SET

    
    •   ADDRESS_PERSON_NOW_ACTIVE = 'YES',

    
    •   ADDRESS_PERSON_DATE_MADE_INAC = null
    
    •   WHERE ADDRESS_KEY = keyholder;
    
    •   UPDATE LD.ADDRESSES_ORGANIZATIONS_INT

    
    •   SET

    
    •   ADDRESS_ORG_NOW_ACTIVE = 'YES',

    
    •   ADDRESS_ORG_DATE_MADE_INACTIVE = null
    
    •   WHERE ADDRESS_KEY = keyholder;
    
    •   UPDATE LD.APPLICATIONS_ADDRESSES_INT

    
    •   SET

    
    •   APPLICATION_ADDRESS_NOW_ACTIVE = 'YES',

    
    •   APPLICATION_ADDRESS_DATE_INAC = null
    
    •   WHERE ADDRESS_KEY = keyholder;
    
    •   END IF;

EXCEPTION

  WHEN ERR20004 THEN

    RAISE_APPLICATION_ERROR (-20004, 'In LD schema, Trigger '||
      'T_ADDMST_ROWBU2, update case, on table Addresses_Mstr '||
      'you tried to change the value for the key column '||
      'Address_Key.  This is not allowed' );
  WHEN ERR20005 THEN

    RAISE_APPLICATION_ERROR (-20005, 'In LD schema, Trigger '||
      'T_ADDMST_ROWBU2, update case, on table Addresses_Mstr '||
      'you tried to change the value for the Address_Create_Date '||
      'column.  This is not allowed' );
  WHEN ERR20006 THEN

    RAISE_APPLICATION_ERROR (-20006, 'In LD schema, Trigger '||
      'T_ADDMST_ROWBU2, update case, on table Addresses_Mstr '||
      'you tried to change the value for the Address_Create_Pers '||
      'column.  This is not allowed' );
END T_ADDMST_ROWBU2;

/




  
    
  
  •   PRIVILEGES ----------------------------------------------------


    
--

    GRANT INSERT, SELECT, UPDATE on LD.ADDRESSES_MSTR to ldc



  
  •   DATA ----------------------------------------------------------


    
--

    INSERT INTO LD.LG_ADDRESSES_MSTR (





"Patrick Demets" <demets_at_home.com> wrote in message
news:5N1M6.38880$Hk4.599397_at_news1.rdc1.ab.home.com...
> Would anyone be able to suggest good (fairly recent) books on physical
 data

> modeling, especially those that follow Oracle Designer (6i, 6, or earlier)
> theory and conventions?  There are many books available on logical data
> modeling (most notably by Barker and by Silverston, amongst others), but
> very few on PDM.  Or would this fall under "database design"?; to me,
> there's a difference between that and "physical data modeling".  Any help
> would be appreciated.
>
> Cheers,
>
> Patrick Demets
>
>
>


Received on Sat May 19 2001 - 06:09:19 CEST












Original text of this message