Database Design Question

[Isaac Knoflicek]

I could use some advice on the best way to do a join.  I've got one table all setup which is a collection of yeast strains which have a unique Strain ID.  The other table contains 96 well plates, we currently have ~10 plates.  Each plate has unique info, but each of the 96 wells can contain a yeast strain.  A given yeast strain may exist in many wells across many plates, but each well only contains a single strain.

My first pass was to just create 96 "location" fields right in the Plate table, limit it to a "StrainID" value list, but then I realized that to setup a relationship there I'd need to create 96 relationships which doesn't seem right.  I think I can do everything I need to do with scripts, passing the values around and performing searches, but that seems pretty cludgy.

Any suggestions for a better way?

[comment]

14 minutes ago, Isaac Knoflicek said:

 The other table contains 96 well plates, we currently have ~10 plates.

This is confusing: what is a "well" and what is a "plate" and what is the (real-life) relationship between them?

 

If I am guessing correctly, you need to have three tables:

Plates -< Wells >- Strains

Then the next question is whether all 96 wells are filled for every plate.

 

Edited February 3, 2016 by comment

[Isaac Knoflicek]

1 minute ago, comment said:

This is confusing: what is a "well" and what is a "plate" and what is the (real-life) relationship between them?

Understandable, sorry.  The plate is a metal rectangle and the wells are holes in it.  

Maybe it'd help to think of the plate is an array with 96 elements (wells)?  Or the plate is a rack with 96 test tubes (wells) in it?

Here's a picture of one:

[comment]

Yes, that's what I figured - see the edit to my answer.

But there are more questions:

1. Do you ever need to present the contents of a plate in a grid?

2. In what way is the location of a well significant?

3. What will you actually want to do with the data? In some circumstances, you could simply use a repeating field in the Plates table.

Edited February 3, 2016 by comment

[Isaac Knoflicek]

We would like to see the plate in a grid.  My hope for data entry is to have the users go to a strain record and then have a popover where they can choose a plate and just tick the wells where that strain is present.

The user wants to be able to choose a strain and then see the location of each well that contains it, as well as look at a plate and see what strains are in each well.

As far as I know the goal is just to catalogue their yeast strains so they know what's incubating where.

Thanks for your help!

[comment]

9 minutes ago, Isaac Knoflicek said:

The user wants to be able to choose a strain and then see the location of each well that contains it,

Then you would not want to use a repeating field.

You haven't answered my first question: does every plate have content in all its 96 wells?

[Isaac Knoflicek]

Sorry, UI issues, didn't see that.

They do use all 96 wells, I'm not sure if they get back the whole tray filled at one time or fill it over time.  Even if it comes back full they'll need to enter the data?

I'm not sure if that answers the question or not?

Thanks!

Isaac

[comment]

3 minutes ago, Isaac Knoflicek said:

They do use all 96 wells

Well, then you have two options: either pre-create 96 related records in the Wells table every time you create a new record in Plates, or use a helper "grid" table to assist in viewing/populating the wells - see: http://fmforums.com/topic/98967-how-to-manage-hundreds-of-fields-and-their-related-records/#comment-450176

[Isaac Knoflicek]

Thanks!  I created a script which loops through and creates the wells, and I created a layout which has 12 portals and filters the related records to just that column.  Hopefully that layout doesn't bog the system down too much.

Anyway I think I'm in good shape.

Thanks,

Isaac

[comment]

1 hour ago, Isaac Knoflicek said:

a layout which has 12 portals and filters the related records to just that column

You don't need a filter - just set each portal to start at a different row: 1, 9, 17, etc.