Re: Array of foreign key

Поиск

Список

Период

Сортировка

От	Ken Tanzer
Тема	Re: Array of foreign key
Дата	25 декабря 2017 г. 05:19:20
Msg-id	CAD3a31W1shWV331p8tLXkB1utJxAatS3Q94UJawWQH2uvSPpNA@mail.gmail.com обсуждение исходный текст
Ответ на	Re: Array of foreign key (Daevor The Devoted <dollien@gmail.com>)
Список	pgsql-general

Дерево обсуждения

On Sat, Dec 23, 2017 at 11:11 PM, Daevor The Devoted <dollien@gmail.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')   )
);