Re: "Shared strings"-style table

On Fri, Oct 13, 2017 at 12:12 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Fri, Oct 13, 2017 at 8:49 AM, Seamus Abshere <seamus(at)abshere(dot)net>
> wrote:
>
>> Theoretically / blue sky, could there be a table or column type that
>> transparently handles "shared strings" like this, reducing size on disk
>> at the cost of lookup overhead for all queries?
>>
>> (I guess maybe it's like TOAST, but content-hashed and de-duped and not
>> only for large objects?)
>>
>
> Row-independence is baked into PostgreSQL pretty deeply...
>
> I think an enum type is about as close are you are likely to get if you
> don't wish to setup your own foreign-key relationships with surrogate keys.
>
> David J.
>

I STRONGLY advise againt the use of ENUMS.

What was described is exactly what relations and Foreign Keys are for.

Example:
CREATE TABLE residence_type
(
residence_type_id INTEGER NOT NULL,
residence_type_desc TEXT NOT NULL,
CONSTRAINT residence_type_pk PRIMARY KEY (residence_type_id)
);

CREATE TABLE state
(
state_id CHAR(02) NOT NULL,
state_name TEXT NOT NULL,
CONSTRAINT state_pk PRIMARY KEY (state_id)
);

CREATE TABLE residence
(
residence_id BIGINT NOT NULL,
residence_type_id INTEGER,
street_num CHARACTER(10),
street_name CHARACTER(20),
city CHARACTER(40),
state_id CHAR(02),
CONSTRAINT residence_pk PRIMARY KEY (residence_id),
CONSTRAINT fk_residence_state FOREIGN KEY (state_id)
REFERENCES state (state_id),
CONSTRAINT fk_residence_type FOREIGN KEY (residence_type_id)
REFERENCES residence_type (residence_type_id)
);

SELECT t.residence_type_desc,
r.street_num,
r.street_name,
r.city,
s.state_name
FROM residence r
JOIN residence_type t ON t.residence_id = r.residence_id
JOIN state s ON s.state_id = r.state_id
WHERE residence_id = 12345;

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.