Re: Table design

Hi Sean,

We use something similar to approach 1) to store our microarray data.

We have a data table that has a few specific columns (signal median, bkg
median etc) as these exist in all the file formats... Plus also some generic
columns for the rest of the data fields.

Then we have a definitions table that maps the column header from the file
format to the column name in the database.

It seems to work well for us. I can send you the table definitions if they
are any use to you?

Cheers

Adam

> 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

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.