As a complete newcomer, I would like to practise designing a database based on the biological data my lab has been accumulating over the years. Here is an explanation (I am simplifying/abstracting what we are doing to make this first database simpler):

Each fieldwork season, we would go and measure trees (e.g. height, trunk diametre, etc.) at different geographical locations. Over the years, each tree and location would have been visited and measured multiple times. However, due to practical constraints we may not get to visit and measure all trees at all locations every season.

The purpose of the database are twofold:

(1) Track how each individual tree changed with time, asking questions like: "Did tree A grow taller over the past two years?"

(2) Track how populations of trees at each location changed with time, asking questions like: "Did the group of 12 trees at Acme Acres grow taller in general over the past three years?"

Here is my initial thoughts on what tables to make:

(a) Trees: Fields: TreeID (e.g. "tree A"), Location (linked to table c below).

(b) Visits: This keeps metadata for each visit, with information like the date of visit (for a location, which will be the same for all trees at that location), locations we covered during the visit.

(c) Locations: Basic information on each location, such as latitude, longitude, name of location (e.g. "Acme Acres"), etc.

(c) TreeData: This contains the actual measurements for individual trees. Fields may include: tree (linked to table a), height, diametre, visit (linked to table b). In the end there will be multiple entries any one tree here, with data from each visit.

Does any of this make sense as a first dab at database design? (I've only been thinking about databases for three days)

I would appreciate and humbly accept any pointers you can give me.

Thank you very much!

BTW, One reason I was considering a Visits table was because sometimes I might to compare the data between visits 3 and 4, or between visits 1 and 5, etc

1 Answer 1


I would rename TreeData to measurements.

Also, unless you really need the Visits table, I wouldn't use it. If designed properly your database would look something like this if you included the visits table:

tree db design

Using the visits table would complicate some of the queries that you're wanting answered. For example, to answer number one you might use something like this in sql server:

     max(m.height)-min(m.height) as Growth
from trees t
     inner join measurements m
          on t.treeid=m.treeid
     inner join visits v
          on m.visitid=t.visitid
where v.date>=dateadd(y,-2,getdate())
group by t.treeid

without the visits table you could rewrite that like this:

     max(m.height)-min(m.height) as Growth
from measurements m
where m.date>=dateadd(y,-2,getdate())
group by m.treeid
  • Thanks for the answer! One reason I was considering a Visits table was because sometimes I might to compare the data between visits 3 and 4, or between visits 1 and 5, etc. Can it still be done without a Visits table? Also, what do the m and 1s in the diagram mean? Thanks so much!
    – hpy
    Commented Apr 5, 2012 at 6:01
  • 1
    Those are used to show the kind of relationship between tables. 1 location has many trees, 1 tree has Many treedata Commented Apr 5, 2012 at 6:36
  • @DForck42: If you want to (properly) enforce integrity at the TreeData table, you have to add LocationID as well - and adjust the 2 Foreign Keys to Tree and Visit tables. Commented Apr 5, 2012 at 6:38
  • 1
    @penyuan you would just filter on the dates and compare the results for the first date to the second date.
    – DForck42
    Commented Apr 5, 2012 at 13:19
  • 1
    Integrity. In order to never end with a row in TreeData that has TreeID = 5 and VisitID = 7 where TreeID=5 has LocationID=15 (California) but VisitID=7 has LocationID=27 (Toronto) Commented Apr 5, 2012 at 14:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.