Assuming that:
- every country has at most 1 leader in a given point in time (no overlaps).
- there may be gaps in a country's leadership (if you don't want to allow that, change the scond constraint to
prevtermend = termstart
)
termstart < termend
, i.e. every term lasts at least 1 day (if that is not the case, you can alter the constraints below, either by making that <=
and/or converting the date
columns to datetime
.)
- you want to enforce all these constraints declaratively.
then I think the following (rather complicated) way will do - by simulating a circular linked list for every country):
CREATE TABLE country_leader (
countryid integer unsigned NOT NULL,
seqno integer NOT NULL,
leaderid integer unsigned NOT NULL,
termstart date NOT NULL,
termend date NOT NULL,
prevseqno integer NOT NULL,
prevtermend date NOT NULL,
PRIMARY KEY (countryid, seqno),
UNIQUE (countryid, prevseqno),
UNIQUE (countryid, seqno, termend),
FOREIGN KEY (countryid)
REFERENCES country (countryid),
FOREIGN KEY (leaderid)
REFERENCES politician (politicianid),
FOREIGN KEY (countryid, prevseqno, prevtermend)
REFERENCES country_leader (countryid, seqno, termend),
CHECK (termstart < termend),
CHECK (prevtermend <= termstart OR seqno = 0),
CHECK (prevseqno = seqno - 1 OR seqno = 0)
);
Not sure why one of the answers made such bold statement .
Regardless: everything can be modeled in relational model
create table entry
(
id number(12) primary key,
name varchar2(256) not null unique,
customer_id number not null references customer(id)
);
create table datatypes
(
id number primary key,
name varchar2(256) not null unique
);
create table entry_data (
id number(12) primary key,
datatype_id number not null references datatypes(id),
value varchar2(256), --or CLOB ,no index
value_geometry geom , -- no index
--value_blob blob -- if you neeed, no index
entry_id number not null references entry(id),
);
your task is related to the multi-valued attributes task.
Datatypes table will abstract your datatypes, however 2 value** columns are created in entry_data due to the fact that your data can be different in nature(belong to different Classes), thus would need different storage. But as far as varchar2 vs. number vs.date -- all can be placed into varchar2 , just would need later on some casting. And with very simplistic logic of NVL in code you can determine which column was populated out of value
or value_geometry
.
Best Answer
That sounds like a many to many relationship where you need a bridge table. You would store everyone in an employee table with an ID and then have a separate hierarchy table with manager and employee combinations.