Re: "Shared strings"-style table

[Melvin Davidson <melvin6925@xxxxxxxxx>]

On Fri, Oct 13, 2017 at 12:52 PM, Melvin Davidson <melvin6925@xxxxxxxxx> wrote:

On Fri, Oct 13, 2017 at 12:43 PM, Seamus Abshere <seamus@xxxxxxxxxxx> wrote:

> > On Fri, Oct 13, 2017 at 8:49 AM, Seamus Abshere 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?)

On Fri, Oct 13, 2017, at 01:29 PM, Melvin Davidson wrote:
> What was described is exactly what relations and Foreign Keys are for.

hi Melvin, appreciate the reminder. Our issue is that we have 300+
columns and frequently include them in the SELECT or WHERE clauses... so
traditional normalization would involve hundreds of joins.

That's why I ask about a new table or column type that handles basic
translation and de-duping transparently, keeping the coded values
in-table.

>I ask about a new table or column type that handles basic translation

AFAIK, there is no such thing currently available.Your initial post indicated you were working with spreadsheets and were

looking to translate to PostgreSQL database. There is no short cut to normalizing, but the time you spend doing so in initial

design will well be worthwhile once it is implemented. 

--

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

Seamus,

Just a thought. As I mentioned previously, there is no shortcut to optimizing your database. However, you can do it in increments.

First, create all your foreign key / repetative data tables.

Next, add additional FK columns to you current tables to reference the fk / repetative data tables.

Modify your application / queries to utilize the new columns.

Do extensive testing to make sure your modifications work properly.

VERY IMPORTANT: Before the next step, make a backup of the existing database and verify you have a good copy.

Finally, drop all the old repetative data columns.

--

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