Re: "Shared strings"-style table

On Fri, Oct 13, 2017 at 12:52 PM, Melvin Davidson <melvin6925(at)gmail(dot)com>
wrote:

>
>
> On Fri, Oct 13, 2017 at 12:43 PM, Seamus Abshere <seamus(at)abshere(dot)net>
> 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.