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 Fri May 18 2001 - 23:09:19 CDT
