db design to calculate overlap in group membership in time

E

EHobs

Hi all,

I am a biologist studying the social structure of birds. I am currently
brainstorming ideas for constructing a database that will allow me to easily
calculate the number of seconds that one bird is in the same group as another
bird. Because group membership changes frequently with birds arriving and
departing at different times, and often arriving, departing, and re-arriving
on the scale of seconds, I will have many thousands of records. I am trying
to determine how to best set up this database before entering all the data. I
have 2 goals with this database:

1) I need to find a way that I can streamline data entry. I was thinking 3
columns: ID, time of movement, and whether it is an arrival or departure.
Because of the frequent movements and the large number of potential
individuals, I need to be able to enter arrivals and departures separately.

2) I need to determine how I am going to calculate the amount of time that
any 2 birds at present at the same time. This needs to be able to account for
individuals that arrive and depart at different times, and calculate just
"shared" time.

I think this problem may be somewhat similar to company records of employees
hired and fired at different dates, where the amount of time of overlap in
employment is calculated.

Any help or advice would be greatly appreciated!

Thanks,

Liz
 
J

Jeff Boyce

Liz

If you haven't built a database/application before, and particularly an
Access (relational) database/application, there are (at least) four separate
learning curves you'll want to consider. Maybe you have already worked your
way up some of them...

1. relational database design - if "normalization" isn't familiar, you
need to understand it first
2. Access tips/tricks - Access handles things differently than Excel or
....
3. graphical user interface design - you do NOT use the tables in Access
for data entry ... that's what the forms are for
4. application development - if you've never built a ... "house", where
do you start?!

My suggestion is that you put aside some of your "how" questions and focus
first on "what". What are the things about which you wish to store (and
retrieve) information? These are your "entities". Now, what pieces of
information do you want to store about each entity? For example, a person's
name and date of birth "belong" to an entity about persons, but do NOT
belong to an entity about jobs...

Here's a rough idea of how your tables (!entities!) might look if you were
building a student registration database (untested, simplistic, for example
purposes only):

tblPerson
PersonID
FName
LName
DOB

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
PersonID
ClassID
EnrollmentDate

What does your data look like?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
S

Steve

Hi Liz,

I think that the first thing is to answer two questions ...
1. "....one bird is in the same group as another bird" implies that you
can identify individual birds. Can you do that and what are the
characteristics (fields) that can be used to uniquely identify a bird?

2. What factors (fields) distinguishes one group from another. For
example, Bird A and Bird B are together; is that a group? At another spot,
Bird C and Bird D are together; is that a group? After a few moments, Bird C
and Bird D join Bird A and Bird B. There are now four birds together, is
that group AB, Group CD or a new group.

Steve
(e-mail address removed)
 
E

EHobs via AccessMonster.com

Hi Steve,

Thanks for your response. I can identify individual birds by a unique color
combination. The field is a 3 letter code corresponding to the 3 colors used
to id the bird (ex: bbb is the bird with the blue-blue-blue color combo). I
have observations of individuals at one specific location, so any birds
observed at that location at the same time are defined as associated. I need
to be able to query the db for both group composition at any one time as well
as overall shared association time among any 2 individuals. I have not yet
entered the data -- I am trying to figure out the best format first to avoid
headaches later. I am currently envisioning a table like this (below), but am
very open to suggestions!

Date Time ID Type (a=arrival, d=departure)
12-Jun 9:01 bbb a
12-Jun 9:02 bbb d
12-Jun 9:02 rrr a
12-Jun 9:02 brr a
12-Jun 9:03 rrr d

I would like to run a query that will calculate the amount of time that every
bird shared with every other bird (ex from above: bbb shared 0 with rrr, brr;
rrr shared 1 with brr; etc). I can have the time of arrival coded in time of
day or in number of seconds from the start of observation, which may make
calculations simpler.

In response to Jeff Boyce's response, I have experience constructing
relational databases and queries. I am self-taught, so they usually aren't
pretty, but I can usually get things to work. This question with this db that
I have is beyond what I have tried to do before. Again, any suggestions would
be very appreciated.

Thanks,

Liz
Hi Liz,

I think that the first thing is to answer two questions ...
1. "....one bird is in the same group as another bird" implies that you
can identify individual birds. Can you do that and what are the
characteristics (fields) that can be used to uniquely identify a bird?

2. What factors (fields) distinguishes one group from another. For
example, Bird A and Bird B are together; is that a group? At another spot,
Bird C and Bird D are together; is that a group? After a few moments, Bird C
and Bird D join Bird A and Bird B. There are now four birds together, is
that group AB, Group CD or a new group.

Steve
(e-mail address removed)
[quoted text clipped - 35 lines]
 
J

Jeff Boyce

The example you provided may not have quite all the information it sounds
like you need.

Steve asked how you "group" the members/birds. In your example, are all of
those birds part of the same group? If so, how do you know/tell Access that
fact?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

EHobs via AccessMonster.com said:
Hi Steve,

Thanks for your response. I can identify individual birds by a unique
color
combination. The field is a 3 letter code corresponding to the 3 colors
used
to id the bird (ex: bbb is the bird with the blue-blue-blue color combo).
I
have observations of individuals at one specific location, so any birds
observed at that location at the same time are defined as associated. I
need
to be able to query the db for both group composition at any one time as
well
as overall shared association time among any 2 individuals. I have not yet
entered the data -- I am trying to figure out the best format first to
avoid
headaches later. I am currently envisioning a table like this (below), but
am
very open to suggestions!

Date Time ID Type (a=arrival, d=departure)
12-Jun 9:01 bbb a
12-Jun 9:02 bbb d
12-Jun 9:02 rrr a
12-Jun 9:02 brr a
12-Jun 9:03 rrr d

I would like to run a query that will calculate the amount of time that
every
bird shared with every other bird (ex from above: bbb shared 0 with rrr,
brr;
rrr shared 1 with brr; etc). I can have the time of arrival coded in time
of
day or in number of seconds from the start of observation, which may make
calculations simpler.

In response to Jeff Boyce's response, I have experience constructing
relational databases and queries. I am self-taught, so they usually aren't
pretty, but I can usually get things to work. This question with this db
that
I have is beyond what I have tried to do before. Again, any suggestions
would
be very appreciated.

Thanks,

Liz
Hi Liz,

I think that the first thing is to answer two questions ...
1. "....one bird is in the same group as another bird" implies that you
can identify individual birds. Can you do that and what are the
characteristics (fields) that can be used to uniquely identify a bird?

2. What factors (fields) distinguishes one group from another. For
example, Bird A and Bird B are together; is that a group? At another spot,
Bird C and Bird D are together; is that a group? After a few moments, Bird
C
and Bird D join Bird A and Bird B. There are now four birds together, is
that group AB, Group CD or a new group.

Steve
(e-mail address removed)
[quoted text clipped - 35 lines]
 
S

Steve

Hi Liz,

Give me your comments please to the following tables ....

TblBirdType
BirdTypeID
BirdType (Robin, Sparrow, Dove, etc)

TblColor
ColorID
Color (Black, Red, B;ue, etc)

TblBird
BirdID
BirdTypeID
ColorID
<other fields to uniquely identify a bird>

TblLocation
LocationID
LocationDescription
<other fields to iniquely identify a location>

TblGroup
GroupID
LocationID
DateGroupObserved

TblGroupComposition
GroupCompositionID
GroupID
BirdID
TimeBirdObserved
ArrivalDeparture

Steve
(e-mail address removed)


EHobs via AccessMonster.com said:
Hi Steve,

Thanks for your response. I can identify individual birds by a unique
color
combination. The field is a 3 letter code corresponding to the 3 colors
used
to id the bird (ex: bbb is the bird with the blue-blue-blue color combo).
I
have observations of individuals at one specific location, so any birds
observed at that location at the same time are defined as associated. I
need
to be able to query the db for both group composition at any one time as
well
as overall shared association time among any 2 individuals. I have not yet
entered the data -- I am trying to figure out the best format first to
avoid
headaches later. I am currently envisioning a table like this (below), but
am
very open to suggestions!

Date Time ID Type (a=arrival, d=departure)
12-Jun 9:01 bbb a
12-Jun 9:02 bbb d
12-Jun 9:02 rrr a
12-Jun 9:02 brr a
12-Jun 9:03 rrr d

I would like to run a query that will calculate the amount of time that
every
bird shared with every other bird (ex from above: bbb shared 0 with rrr,
brr;
rrr shared 1 with brr; etc). I can have the time of arrival coded in time
of
day or in number of seconds from the start of observation, which may make
calculations simpler.

In response to Jeff Boyce's response, I have experience constructing
relational databases and queries. I am self-taught, so they usually aren't
pretty, but I can usually get things to work. This question with this db
that
I have is beyond what I have tried to do before. Again, any suggestions
would
be very appreciated.

Thanks,

Liz
Hi Liz,

I think that the first thing is to answer two questions ...
1. "....one bird is in the same group as another bird" implies that you
can identify individual birds. Can you do that and what are the
characteristics (fields) that can be used to uniquely identify a bird?

2. What factors (fields) distinguishes one group from another. For
example, Bird A and Bird B are together; is that a group? At another spot,
Bird C and Bird D are together; is that a group? After a few moments, Bird
C
and Bird D join Bird A and Bird B. There are now four birds together, is
that group AB, Group CD or a new group.

Steve
(e-mail address removed)
[quoted text clipped - 35 lines]
 
E

EHobs via AccessMonster.com

Hi Steve & Jeff,

Thanks for your comments. I was actually planning something much simpler than
the several tables Steve posted - I am working with only one type of bird at
one specific location. I was thinking of having just a single table that I
can query. I could put the data in the following format

Date BirdID ArrivalTime DepartureTime

Any ideas on how to structure a query that will allow me to:

1) determine the total shared amount of time present for any 2 individuals
2) determine all of the individuals present at any one time point

Thanks again,

Liz
Hi Liz,

Give me your comments please to the following tables ....

TblBirdType
BirdTypeID
BirdType (Robin, Sparrow, Dove, etc)

TblColor
ColorID
Color (Black, Red, B;ue, etc)

TblBird
BirdID
BirdTypeID
ColorID
<other fields to uniquely identify a bird>

TblLocation
LocationID
LocationDescription
<other fields to iniquely identify a location>

TblGroup
GroupID
LocationID
DateGroupObserved

TblGroupComposition
GroupCompositionID
GroupID
BirdID
TimeBirdObserved
ArrivalDeparture

Steve
(e-mail address removed)
Hi Steve,
[quoted text clipped - 66 lines]
 
J

Jeff Boyce

You posted your question in a newsgroup that supports the use of Microsoft
Access. MS Access is a relational database.

It all starts with the data.

It sounds like you want to use a spreadsheet instead (all the data in one
table). Have you looked into that option?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

EHobs via AccessMonster.com said:
Hi Steve & Jeff,

Thanks for your comments. I was actually planning something much simpler
than
the several tables Steve posted - I am working with only one type of bird
at
one specific location. I was thinking of having just a single table that I
can query. I could put the data in the following format

Date BirdID ArrivalTime DepartureTime

Any ideas on how to structure a query that will allow me to:

1) determine the total shared amount of time present for any 2 individuals
2) determine all of the individuals present at any one time point

Thanks again,

Liz
Hi Liz,

Give me your comments please to the following tables ....

TblBirdType
BirdTypeID
BirdType (Robin, Sparrow, Dove, etc)

TblColor
ColorID
Color (Black, Red, B;ue, etc)

TblBird
BirdID
BirdTypeID
ColorID
<other fields to uniquely identify a bird>

TblLocation
LocationID
LocationDescription
<other fields to iniquely identify a location>

TblGroup
GroupID
LocationID
DateGroupObserved

TblGroupComposition
GroupCompositionID
GroupID
BirdID
TimeBirdObserved
ArrivalDeparture

Steve
(e-mail address removed)
Hi Steve,
[quoted text clipped - 66 lines]
 
E

EHobs via AccessMonster.com

Hi Jeff,

Yes, I have looked into spread sheets, but I have settled on access because I
am more comfortable with writing queries in that program and am estimating
that I should have around 80,000 data points, which is more than Excel can
handle. Thank you for your comments and your time. I will try posting under
queries, as it is clear that my question really doesn't fit here.

Thanks again,

Liz


Jeff said:
You posted your question in a newsgroup that supports the use of Microsoft
Access. MS Access is a relational database.

It all starts with the data.

It sounds like you want to use a spreadsheet instead (all the data in one
table). Have you looked into that option?

Regards

Jeff Boyce
Microsoft Access MVP
Hi Steve & Jeff,
[quoted text clipped - 59 lines]
 
S

Steve

Hi Liz,

You ought to reconsider the tables I suggested. Multiple tables DOES NOT
make querying harder, In fact, multiple tab;es make querying easier. Data
entry will be easier and more accurate too!

Steve
(e-mail address removed)


EHobs via AccessMonster.com said:
Hi Steve & Jeff,

Thanks for your comments. I was actually planning something much simpler
than
the several tables Steve posted - I am working with only one type of bird
at
one specific location. I was thinking of having just a single table that I
can query. I could put the data in the following format

Date BirdID ArrivalTime DepartureTime

Any ideas on how to structure a query that will allow me to:

1) determine the total shared amount of time present for any 2 individuals
2) determine all of the individuals present at any one time point

Thanks again,

Liz
Hi Liz,

Give me your comments please to the following tables ....

TblBirdType
BirdTypeID
BirdType (Robin, Sparrow, Dove, etc)

TblColor
ColorID
Color (Black, Red, B;ue, etc)

TblBird
BirdID
BirdTypeID
ColorID
<other fields to uniquely identify a bird>

TblLocation
LocationID
LocationDescription
<other fields to iniquely identify a location>

TblGroup
GroupID
LocationID
DateGroupObserved

TblGroupComposition
GroupCompositionID
GroupID
BirdID
TimeBirdObserved
ArrivalDeparture

Steve
(e-mail address removed)
Hi Steve,
[quoted text clipped - 66 lines]
 
K

KenSheridan via AccessMonster.com

Liz:

First you need to establish a way of determining the shared time where two
birds are concurrently present. I suspect this could be done with a
mathematical expression, but I can't put my finger on just what this would be,
but it's pretty easy to write a function to do it and return the shared time
in seconds:

Public Function TimeShared(ArrivalA As Date, _
DepartureA As Date, _
ArrivalB As Date, _
DepartureB As Date) As Long

Const SECONDSINDAY = 86400

Dim dtmOuterRangeStart As Date
Dim dtmOuterRangeEnd As Date
Dim dtmInnerRangeStart As Date
Dim dtmInnerRangeEnd As Date
Dim dblTimeShared As Double

' determine limits of inner and outer ranges
If ArrivalA < ArrivalB Then
dtmOuterRangeStart = ArrivalA
dtmInnerRangeStart = ArrivalB
Else
dtmOuterRangeStart = ArrivalB
dtmInnerRangeStart = ArrivalA
End If

If DepartureA > DepartureB Then
dtmOuterRangeEnd = DepartureA
dtmInnerRangeEnd = DepartureB
Else
dtmOuterRangeEnd = DepartureB
dtmInnerRangeEnd = DepartureA
End If

' shared time = length of outer range less times from
' end of inner range and end of outer range and
' start of outer range and start of inner range
dblTimeShared = (dtmOuterRangeEnd - dtmOuterRangeStart) - _
(dtmOuterRangeEnd - dtmInnerRangeEnd) - _
(dtmInnerRangeStart - dtmOuterRangeStart)

' covert value to seconds
TimeShared = dblTimeShared * SECONDSINDAY

End Function

Add the function to a standard module, then you should be able to call it in
query. Off the top of my head and untested try this:

SELECT A1.BirdID As Bird1, A2.BirdID AS Bird2,
SUM(TIMESHARED(A1.ArrivalTime, A1.DepartureTime,
A2.ArrivalTime, A2.DepartureTime)) AS TotalSharedTime
FROM Arrivals As A1, Arrivals AS A2
WHERE A1.ArrivalTime <= A2.DepartureTime
AND A1.DepartureTime >= A2.ArrivalTime
GROUP BY A1.BirdID, A2.BirdID;

and for the second:

PARAMETERS [Enter time:] DATETIME;
SELECT *
FROM Arrivals
WHERE [Enter time:] BETWEEN
ArrivalTime AND DepartureTime;

The first should return the total length of time in seconds for all pairs of
birds who are concurrently present. Note that it will return each pair twice
with the birds transposed in each case. The second should return all birds
who are present at the time entered at the parameter prompt. You could if
you wish include parameters in the first query on A1.BirdID and A2.BirdID to
restrict the result to one particular pair of birds. If your table includes
data over more than one day you must of course include both the date and time
in the ArrivalTime and DepartureTime values.

As regards the design of the database as a whole your table should reference
another table of Birds, with one row per bird and a primary key column of
BirdID. A relationship between the tables should be created and referential
integrity enforced to ensure that only valid BirdID values can be entered in
the Arrivals table. Should you need to extend the database to cover more
than one location then you'd need to introduce a Locations table and include
a LocationID foreign key in the Arrivals table. This could then be included
in the queries to differentiate between locations.

Ken Sheridan
Stafford, England
Hi Steve & Jeff,

Thanks for your comments. I was actually planning something much simpler than
the several tables Steve posted - I am working with only one type of bird at
one specific location. I was thinking of having just a single table that I
can query. I could put the data in the following format

Date BirdID ArrivalTime DepartureTime

Any ideas on how to structure a query that will allow me to:

1) determine the total shared amount of time present for any 2 individuals
2) determine all of the individuals present at any one time point

Thanks again,

Liz
[quoted text clipped - 39 lines]
 
J

Jeff Boyce

Liz

What I've been trying to point out (apparently unsuccessfully) is that "how"
depends on "what". The "what" in this case is your data and table
structure. If you elect to store all your data in a single table, you might
as well be using a spreadsheet, because you and Access will have to work
overtime to come up with work-around "hows".

If you use a well-normalized data structure instead, most of what you're
trying to do is relatively simple.

This probably comes down to a "pay now or pay later" situation. If you
decide not to normalize your data, you will be paying (over and over again)
to build your work-arounds.

Best of luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

EHobs via AccessMonster.com said:
Hi Jeff,

Yes, I have looked into spread sheets, but I have settled on access
because I
am more comfortable with writing queries in that program and am estimating
that I should have around 80,000 data points, which is more than Excel can
handle. Thank you for your comments and your time. I will try posting
under
queries, as it is clear that my question really doesn't fit here.

Thanks again,

Liz


Jeff said:
You posted your question in a newsgroup that supports the use of Microsoft
Access. MS Access is a relational database.

It all starts with the data.

It sounds like you want to use a spreadsheet instead (all the data in one
table). Have you looked into that option?

Regards

Jeff Boyce
Microsoft Access MVP
Hi Steve & Jeff,
[quoted text clipped - 59 lines]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top