I am currently working on designing phase for an Array Module. We are planning to track the Array information and store it's content to database. Currently I am finalizing the data model and I have following question.
--We need to track the Geneology of the Sample through out the whole system (Especially the results of Transfers). Is it better to have all that relationship in one flat table with columns for location (like PlateWell, GelLane ..) and columns for Content (Column like . LocationType LocationId ContentType ContentId) or is it better to store all the content of location as a ArrayType? Or is there any other option available in the Market?
In the first case database rows for that table will increate very very fast plus to find out the geneology of the location in Array , I have to use Connect By Prior which will take good amount of time(Is there any other option available?). I dont have much idea about second option but I think retrieval individual content is going to be a problem.