Re: Table design

Perhaps this is not a postgresql solution, but you could;

1. first design a common data structure in postgresql.
2. then convert each type of the tab-delimited file to a basic xml
structures.
3. map the structures to the common data structure using xslt.

I hope this helps,

> This might be a bit off-topic, but I'm curious what folks would do with
> this
> situation:
>
> I have about 6 different tab-delimited file types, all of which store
> similar information (microarray gene expression). However, the files come
> from different manufacturers, so each has slightly different fields with
> different meanings. However, there are a few columns that are shared. I
> may need to add table formats in the future (as we get more
> manufacturers).
> I can think of at least three ways to go about storing these data:
>
> 1) Create a single table that has as many columns as needed for ALL
> formats
> and make manufacturer-specific views, naming columns in the view as
> appropriate. Then put rules on the view for inserts, updates, etc. This
> is
> my first choice, I think, but adding a new manufacturer's format means
> creating a new view and possibly adding columns; some columns may NULL for
> large portions of the table.
>
> 2) Use postgres inheritance, but even shared columns in our data may have
> different names depending on the manufacturer, so there may be views
> involved anyway.
>
> 3) Use a fully-normalized strategy that stacks each column into one very
> long table--this would be my last choice.
>
> Thanks for any insight.
>
> (For replies, please try to reply to me directly as well as the list as I
> just get digests right now).
>
> Thanks,
> Sean
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>