Good afternoon Every one.
I have seen and used many times, "Date" column (some times, just the date, and some times, Date along with time component) as part of the primary key.
I want to maintain a primary key, that contains a set of columns, a range (duration) of time between two date columns, so there will be no duplicate records for the ENTIRE RANGE.
Here is the process I followed, however I know there can be better ways to do this... Please share it if you can..
My question is - is there any easier/better/organized way to implement this??
Here is my way
---------------
Lets say I want to create a table that maintains the "name changes for a given person over a period of time".
To achieve that I have two tables listed below.
1. This maintain list of ALL person_IDs.
CREATE TABLE person_ids (person_id NUMBER CONSTRAINT person_id_pk PRIMARY KEY);
2. For each person listed in the above table, we want to maintain legal_name over different periods of time.
In this below table
CREATE TABLE person_m
(
rec_id NUMBER CONSTRAINT person_m_pk PRIMARY KEY
,person_id NUMBER CONSTRAINT person_m_fk1 REFERENCES person_ids(person_id)
,legal_name VARCHAR2(32)
,eff_from_ge_dtm DATE
,eff_thru_lt_dtm DATE
,CONSTRAINT person_m_fk2 CHECK(eff_from_ge_dtm <= eff_thru_lt_dtm)
);
Just to make sure, I relay the meaning of the column names.
eff_from_ge_dtm - Effective from (greater than or equal to) this specific date&time.
eff_thru_lt_dtm - The legal name is effective until (less than) this specific date&time.
INSERT INTO person_ids VALUES(1);
INSERT INTO person_m VALUES (1001, 1, 'LN1', TO_DATE('2001/01/01','yyyy/mm/dd'), TO_DATE('2010/01/01','yyyy/mm/dd'));
INSERT INTO person_m VALUES (1002, 1, 'LN2', TO_DATE('2010/01/01','yyyy/mm/dd'), TO_DATE('2012/01/01','yyyy/mm/dd'));
For the same person identified by "1", we have two names for the given two different date&time ranges.
We can not have overlapped date range records for a given person.
For that I have created a function that checks if the incoming record values cause any overlaps and returns a Yes OR No.
I call this in a trigger (or in a another procedure) where the actual insert is going to happen.
CREATE OR REPLACE FUNCTION does_cause_overlap_dt_range_yn(
i_person_id IN NUMBER
,i_lower_date_val IN DATE
,i_upper_date_val IN DATE)
RETURN VARCHAR2 IS
l_yes_no VARCHAR2(1);
l_cnt NUMBER;
BEGIN
BEGIN
SELECT 1
INTO l_cnt
FROM person_m e1
WHERE e1.person_id = i_person_id
AND NOT ( e1.eff_from_ge_dtm >= ALL (i_lower_date_val, i_upper_date_val)
OR e1.eff_thru_lt_dtm < ALL (i_lower_date_val, i_upper_date_val)
);
-- if there is one record, that means it is going to cause overlap
l_yes_no := 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- no overlaps
l_yes_no := 'N';
WHEN TOO_MANY_ROWS THEN
-- overlaps
l_yes_no := 'Y';
END;
RETURN l_yes_no;
END;