Re: Array of foreign key

On Sat, Dec 23, 2017 at 11:11 PM, Daevor The Devoted <dollien(at)gmail(dot)com>
wrote:

>
> This is an interesting feature you are talking about. However, I'm not
> sure I properly understand the use-case(s). Could you perhaps please give
> some examples to help me understand? (I don't just mean some SQL code, I
> mean an explanation of the business use-cases served by the code).
>
> I work on a data system for nonprofit organizations that uses Postgresql.
We're constantly having to collect new data, adding new tables or new
columns to existing tables. The acceptable values are all store in tables
and referenced as foreign keys. This works great for situations where
there is one possible answer (e.g., "What is your marital status?") But
for questions which allow multiple responses (e.g., "In the last 30 days,
which types of medical care did you utilize?"), we use an array to store
all the values.

Of course, it is possible to create a separate table to store each
individual response, and tie those back to the table. But that is
considerably more cumbersome, for in my case not much benefit. (We also
have a UI that automatically picks up the FKs to generate web forms with
the allowable responses.) So in my case, allowing the array elements to be
enforced by FKs would be of considerable benefit. In practice, I have
ended up using the arrays anyway, and sacrificing the greater data
integrity the FKs would provide. I've left them commented out in my code
though, wistfully waiting for the day I can turn them on. :)

I've included an example of one of these tables below. Hope this helps
answer your question, even if it may be unique to my particular situation.

Cheers,
Ken

CREATE TABLE tbl_veteran_status (
veteran_status_id SERIAL PRIMARY KEY,
client_id INTEGER NOT NULL REFERENCES tbl_client ( client_id ),
veteran_status_date DATE NOT NULL,
year_entered INTEGER NOT NULL CHECK (year_entered BETWEEN
1920 AND date_part('year',current_date)),
year_exited INTEGER CHECK (year_exited BETWEEN 1920 AND
date_part('year',current_date)),* military_theatre_codes
VARCHAR(10)[], --REFERENCES tbl_l_military_theatre
(military_theatre_code),
military_branch_codes VARCHAR(10)[], --REEFERENCES
tbl_l_military_branch (military_branch_code),
* veteran_status_code VARCHAR(10) NOT NULL REFERENCES
tbl_l_veteran_status ( veteran_status_code ),
has_va_benefits BOOLEAN NOT NULL,
has_service_disability BOOLEAN NOT NULL,
has_military_pension BOOLEAN NOT NULL,
has_received_va_hospital_care BOOLEAN NOT NULL,
added_by INTEGER NOT NULL REFERENCES tbl_staff (staff_id),
added_at TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
changed_by INTEGER NOT NULL REFERENCES tbl_staff (staff_id),
changed_at TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
deleted_at TIMESTAMP(0),
deleted_by INTEGER REFERENCES tbl_staff(staff_id),
deleted_comment TEXT,
sys_log TEXT

CONSTRAINT non_conflicting_data CHECK (
(veteran_status_code IN ('0','5') AND has_va_benefits IS FALSE
AND has_service_disability IS FALSE
AND has_military_pension IS FALSE AND
has_received_va_hospital_care IS FALSE)
OR veteran_status_code NOT IN ('0','5')
)
);

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.