On Sat, Dec 23, 2017 at 11:11 PM, Daevor The Devoted <dollien@xxxxxxxxx> wrote:
Cheers,
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
(253) 245-3801
learn more about AGENCY or
follow the discussion.