4 Replies Latest reply on Aug 11, 2013 12:42 PM by FentonJones

    Best structure for my database



      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?

           Maybe the structure doesn’t matter and I can choose whatever version I want but I just thought I might ask. So, thank you for any suggestion. smiley

        • 1. Re: Best structure for my database

               It seems you may have overwhelmed us, especially those of us who see birds of one type to look pretty much the same, and know little otherwise :-]


               I would think that you'd have a table for "bird type"; i.e., not the details of tests, just the type of bird, in general. That would then be what the picture, and all the other details tables would be linked to, via either a 'bird type' auto-entered ID (or a known id for that bird type; I don't know). 


               Other than that fact that you may need specific tables for the very different tests, I don't see what exactly what problem you may be wondering/worrying about.


               It is true that when you have many different tests on a bird, and that it is often difficult to decide whether this needs a "table of its own", or just "some different fields mixed together in an existing table".


               [ I think you should create a table, which has only 1 record for each "test". It would have all the data which only had one entry for any test. 


               The critical question is I think, "Do these happen at the same time?" And by time I don't really mean the time, as seconds go by. I mean you've got a bird, you're testing it. Enter this as one date/time (Timestamp), from when you start, the same of that bird then. That way you can see all its tests, today (or twice a day, whatever you consider a "test"). [Also let an auto-enter Timestamp happen, so you can know when each specific thing happened). 


               One big thing to think about is whether you will want to seem them together later, on a single list. Like show "this" and "that" in the same list, sorted by date/time. That's is pretty hard if they're two different tables. It is often not vary hard to keep slightly different things in a table, as long as the share the same parent ID, and the same timestamp (specified by you). You can have different tabs, or entire layouts, which show only the details for each. 


               On the other hand, if some part of the date/time overall test quires multiple tests for one thing, then obviously that would require a new table. Basically, don't do the 1, 2, 3, etc..


               Hopefully there is some assistance and sense hear. I am pretty much retired, partly because of age (63), but mostly because of a stroke 2 1/2 years ago, which makes my thinking and writing a little confused, even to myself. But FileMaker was what I did for many years, and relationships were one thing I have mostly retained.

          • 2. Re: Best structure for my database

                 You were clear, so no worries and thanks for your answer. smiley

                 I was not sure how much I should go into details. I have three "tests" as you refer to it: 1 for females, 3 for males. They all happen on the same date I capture the bird but not on the same time as the Manipulation (which includes the measuring) and the tests happen one after another. But as I said above sometimes it's not possible to do all the tests and when I after some time capture the same bird again I will only do the tests which happened longer then a month ago but I will measure the bird each time I captured it.

                 I will give an example of the tables:

            Birds table                               Manipulation table                                                                                          Test table (They are all the same)

                                                                   Id                                                                                                                           Id

                 Id                                               BirdsIdFk (linked via Id Birds)                                                                         BirdsIdFk (linked via Id Birds)

                 RingNumber                           RingNumber                                                                                                      RingNumber

                 Colourcode                             ColourCode                                                                                                       ColourCode

                 Sex                                            Sex                                                                                                                       Sex

                 Observer                                  Observer                                                                                                             Observer

                 FirstCapture                            CaptureDate                                                                                                      TestTakingDate

                 Status                                       StartTime (start of the whole process, bird is in my hands)                    StartTime

                 Dead                                         Recapture (Yes or No)                                                                                     Endtime

                                                                    ReRinged (Yes or No)                                                                                     Observer in the lab (who is analysing it)

                                                                    Test1 (Yes or No)                                                                                              Processingdate in the lab (when is it analysed)

                                                                    Test2 (Yes or No)                                                                                              Comments

                                                                    Test3 (Yes or No)





                                                                    ReleaseTime (when I let the bird go, meaning after the measures and the tests)


                 I'm worrying mostly about the clarity of the database and the scripts I want to implement. I was not sure if the structure of the database would influence the simplicity/difficulty to implement it.

            • 3. Re: Best structure for my database

                   One thing to keep in mind: Your database structure is not chiseled in stone. It's normal to devise a database, start working with the actual data in actual real world solutions and then find you need to go back and rethink-revise aspects of your design to improve on how it functions. FileMaker makes this fairly easy to do and techniques such as the Convert to Seperation Model can make the needed updates a bit easier to manage.

              • 4. Re: Best structure for my database


                     First, as I said before, I have some problems understanding all the details of what others write. In general, it is sometimes difficult for a "database" kind of person to communicate with a "business" or "science" person. We have some words in common; but we look at the database from a different point of view. I (in particular) look from the top down, and see the basics first. Yes, I still want to know the details (mostly to see if they stand out in an unusual way); but I'm first concerned with what tables are required, how the IDs will be entered, and what the connections will be.


                     In this case, I see the three tables you sent in your last post: Birds, Manipulation, and Test. Those look fine. In the fields, what I don't see clearly are what the IDs are, exactly. In Manipulation and Test I see "Id". Are those the local auto-entered Id for ITS table? That is fine, but each needs an Id from its "parent" table. Manipulation would need an "Id_bird" field. Test would need an "Id_manip" (shortened that long word); IF it is a child of Manipulation (which it seems to be). 


                     [ You do necessarily need to copy so much data from one table to another. Yes, Id for sure, many a few more; but not many, for several reasons. For example, if "Observer" is only a Bird's field, they you can see if from any child table's layout (or even enter, if wanted).]


                     What I see as confusing, and likely do not agree with. "Test1", "Test2", Test3". I don't see why you have these at all in Manipulation. If the Test table is a child of Manipulation, then a record in Test, with the Id_manip of Manipulation, will BE the test. If there's been 3 created, then you'll see 3 in a portal in their Manipulation table's record layout. If you only need 1 test, you'll only see 1. 


                     The multiple "Measure" fields. This I don't really know. But I would not want "1, 2, 3, etc.". If they are separate from "test" records, then the could be another table. Unless you specifically want to always see all 4 (empty or not; though a button-script could create all 4 in a child table also). One thing to consider re: 1, 2, 3, etc. fields; they make for poor Finds, or other uses.


                     You have some other ideas, portals, scripts, conditional formatting, etc.. Yes, good ideas. But I kind of think of those after I get the basics in place. Because the basics do matter; or else you have to later move the data around to fix it; which is harder than getting it right to start with (though possible).


                     The "Pictures". How big of pictures? If big then this is one thing I even consider creating in another file even. It depends on whether you're talking about whether this all goes on a Server, etc.. But in any case, large pictures will add a lot of size to a database file. They are not like data, and do not need to be saved as often, etc.. Of course, another file means different Security settings, etc.. Kind of a separate question.