[BiO BB] database design question

[Dan Bolser]

Database design is a big field!

It is unlikely that you can find an 'out of the box' solution for what you
want, but it is a good idea to search. Many books on data modeling (a way
of fitting data to a particular design concept) exist, but many are based
on principals of good design strategy rather than a particular design
formula. I don't advise reading more than a few articles on database
design (biological database design if you can find them).

The best way I know is make one table for each concept in your data, so
you have genes, give them a table - you mention synonyms, so make a
synonym table, protocols = protocols table, results = results table. If
each protocol has a special results set, then create a separate table for
the results of each protocol. An implicit concept is that of an experiment
which links a gene to a protocol and a set of results. 

as you develope the datbase I advise you constantly try to use it, and
through an itterative process of design and use you converge on an optimal
solution for your requirements.

This is just my way of working, people will always argue different ways
which suit them (or their aims) better.

Cheers,
Dan.

On Sat, 21 Feb 2004, Rowland, John G A wrote:

> I am currently in the process of building a small database in MS
> Access for the purposes of storing (in a computationally searchable
> format) protocol and results data from published and unpublished
> material.
> 
> I would like to be able to search this database for a particular
> gene/protein and see all instances of its occurrence, regardless of
> the use of synonyms and not restrictiong it to only pulling out a
> specific Accession number.
> 
> Unfortunately I am unsure of the best table/relationship setup to
> acheive this!
> 
> Can anyone offer any insight into this problem? (I have googled and
> googled for about 6 months...)
> 
> Regards,
> 
> John
>