Hi Elizabeth,

I would suggest that in a first step you have to define all lab processes which are supposed to be tracked in your new database. This will probably be quite a lot of work (depending on the size of the lab) as you might have to talk to a few different people to ensure that you fully understand what everyone is doing in their particular workflow. This information will then form the basis to generate a high level flow chart depicting how a sample will be handled throughout its processing (all the different possible assays, experiments, sending to other providers), as well as what kind of data it will produce and where this data ends up at the moment. The chart will probably be very complex, but it will achieve several things:

1) You will have a much clearer picture of what kind of processes and dataflows are happening in the lab

2) this will form the basis for all further discussions and planning of the software implementation

3) it will also form the basis of what exactly needs to be implemented as a minimum requirement in order to have project milestones

4) it will allow the generation of test cases to later test the functionality of the database

5) the IT guys love flowcharts and it will make it much easier for them to design a database which can handle all the processes

These steps should come well ahead before making a decision which kind of DB system or web app environment should be used to implement the solution. All the underlying logic needs to be developed before anyone starts producing code.

However, before starting to develop some custom database and frontend I would also suggest to look at what is currently available and if existing solutions could be customized to your requirements. E.g. http://www.labkey.com/ is a free solution for managing biomedical data (both research & diagnostic) but it is not trivial to set up and customize - on the plus side it might save a lot of work reinventing things that are already out there.

Hope that helps!

MySQL will be good to begin with.

Hi Elizabeth...Some more details would be helpful. Like what program or language are you developing your analysis process or pipeline in? Also, what is the purpose of the class? Do the students need to learn database development or is an off-the-shelf solution better tailored to the purpose of the course? One I hear more I can make some suggestions.

Hello Elizabeth,

I agree with Oscar, that your preferred route for this project, if you've no previous experience with database construction, will be to choose a commercial workflow management product. Otherwise, you will spend an inordinate amount of time needlessly learning how to write a database program architecture, when all you really need to is let the commercial program construct your architecture for you.

They are informatics students, so they are working with established programs, but I think they can code within SQL. I am having them do the database design, but the challenge is that they have no understanding of biology at all. Their project is to design a working database for us. I have to work with them to describe what we want the database to do, but they are doing the coding and set up. I will not be doing any of the coding or construction myself.

Right now we track things using a bunch of excel files during the benchwork steps, and then we do DNA sequence analysis using a linux based pipeline. That is currently run by our computer programmer person.

I am particularly interested in how to deal with samples that have to be repeated. Sometimes we send DNA for sequencing and we have to have the facility repeat the sequencing because the data does not pass our quality controls. Or we will have to reextract samples if the DNA does not pass our QCs.

If you can give me examples of problems you have encountered when working with a database, or tips to make a better database, that would be great. I want to make it as easy to use as possible.

Hi Elizabeth,

Thanks for the information. That helps immensely. If the students are informatics students they should be comfortable scripting with python or perl. They could use the BioSQL schema (http://www.biosql.org/wiki/Schema_Overview) and create a database in MySQL then use biopython or bioperl to interact with the schema.

With regards to multiple sequencing entries for a biological sample this can be represented in BioSQL where a sample is a bioentity and each sequencing run is a biosequence.

Hi Elizabeth,

I built a sample tracking database for our group last year using Microsoft Access. That's probably not the best program but I found it a little easier as I am a lab based biologist rather than a bioinformatician. Our database was only for recording for human tissue act, so no analysis was required. The main problem we had in the group was giving everything a unique name/ID, and making it easy enough for people to input data. We had a label printer and that was quite a large incentive for people to use the database.

Hi Elizabeth,

I would suggest that in a first step you have to define all lab processes which are supposed to be tracked in your new database. This will probably be quite a lot of work (depending on the size of the lab) as you might have to talk to a few different people to ensure that you fully understand what everyone is doing in their particular workflow. This information will then form the basis to generate a high level flow chart depicting how a sample will be handled throughout its processing (all the different possible assays, experiments, sending to other providers), as well as what kind of data it will produce and where this data ends up at the moment. The chart will probably be very complex, but it will achieve several things:

1) You will have a much clearer picture of what kind of processes and dataflows are happening in the lab

2) this will form the basis for all further discussions and planning of the software implementation

3) it will also form the basis of what exactly needs to be implemented as a minimum requirement in order to have project milestones

4) it will allow the generation of test cases to later test the functionality of the database

5) the IT guys love flowcharts and it will make it much easier for them to design a database which can handle all the processes

These steps should come well ahead before making a decision which kind of DB system or web app environment should be used to implement the solution. All the underlying logic needs to be developed before anyone starts producing code.

However, before starting to develop some custom database and frontend I would also suggest to look at what is currently available and if existing solutions could be customized to your requirements. E.g. http://www.labkey.com/ is a free solution for managing biomedical data (both research & diagnostic) but it is not trivial to set up and customize - on the plus side it might save a lot of work reinventing things that are already out there.

Hope that helps!

My advice, don't get complicated with enterprise level databases requiring programmers, layout designers, webmasters etc. Get FileMaker and develop your own skills and independence, it is not rocket science, it can be learned very easily and for sophisticated designs you can hire consultants. I use it all the time, because of the speed of development, rapid changes, easy reporting and query. It is inexpensive compare to the cost of licensing large DBMS or pay developers. So that is my advice, and don't let anybody discourage you about filemaker, most people just don't know how powerful it is. Large projects usually prototype in filemaker, before making a large commitment to a more expensive SQL project. You will keep all your data if you migrate later. In database developments for research applications, you need the flexibility to think of a new data collection form and have it on-line in hours, not weeks, such as is the case with most SQL platforms.

Hi,

Look at REDCAP: http://project-redcap.org/

The advantage is that you could create your database (or part of it) from the Excel files you have - saves time. Plus they have some tracking tools at place.

Elizabeth,

what do you want to DO with your data? Is this simply a sample-tracking system? Or will you also fill it with the sample results? Your database design/needs will depend on the answers to these questions. If sample tracking, barcoding, and inventory are your only requirements then an open-source LIMS model might work and most of those come with web-client query tools. If you need the capability to analyze data (statistically, graphically, etc) then you are talking about more sophisticated web development and coding along with a database and connector.

If budget is an issue, I shall recommend mySQL, an open-source database available for free from www.mysql.com. Whats more, there is a great online community ready to hold your hands all through the way.

As Sebastian has precisely described, first you'd better analyse what you do, what you have, what you want to do and what you want to have from informatics.

The best database is without doubt Oracle but it's not certainly cheap and simple to manage and to use, usually it requires high resources to work properly and could not always be the best startup solution..

After an accurate analysis, maybe you can start with something you can find working now asking to the community, with a valid quality / price ratio, and maybe you can enhance it in the future, after having acquired specific expertise of you needs and choices.

Hello again, Elizabeth,

Thank you for your clarifications. I think there are a lot of really good comments and suggestions here. We have used the REDCap database resource extensively for research data capture, which is its primary intent, and it has the appeal of being very user friendly, highly customizable, and well supported. Alternatively, for our specimen inventory/data management, system we've gone with a commercially available customizable inventory management package specifically designed for that, which we're also very happy with.

I'd like to add thoughts, based in the premise that your end goal, beyond the teaching (theoretical) experience, is the synthesis of a tool which can then be applied to real world research applications and challenges.

In particular...to Sebastian's VERY insightful discussion, I do have an addition to his guidance.

I think that t is of critical importance, as Sebastian implies, that you exercise a thorough reverse-planning strategy in this project...one which is anchored in how your data (and samples) will be used, and not just how well you organize the information system itself. To successfully provide these useful resources to biologists as research assets, it really is critical to the success of this project that you remove as much of the "mystery" about what your biologists will actually be trying to do with the services you provide, before you begin building. The best-organized data/inventory management system you can conceive will be of limited value as a resource, if you don't have clear understanding in defining its realistic outcomes. I would take a long time talking to the scientists nearby who will actually be your client/consumers, at the practical (not theoretical) back end of this entire specimen management process.

Their needs will be more highly specialized, less likely to be anticipated by someone outside the field, and consistently more stringent, in terms of what they need to know secifically about sample sources, the actual sample treatments, and matching datasets.

Your design should not be limited by what you perceive in this moment to be important, but must be adapted to the specifica technical/application needs of the resources themselves, and responsive to the changing needs of the research field itself.

Best of luck

Larry

I am one of the clients, which thankfully eliminates a middleman. The other clients are the rest of the people working in the lab with me.

I have been doing my best to figure out the end result. I have presented most of the information we are tracking as example reports, giving them an example of the final output we want, because I don't think I would be able to design the tables.

We are trying to track our samples from their arrival through 5+ different assays, and reruns at various points, and pooling or dividing replicates. We want to track the data gathered, and the physical location of the samples. We do not use barcodes. We are also going to try to automate a couple of pieces, and potentially tie in or at least track part of an analysis pipeline that is currently being run in Linux.

Thank you all very much for your thoughtful and helpful advice. It is greatly appreciated.

Hi,

You can use "xampp", which has inbuilt 'mysql' and provides graphical user interface.

Its simple and runs in both windows & linux.

Sorry for the late reply.

Using Castor EDC (https://castoredc.com) you can set up you study exactly as you like. The system is totally flexible so you can make it fit your workflow. You can use reports for repeated measures, so you don't have to define every possible event before hand, but can create forms (reports) dynamically.

Some background: Castor EDC is a GCP compliant system for online data capture, created by researchers, and is free for small studies. For bigger studies it's way cheaper than anything else out there and doesn't require any contracts or consortium deals (Redcap). You can just start building your eCRFs by registering and creating your study. You don't need a server, programming skills etc (OpenClinica), anyone can use the Form Builder.

Disclaimer: Apart from doing my PhD, I'm the founder of Castor EDC.