Use Case Example for Relational Database Design Models
This is an example of using "Use Case" methods for designing a
web based architecture of a voter database schema that uses a
relational data model for data storage. Use case methods of
design are usually applied to object-oriented models but
I am trying out the method in other paradigms.
The table schema is at the bottom and the physical schema is implemented
in Postgresql. Many requirements and the design to satisfy those
requirements must be done with programming, not just schema. For example,
reports, one mailing to one address, etc.
Requirements:
>PERSON: Members, volunteers, donors, etc., have many attributes
that are beyond SOS_VOTER attributes. Like subscriptions, interests,
modes of contact, sub-organizations. So the schema needs to be
flexible to handle many types of PERSON attributes.
>SOS_VOTER (SOS is Secretary of State of Minnesota) data
should be used to find the political units the PERSON is in to
help campaigns and party organizations.
> Local organizations should administer their PERSON information.
update, use for campaign, etc. This requirement is not
yet fully defined, what if PERSON is claimed by several locals
or state-local crossover?
>Track last change to per record with date and application user.
>Security:
DB level: tables owned by an administrative user
Separate database users will be granted privileges
and used by the application to access the tables.
Application level: A table with user, password will be used to
access the database from the web.
Recovery from loss of table.
Recovery from loss of database.
Recovery from loss of server.
Data Sources:
> FileMaker contacts database. Has typical name, address,
plus the other attributes. Or the Postgresql data that came from it.
> Secretary of State (SOS_VOTER) voter, voter_history, precinct finder,
precinct location definitions at:
http://www.sos.state.mn.us/votlist.html
SOS_VOTER data is controlled by the State of Minnesota and changes on
their schedule.
Use Case Summary:
These use cases (or scenarios) show clearly that SOS_VOTER data
and PERSON may not be the same. PERSON and SOS_VOTER intersect but one
is not a subset of the other in a Venn Diagram.
Also there is an update integrity problem between the SOS_VOTER
data attributes of address and political unit and the PERSON attributes.
So we separate the entities, PERSON and SOS_VOTER.
We will have keep two separate tables for the
PERSON and SOS_VOTER with the same set of attributes for address and
precinct-political data in each table. The use cases will try to make
the PERSON table have the most current attributes since we control that set
of data. This situation adds about as much complexity as this organization
can handle.
The SOS_VOTER is used to fill out the attributes of PERSON (if verified) as
well as for campaign work. But there are update problems.
Example problems:
If a PERSON moves from one city to another tells the organization it should
be tracked. But the SOS_VOTER data is now out of sync and there is no way
to control the update of SOS_VOTER data. If you update your local copy,
it will wipe during the next election update cycle. Update problems also
occur when the PERSON moves, re-registers to vote but does not inform
the organization.
Use Cases:
ADD PERSON:
Get last name, first name, middle name, city, address_zip,
county_description, address_street_name.
MATCH TO SOS_VOTER (defined below)
Note: members may not be in SOS_VOTER, too young, etc.
Also see update problem below.
Get the additional PERSON attributes (phone, etc).
Date_changed, user_changed on each PERSON table.
UPDATE PERSON:
Find entry, change attributes.
MATCH TO SOS_VOTER (if not matched, voter_id Foreign Key
set to default value.)
date_changed, user_changed.
Note:
Update problem: PERSON attributes may change and not be
reflected in the SOS_VOTER data. For example, a PERSON may
move and not re-registered to vote yet.
It also works the other way, a PERSON changes registration
data, does not inform the organization. PERSON data is then
outdated. PERSON update_date can be compared to
SOS_VOTER registration_date to see which is newest data.
SOS_VOTER data updates are controlled by SOS, and we control
PERSON updates.
UPDATING EXTRA PERSON ATTRIBUTES NOT IN SOS_VOTER:
(Like email, contributions, subscriptions, etc.)
Find table, find entry, update attribute, date_changed,
user_changed.
As these attributes are in separate tables,
the update_date of a fax-phone will not make the PERSON address
attributes and political unit attributes update_date change.
All records in the extra attribute tables must have a FK relation
to the PERSON table.
DELETE PERSON:
Find record, delete record from all PERSON tables using FKey
relations with PERSON(id).
UPDATE SOS_VOTER DATA:
Drop FK constraints. Drop SOS_VOTER, SOS_VOTER_HISTORY tables. Tables are
then reloaded with data from the entire State of Minnesota.
Add the extra row to SOS_VOTER needed to enforce PERSON fk constraint.
Note: SOS_VOTER(id), the primary key will not change in the SOS.
Enforce FK constraints.
Find outdated PERSON data by checking the FK to SOS_VOTER attributes
and checking update_date to SOS_VOTER registration_date.
MATCH TO SOS_VOTER report to check for new SOS_VOTER - PERSON matches and
loss of FKey. Resolve differences.
MATCH PERSON TO SOS_VOTER DATA:
Try comparing PERSON last name and home phone number to
SOS_VOTER.Phone_number and last_name.
if matched add sos_voter(id) foreign key to PERSON table
populate PERSON attributes (address, political units)
if not matched sos_voter(id) foreign key gets a default
no match value.
if no match then try SOS_Precinct_finder county_description, address_zip,
address_street_name, street_address, etc.
if matched on geographical data fill in political unit
data.
if not matched leave for special follow up by people.
BACKUP DATABASE:
-- SOS_VOTER entity data is from the Secretary of State, rarely changes
and can be reloaded easily, except the inactive_voter table.
State data is large, on CDROMs and does not need to be backed up.
CD copies of the state disks can be made.
-- PERSON entity, inactive_voter table, and APPLICATION objects
are far smaller, valuable, not easily replaced. Lock database and
backup tables using the psql "copy to" command. Backup files can
be compressed, encrypted and stored on the net in several places
for a "warm" recovery using "cron" daily. A CDROM can be cut once in
a while (once a month) or sooner when data changes a lot.
RECOVERY OF DATABASE:
-- To recover SOS_VOTER data:
Build objects with schema script. Reload from the state CDROMs
or new data from the state. A single table can be recovered by
loading just one part of the data set.
-- To recover PERSON Entity, APPLICATION, inactive_voter data,
and other objects:
Build objects with schema script. Reload from table backup files on
the net or CDROM. Table backup files allow single table recovery.
SECURITY and INTEGRITY Scenarios:
-- SOS_VOTER entity copied by internet attacker.
Result: Patch security holes. The SOS_VOTER entity is public data so
no large problem is seen.
-- PERSON entity and APPLICATION objects copied by internet attacker.
Result: Patch security holes. PERSON entity and APPLICATION objects
should not be public. Probably some sort of organization action
will happen that is not in the scope of this document. The Party
should prepare for this action now.
-- Database gets trashed by internet attacker.
Result: Patch security holes, RECOVER database. Assume the
PERSON entity may have been copied.
-- Server trashed or taken by physical attacker.
Result: Patch physical security problems. Backup hardware
should be planned on now so the organization is aware of how long
a physical replacement of a server will take.
If the machine is taken it should be assumed that the PERSON entity
is copied and the organization action prepared for that scenario
may be needed.
Backup hardware could be another server already on the internet
that needs only a database RECOVERY.
Or it could be another physical machine that needs the OS loaded,
configured, applications loaded, database loaded. A much longer
time frame.
Schema Design Overview:
To handle the differences between PERSON and SOS_VOTER we will track
two sets of address and precinct-political attributes.
PERSON will try to be the most accurate entity since the organization
controls that data and not the SOS_VOTER data which is controlled
by the Secretary of State of Minnesota. Extra PERSON attributes
are tracked by adding tables that fit the types of attributes.
PERSON could have members too young to vote, not yet registered, a donor
outside the state, national party contacts, etc.
-Updates controlled by the organization.
-Attributes domain will include those of SOS_VOTER as a subset.
Other tables will contain other PERSON attributes like email, fax, etc.
-Size is thousands of records.
-The PERSON entity tables that are defined below are examples of the
schema patterns to expand the schema to add more PERSON attributes.
SOS_VOTER is only Minnesota voters.
-Updates controlled by the Secretary of State (SOS).
The organization will update only inactive_voter table.
-Attributes are geographical address, the political units
(like legislative district).
-Size is millions of records.
APPLICATION SUPPORT tables that control access and other database objects.
-app_user is an example of one of the tables.
Data Definition Language Scripts Using Postgresql
-----------------------------------------------------------------------
-- USE NO TABS IN DATA. Data is tab delimited when backed up.
--
-- File to start and stop server:
-- /etc/init.d/postgresql [start|stop|restart|..]
--
-- To create a database and the user to create the tables:
-- su postgres
-- createdb ExampleDB
-- psql ExampleDB
-- CREATE USER table_owner;
-- As the table_owner "Administrator" create the database objects (tables,
-- index, sequences, ...)
-- psql ExampleDB
-- ...
-- \q
-- psql script to create and load Secretary of State (SOS)
-- sourced voter data tables. These tables are select only for
-- all users. The data is public, and can be used by all locals
-- and state organizations.
-- Secretary of State (SOS) voter registration
-- Add a default value for id that means that there is no
-- match to voter and allows the foreign key in PERSON to be enforced.
DROP TABLE sos_voter;
CREATE TABLE sos_voter (
id integer NOT NULL, -- primary key, add default record for fk to PERSON.
County_code text, -- County_Number from sos_precinct_finder.
First_name text,
Middle_name text,
Last_name text,
Suffix text, -- ??
House text,
Street_Name text,
Unit_type text,
Unit text,
Address_Line_2 text,
City text,
State text,
Zip text,
Phone_number text,
Registration_date date,
Birth_year integer,
County_description text,
Status_description text,
Precinct_Split_ID text, -- Precinct divided by school district
Precinct_Description text,
Precinct_Code text,
MCD text, -- City or Township number.
Ward text,
School_District text,
Judicial_District text, -- State Judicial District
Legislative_District text,
Senate_District text,
Congressional_District text,
Commissioner_District text,
City_Township_Code text,
Special_1 text, -- County designated district
Special_2 text, -- County designated district
Constraint voter_pkey Primary Key (id)
);
REVOKE ALL PRIVILEGES ON sos_voter FROM PUBLIC;
GRANT SELECT ON sos_voter TO PUBLIC;
-- Secretary of State (SOS) voter history
DROP TABLE sos_voter_history;
CREATE TABLE sos_voter_history (
id integer REFERENCES sos_voter,
Election_Date date, -- Date of election.
Election_Description text, -- label of election.
Voting_method char(1), -- in (P At Polls, A Absentee)
Constraint voter_history_pkey Primary Key (id,election_date)
);
REVOKE ALL PRIVILEGES ON sos_voter_history FROM PUBLIC;
GRANT SELECT ON sos_voter_history TO PUBLIC;
-- Secretary of State (SOS) precinct finder
DROP TABLE sos_precinct_finder;
CREATE TABLE sos_precinct_finder (
County_Number text, -- County number
MCD_Number text, -- MCD city or township number
MCD_Name text, -- MCD city or township name
Precinct_Number text, -- Precinct number
Precinct_Name text, -- Precinct name
From_address text, -- Street address range start.
To_address text, -- Street address range end.
Odd_Even text, -- in (E,B,O) Even, Odd, Both sides of street
Street_Name text, -- Street name for address range to affect.
Street_Direction text, -- Street direction.
Zip_Code text, -- US Postal code.
Ward text, -- Ward
Congressional_District text, -- Congressional district
Legislative_District text, -- Legislative district
Commissioner_District text, -- County Commissioner district
School_District text, -- School district
Precinct_Split_Number text, -- Precinct, divided by School District
Special_District_1 text, -- County designated district 1
Special_District_2 text, -- County designated district 2
State_Judicial_District text -- State judicial district
);
-- Constraint precinct_finder_pkey Primary Key (??)
REVOKE ALL PRIVILEGES ON sos_precinct_finder FROM PUBLIC;
GRANT SELECT ON sos_precinct_finder TO PUBLIC;
-- Secretary of State (SOS) precinct location field description
-- field position length description
DROP TABLE sos_precinct_location;
CREATE TABLE sos_precinct_location (
County_Number text, --01 02 County number
Precinct_Number text, --03 04 Precinct number
Precinct_Name text, --07 22 Precinct name
Type_of_Address text, --29 01 P=Polling (Active-for upcoming election)
Place_Name text, --30 25 Place name
Place_Address text, --55 34 Place address
Place_City_State_Zip text, --89 25 Place city, state, zip
Ward text, --114 02 Ward
Congressional_District text, --116 02 Congressional district
Legislative_District text, --118 03 Legislative district
Commissioner_District text, --121 02 Commissioner district
Special_District_1 text, --123 02 County designated district 1
Special_District_2 text, --125 02 County designated district 2
State_Judicial_District text, --127 02 State judicial district
MCD_Number text, --129 03 MCD city or township number
Precinct_Split_Number text --132 13 Precinct, divided by School District
);
-- Constraint precinct_pkey Primary Key (Precinct_Number,Precinct_Split_number)
REVOKE ALL PRIVILEGES ON sos_precinct_location FROM PUBLIC;
GRANT SELECT ON sos_precinct_location TO PUBLIC;
-----------------------------------------------------------------------
-- psql script for the rest of the tables.
-- List of inactive sos_voter ids and reason they are inactive.
DROP TABLE inactive_voter;
CREATE TABLE inactive_voter (
voter_id integer REFERENCES sos_voter,
reason text, -- Reason voter inactive.
update_date date, -- Date record changed.
update_user text -- The application user updating record
);
REVOKE ALL PRIVILEGES ON inactive_voter FROM PUBLIC;
-- tables for the PERSON entity.
-- organization contacts, volunteers, members, organizations
-- The basic pattern for extending attributes is to create at
-- table with a key, foreign key to person, and attributes needed.
-- person is the table that closely matches voter.
DROP TABLE person;
CREATE TABLE person (
id integer PRIMARY KEY, -- get value from a sequence
voter_id integer REFERENCES sos_voter, -- default value (0?) if
-- person not in sos_voter.
First_name text,
Middle_name text,
Last_name text,
Suffix text, -- ??
House text,
Street_Name text,
Unit_type text,
Unit text,
Address_Line_2 text,
City text,
State text,
Zip text,
Phone_number text,
Registration_date date,
Birth_year integer,
County_description text,
Status_description text,
Precinct_Split_ID text, -- Precinct divided by school district
Precinct_Description text,
Precinct_Code text,
MCD text, -- City or Township number.
Ward text,
School_District text,
Judicial_District text, -- State Judicial District
Legislative_District text,
Senate_District text,
Congressional_District text,
Commissioner_District text,
City_Township_Code text,
Special_1 text, -- County designated district
Special_2 text, -- County designated district
update_date date, -- Date record changed.
update_user text -- the application user updating record
);
REVOKE ALL PRIVILEGES ON person FROM PUBLIC;
-- For other PERSON attributes NOT in SOS_VOTER we can use other tables.
-- All will have similar foreign key relations to the PERSON table.
-- Contact preference.
DROP TABLE person_contact_preference;
CREATE TABLE person_contact_preference (
id integer REFERENCES person,
calls text check (calls in ('yes','no')),
email text check (email in ('yes','no')),
usmail text check (usmail in ('yes','no')),
update_date date, -- Date of update.
update_user text -- the application user updating record.
);
REVOKE ALL PRIVILEGES ON person_contact_preference FROM PUBLIC;
-- Contact audit to track people so lists do not get overused.
DROP TABLE person_contact_audit;
CREATE TABLE person_contact_audit (
id integer PRIMARY KEY, -- from sequence.
person_id integer REFERENCES person,
contact_type text, -- How, why person contacted.
update_date date, -- Date of update.
update_user text -- the application user updating record.
);
REVOKE ALL PRIVILEGES ON person_contact_audit FROM PUBLIC;
-- interests, like issues, etc.
DROP TABLE person_interest;
CREATE TABLE person_interest (
id integer PRIMARY KEY, -- primary key from sequence
person_id integer REFERENCES person, -- PERSON(id) FK
interest_label text, -- interests like issues, etc.
-- check constraint??
notes text, -- notes describing the interest.
update_date date, -- Date of update.
update_user text -- the application user updating record.
);
REVOKE ALL PRIVILEGES ON person_interest FROM PUBLIC;
-- Subscriptions to lists
DROP TABLE person_subscription;
CREATE TABLE person_subscription (
id integer PRIMARY KEY, -- generate from a sequence.
person_id integer REFERENCES person,
subscription_type text check (subscription_type in ('sunflower')),
-- add more types for other mailings.
subscription_date date, -- date subscription is good through
-- add 100 years for always good.
update_date date, -- the timestamp when you update the record
update_user text -- the application user updating record
);
REVOKE ALL PRIVILEGES ON person_subscription FROM PUBLIC;
-- contact methods not home address related
DROP TABLE person_contact;
CREATE TABLE person_contact (
id integer PRIMARY KEY, -- generate from a sequence.
person_id integer REFERENCES person,
contact_type text check (contact_type in ('home_phone','work_phone',
'fax','cell','email')),
contact text, -- the actual phone or email
update_date date, -- the timestamp when you update the record
update_user text -- the application user updating record
);
REVOKE ALL PRIVILEGES ON person_contact FROM PUBLIC;
-- Application support tables.
-- USER is the application security table.
DROP TABLE app_user;
CREATE TABLE app_user (
name text PRIMARY KEY, -- identity of application user
password text NOT NULL -- password for application user
);
REVOKE ALL PRIVILEGES ON app_user FROM PUBLIC;
\q
----------------------------------------------------------------------------
-- psql script to load the SOS_VOTER tables from the files supplied by
-- the Secretary of State. Run with the id of the owner of the tables.
VACUUM; -- clean up database and any aborted loads.
SET DATESTYLE TO '???'; -- Depends on the SOS_VOTER date format.
\COPY sos_voter from '/complete/file/path'; -- Tab delimited default.
-- using delimiter '|'; -- if using other delimiter like |.
-- Load sos_voter_history after the sos_voter for valid foreign key constraint
\COPY sos_voter_history from '/complete/file/path';
\COPY sos_precinct_finder from '/complete/file/path' with null as '';
\COPY sos_precinct_location from '/complete/file/path' with null as '';
VACUUM ANALYZE; -- Update system tables to help optimizer pick fastest
-- query path.
\q
-----------------------------------------------------------------------
-- Backup script
COPY PERSON to '/complete/file/path'; -- with null as '';
-- Tab delimited default.
\q
----------------------------------------------------------------------
-- psql script to create users to access tables and grant privileges
to those users.
DROP USER mnstate;
CREATE USER mnstate WITH PASSWORD 'string'; -- User with web access,state level
--GRANT SELELCT ON tablename;
--CREATE GROUP name
--ALTER GROUP name ADD USER uname1, ...
--ALTER GROUP name DROP USER uname1, ...
\q
-----------------------------------------------------------------------