Articles   Home

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

-----------------------------------------------------------------------

The views and opinions expressed in this page are strictly those of the page author.
The contents of this page have not been reviewed or approved by the University of Minnesota.