Flexible Database Models
Rick van der Lans states in articles and seminars that it would be nice for database implementations to be more generic. Have less business rules implemented in the database structure and more in constraints. Consequences for having rules change are enormous. It requires re-engineering of the database, the applications and rearranging existing data. Having a rule change once a year would be not a big issue, but since requirements change more often and faster than ever, it is perhaps a trend to look at.
Conceptual Model
CaseTalk gives analists the tools to make flexible models. This article briefly shows the scope between an common model in structure and the flexible, more generic, model.
This is the elementary model of the apprenticeship example:
Optimal Normal Form
If database developers need to have a fully optimized database structure, they'll typically perform the GLR and come up with the structure like this:
The database creation script looks like this (using sql92 notation):
CREATE SCHEMA LESSON
CREATE DOMAIN APPRENTICESHIP_CODE AS VARCHAR(4);
CREATE DOMAIN CITY_NAME AS VARCHAR(10);
CREATE DOMAIN DESCRIPTION AS VARCHAR(48);
CREATE DOMAIN FIRST_NAME AS VARCHAR(5);
CREATE DOMAIN NUMBER AS INTEGER
CHECK (VALUE IN (1, 2, 3));
CREATE DOMAIN SURNAME AS VARCHAR(8);
CREATE TABLE APPRENTICESHIP (
APPRENTICESHIP_CODE APPRENTICESHIP_CODE NOT NULL,
CITY CITY_NAME NOT NULL,
DESCRIPTION DESCRIPTION NOT NULL,
PRIMARY KEY (APPRENTICESHIP_CODE)
);
CREATE TABLE APPRENTICESHIP_PREFERENCE (
FIRST_NAME FIRST_NAME NOT NULL,
SURNAME SURNAME NOT NULL,
NUMBER NUMBER NOT NULL,
APPRENTICESHIP APPRENTICESHIP_CODE NOT NULL,
UNIQUE (FIRST_NAME, SURNAME, NUMBER),
UNIQUE (FIRST_NAME, SURNAME, APPRENTICESHIP)
);
CREATE TABLE ASSIGNED_APPRENTICESHIP (
FIRST_NAME FIRST_NAME NOT NULL,
SURNAME SURNAME NOT NULL,
APPRENTICESHIP APPRENTICESHIP_CODE NOT NULL,
UNIQUE (FIRST_NAME, SURNAME),
UNIQUE (APPRENTICESHIP)
);
CREATE TABLE STUDENT (
FIRST_NAME FIRST_NAME NOT NULL,
SURNAME SURNAME NOT NULL,
CITY1 CITY_NAME NOT NULL,
CITY2 CITY_NAME NOT NULL,
PRIMARY KEY (FIRST_NAME, SURNAME)
);
ALTER TABLE APPRENTICESHIP_PREFERENCE
ADD FOREIGN KEY (FIRST_NAME, SURNAME)
REFERENCES STUDENT (FIRST_NAME, SURNAME);
ALTER TABLE APPRENTICESHIP_PREFERENCE
ADD FOREIGN KEY (APPRENTICESHIP)
REFERENCES APPRENTICESHIP (APPRENTICESHIP_CODE);
ALTER TABLE ASSIGNED_APPRENTICESHIP
ADD FOREIGN KEY (FIRST_NAME, SURNAME)
REFERENCES STUDENT (FIRST_NAME, SURNAME);
ALTER TABLE ASSIGNED_APPRENTICESHIP
ADD FOREIGN KEY (APPRENTICESHIP)
REFERENCES APPRENTICESHIP (APPRENTICESHIP_CODE);
Flexible Database Model
However if the database system needs less rules in the structure and more as constraints to allow changing rules later on without re-developing applications, a simple lexicalization will do. This will not optimize the structure but will add all rules as database constraints.
It will look like this:
The script for a flexible model looks like this (again sql92 notation):
CREATE SCHEMA LESSON
CREATE DOMAIN APPRENTICESHIP_CODE AS VARCHAR(4);
CREATE DOMAIN CITY_NAME AS VARCHAR(10);
CREATE DOMAIN DESCRIPTION AS VARCHAR(48);
CREATE DOMAIN FIRST_NAME AS VARCHAR(5);
CREATE DOMAIN NUMBER AS INTEGER
CHECK (VALUE IN (1, 2, 3));
CREATE DOMAIN SURNAME AS VARCHAR(8);
CREATE TABLE APPRENTICESHIP (
APPRENTICESHIP_CODE APPRENTICESHIP_CODE NOT NULL,
PRIMARY KEY (APPRENTICESHIP_CODE)
);
CREATE TABLE APPRENTICESHIP_CITY (
APPRENTICESHIP APPRENTICESHIP_CODE NOT NULL,
CITY CITY_NAME NOT NULL,
PRIMARY KEY (APPRENTICESHIP)
);
CREATE TABLE APPRENTICESHIP_DESCRIPTION (
APPRENTICESHIP APPRENTICESHIP_CODE NOT NULL,
DESCRIPTION DESCRIPTION NOT NULL,
PRIMARY KEY (APPRENTICESHIP)
);
CREATE TABLE APPRENTICESHIP_PREFERENCE (
FIRST_NAME FIRST_NAME NOT NULL,
SURNAME SURNAME NOT NULL,
NUMBER NUMBER NOT NULL,
APPRENTICESHIP APPRENTICESHIP_CODE NOT NULL,
PRIMARY KEY (FIRST_NAME, SURNAME, NUMBER),
UNIQUE (FIRST_NAME, SURNAME, APPRENTICESHIP)
);
CREATE TABLE ASSIGNED_APPRENTICESHIP (
APPRENTICESHIP APPRENTICESHIP_CODE NOT NULL,
FIRST_NAME FIRST_NAME NOT NULL,
SURNAME SURNAME NOT NULL,
PRIMARY KEY (APPRENTICESHIP),
UNIQUE (FIRST_NAME, SURNAME)
);
CREATE TABLE CITY (
CITY_NAME CITY_NAME NOT NULL,
PRIMARY KEY (CITY_NAME)
);
CREATE TABLE CITY_OF_RESIDENCE (
FIRST_NAME FIRST_NAME NOT NULL,
SURNAME SURNAME NOT NULL,
CITY CITY_NAME NOT NULL,
PRIMARY KEY (FIRST_NAME, SURNAME)
);
CREATE TABLE NATIVE_CITY (
FIRST_NAME FIRST_NAME NOT NULL,
SURNAME SURNAME NOT NULL,
CITY CITY_NAME NOT NULL,
PRIMARY KEY (FIRST_NAME, SURNAME)
);
CREATE TABLE STUDENT (
FIRST_NAME FIRST_NAME NOT NULL,
SURNAME SURNAME NOT NULL,
PRIMARY KEY (FIRST_NAME, SURNAME)
);
ALTER TABLE CITY_OF_RESIDENCE
ADD FOREIGN KEY (FIRST_NAME, SURNAME)
REFERENCES STUDENT (FIRST_NAME, SURNAME);
ALTER TABLE STUDENT
ADD FOREIGN KEY (FIRST_NAME, SURNAME)
REFERENCES CITY_OF_RESIDENCE (FIRST_NAME, SURNAME);
ALTER TABLE CITY_OF_RESIDENCE
ADD FOREIGN KEY (CITY)
REFERENCES CITY (CITY_NAME);
ALTER TABLE APPRENTICESHIP_CITY
ADD FOREIGN KEY (APPRENTICESHIP)
REFERENCES APPRENTICESHIP (APPRENTICESHIP_CODE);
ALTER TABLE APPRENTICESHIP
ADD FOREIGN KEY (APPRENTICESHIP_CODE)
REFERENCES APPRENTICESHIP_CITY (APPRENTICESHIP);
ALTER TABLE APPRENTICESHIP_CITY
ADD FOREIGN KEY (CITY)
REFERENCES CITY (CITY_NAME);
ALTER TABLE APPRENTICESHIP_DESCRIPTION
ADD FOREIGN KEY (APPRENTICESHIP)
REFERENCES APPRENTICESHIP (APPRENTICESHIP_CODE);
ALTER TABLE APPRENTICESHIP
ADD FOREIGN KEY (APPRENTICESHIP_CODE)
REFERENCES APPRENTICESHIP_DESCRIPTION (APPRENTICESHIP);
ALTER TABLE APPRENTICESHIP_PREFERENCE
ADD FOREIGN KEY (FIRST_NAME, SURNAME)
REFERENCES STUDENT (FIRST_NAME, SURNAME);
ALTER TABLE APPRENTICESHIP_PREFERENCE
ADD FOREIGN KEY (APPRENTICESHIP)
REFERENCES APPRENTICESHIP (APPRENTICESHIP_CODE);
ALTER TABLE ASSIGNED_APPRENTICESHIP
ADD FOREIGN KEY (FIRST_NAME, SURNAME)
REFERENCES STUDENT (FIRST_NAME, SURNAME);
ALTER TABLE ASSIGNED_APPRENTICESHIP
ADD FOREIGN KEY (APPRENTICESHIP)
REFERENCES APPRENTICESHIP (APPRENTICESHIP_CODE);
ALTER TABLE NATIVE_CITY
ADD FOREIGN KEY (FIRST_NAME, SURNAME)
REFERENCES STUDENT (FIRST_NAME, SURNAME);
ALTER TABLE STUDENT
ADD FOREIGN KEY (FIRST_NAME, SURNAME)
REFERENCES NATIVE_CITY (FIRST_NAME, SURNAME);
ALTER TABLE NATIVE_CITY
ADD FOREIGN KEY (CITY)
REFERENCES CITY (CITY_NAME);
Conclusion
Flexible database models are also considered generic models, since the structure which is created is usable for many different systems, only the rules or constraints are to be adjusted for the specific purpose. CaseTalk allows analists to set these choices per OTFT within the tool allowing an automatic transformation from database structures with all rules implemented or a generic model with all rules as constraints.
Rick van der Lans has a Dutch article on his website with the arguments in favour of generic database models. Click here
|
