On Fri, Oct 13, 2017 at 12:52 PM, Melvin Davidson <melvin6...@gmail.com> wrote:
> > > On Fri, Oct 13, 2017 at 12:43 PM, Seamus Abshere <sea...@abshere.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.
