Best structure for my database
I’m still constructing my database but have already set up most tables and fields. So this is a question about how to best structure my database. Since at the moment I can easily change things I would like to ask your opinion. I don’t want to run into a problem when I have already entered a lot of my data. ;)
I’m a biologist and working with birds. I catch birds and doing manipulations on them, this includes taking samples (several tables), taking a picture (one table, only males) and measuring certain body measurements (one table). Body measurements are measured every time I capture a bird but samples and pictures are only needed about every month. So, when I capture a bird and enter a new manipulation I would like the database to tell me, when the last time I took a sample or a picture was more than one month ago. (Samples and picture are not necessarily taken always the same date for one bird. It might be the case that I only need to take a picture but no samples depending on the specific time elapsed.) I’m telling this because I thought it might influence how I set up my database, making it more or less difficult to implement this. Or maybe I’m wrong and it doesn’t matter anyway.
What I will have for sure is a “birds” table with information that always stays true, like “Ring number”,” sex”, “first time of capture”, “dead” or “alive”. As all birds get 3 colour rings for identification I have a “Colour Ring” table too. There is a script which generates unique colour-combinations, see this thread: generate alphanumeric serial number and letter code in a specific order
I thought about a script button in the “birds” table which generates a new manipulation (in the manipulation table) with the same fields as in the birds table plus some others like “Observer”, “date” (Creation Date field), “Start time” and “End time". In this table there are portals of tables “sample”, “picture” and “body measures” and sorted descending by date of taking (Creation Date field). I thought about conditional formatting which highlights the portals if the date of the “First row” plus 1 Month is smaller than the “Current date” of the Manipulation.
But the portal shows me e.g. all pictures ever taken, which is in a manipulation table of only a certain date. That might be confusing.
The other solution is only one big table but I think having only one table with the different “samples”, “picture” and “body measures” as fields is getting just too crowded and confusing.
The third option I was thinking is a manipulation table with the “sample”, “picture” and “body measures” just as checkbox fields and an overview table (e.g. a profile table) where I have them as portals again including now a portal for the manipulation table. If it’s possible a conditional formatting which highlights the checkbox that I need to take (e.g. picture) and when I check this box it would be nice to automatically open up a new entry in the corresponding table.
Form these tables I would also like to generate a well-arranged list. And what happens to the portals in the list or table view mode?