I have an interesting issue that I am working through and was hoping to get some insight from the group. I will do my best to explain the scenario and hope I do not confuse anyone.
We are storing data regarding some analyses that are being run here. We also have a set of tables in which we store information "about" the types of analyses being run. I am using these tables to do a validity check on what file types (or input files) are acceptable for a certain type of analyses.
I have the following tables:
An ANALYSIS table which merely holds the name and some specifics about the type of analysis.
A FILETYPES table which stores information about the types of input and output files for each analysis. We have chosen to use file extensions as a way to identify a file type.
Some analyses can have multiple input and/or output files so I have linking tables between the ANALYSIS and FILETYPES table.
My question is due to a recent development. Apparently when an analysis is run the individual can specify what type of file the output can be. Since these tables are holding the data regarding the "form" of the analysis I must somehow show that each analysis can result in various filetypes. I thought I had done this and had an "extension" field in the FILETYPES table. Now I am being told that each of these input or output files can have more than one file extension.
My solution was to add another table to hold the extensions. I think this is better than allowing extensions to concatenate within a field in the filetypes table which was suggested by a programmer here. Her intention was to parse the field. I prefer a more "appropriate" solution, but I am not convinced mine is the best solution since there will be some repeating data in the extensions table. Various analyses can be associated with the same file types so the extension field in the extensions table will have repeating values.
I have attached a small schema with these few tables to assist with your understanding our issue.
I would appreciate suggestions.
Thank you,
Jodi Kanter
_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu
