Sign in to follow this  
Followers 0

Recording and Maintaining Relative Dating Among Records

25 posts in this topic

Posted

Hello,

I am creating a database of properties in a city. I want to organize known changes over time, (physical changes as well as type of use, business names, addresses, etc.). Just about the only constant over time will be a rough GPS location.

In many cases, I cannot ascertain with certainty an exact date of physical changes or the duration of a business occupation. However, for the most part I know the relative chronology.

How can I maintain a known chronology among records, yet, as my research provides new information, be able to add new data within the chronology? For example, let's say I know of 4 businesses that occupied a building between 1950 and 2000 and they are in the databse in a way that they can be sorted chronologically. Let's say I later find out there were two other businesses that occupied the same building for short periods within the same period. I would want to be able to add them to the database without disturbing the chronological relationships already established.

Can anyone recommend some approaches to this system?

Thanks in advance!

0

Share this post


Link to post
Share on other sites

Posted

For example, let's say I know of 4 businesses that occupied a building between 1950 and 2000 and they are in the databse in a way that they can be sorted chronologically.

I assume this would mean two date fields, to cover a date range.

Let's say I later find out there were two other businesses that occupied the same building for short periods within the same period. I would want to be able to add them to the database without disturbing the chronological relationships already established.

Just add the appropriate date range, and it would be show up where is should in chronological order.

In many cases, I cannot ascertain with certainty an exact date of physical changes or the duration of a business occupation. However, for the most part I know the relative chronology.

As mentioned above, I assume this information will be stored via a date range in two date fields. Although; It is quite possible that a single date would work - the starting date. If the date is approximate, you could enter the date as the first day/month/year/century, based on how accurate your information is. If you sometimes have exact dates, and want to be able to differentiate them from approximate dates, I would recommend a boolean field: isDateApproximate, who's value would contain a 1 if the date is approximate.

0

Share this post


Link to post
Share on other sites

Posted

Your third answer gets to my issue. Most dates I have are approximate. Much dating is determined from historic photos. I may be able to approximate the date a photo was taken but it doesn't tell me duration of that business. That's why I'd like to avoid making specific dates too important to the table since their accuracy will be questionable.

0

Share this post


Link to post
Share on other sites

Posted (edited)

I don't understand your question. What exactly do you mean by:

they are in the databse in a way that they can be sorted chronologically.

This part is also not clear:

Just about the only constant over time will be a rough GPS location.

Isn't "property" the same thing as "real estate" - i.e. defined by land registry? I can understand you might have a business related to more than one property - but I don't think this should be based on coordinates...

---

See also:

http://fmforums.com/forum/topic/77001-unknown-dates/page__view__findpost__p__361765

Edited by comment
0

Share this post


Link to post
Share on other sites

Posted

I don't understand your question. What exactly do you mean by:

This part is also not clear:

Isn't "property" the same thing as "real estate" - i.e. defined by land registry? I can understand you might have a business related to more than one property - but I don't think this should be based on coordinates...

In the first statement, I mean that I imagine a database where records can be sorted chronologically despite very approximate dates. Someone might tell me, "well, when I was a boy I remember xyz business being there." If I know what business is in the building now, I can enter in "xyz business" as being earlier, but unless or until I have better date information, I'd like to avoid entering it.

Isn't "property" the same thing as "real estate" - i.e. defined by land registry? I can understand you might have a business related to more than one property - but I don't think this should be based on coordinates...

Over the large span of time I'm tracking (back to the 1800s) properties have been split and combined, address numbers have changed, even street names have changed or been rerouted. The only constant I can imagine is geographic location.

0

Share this post


Link to post
Share on other sites

Posted

So what you're saying is that you might have 4 records (related to the same property) that are also related to each other, in order to establish their chronological order relative to each other?

0

Share this post


Link to post
Share on other sites

Posted

If I understand your response...yes.

0

Share this post


Link to post
Share on other sites

Posted

I'll have to give this some additional thought, but it looks like you need to have three tables. For lack of better names, I'll call them Places, Events, and Precedences. The first two should be obvious. The Precedences table is a join table between Events and itself, in the form of:

Event X precedes Event Y

This should at least take care of the data you receive, in the form you receive it. What remains to be seen is (a) how to flag a conflict between various precedence entries, and ( B) how to use the precedences to establish some form of sorting order for the events.

0

Share this post


Link to post
Share on other sites

Posted

Thanks for your input.

0

Share this post


Link to post
Share on other sites

Posted

I'm trying to think of how to convert something I can imagine graphically into a format I can put in a database.

For instance, say I have a piece of chain, made up of 4 individual links, each painted a different color. The first link in the chain is red, the next blue, the third link is green and the fourth is yellow. I have a rule that says I must maintain those relative positions, even if I add or remove links from the chain. (So, I can create a longer chain of red, purple, blue, green, yellow, orange, or a shorter chain of red, blue, yellow.) Furthermore, any new links, once added, have to preserve their relative order to all other links.

Is this possible to translate into the sorting of records in Filemaker?

Comment, please forgive me if I've restated what you've already described with your "Precedences" table above.

0

Share this post


Link to post
Share on other sites

Posted

If each link in your chain was a date, and in sequential order (red is older than blue which is older than green which is older than yellow), then all you have to do is sort by date, and you have what you want.

0

Share this post


Link to post
Share on other sites

Posted

If each link in your chain was a date, and in sequential order (red is older than blue which is older than green which is older than yellow), then all you have to do is sort by date, and you have what you want.

Thanks for the response, but as I tried to make clear in the first post, the lack of accurate data prevents me from assigning complete dates to each record.

0

Share this post


Link to post
Share on other sites

Posted

The first link in the chain is red, the next blue, the third link is green and the fourth is yellow.

...

any new links, once added, have to preserve their relative order to all other links.

It's not quite the same thing - but note that your rule does not state where the correct position of purple is. Starting with a chain of {red, blue, green, yellow}, any one of these will "preserve their relative order to all other links":

{purple, red, blue, green, yellow}

{red, purple, blue, green, yellow}

{red, blue, purple, green, yellow}

{red, blue, green, purple, yellow}

{red, blue, green, yellow, purple}

1

Share this post


Link to post
Share on other sites

Posted

Thanks for the response, but as I tried to make clear in the first post, the lack of accurate data prevents me from assigning complete dates to each record.

I think I finally understand your problem...

How about a custom sort order? That way, when you add 'purple', you manually define where it goes.

comment, do you have any links to custom sort techniques? I thought I remember seeing one created by you.

0

Share this post


Link to post
Share on other sites

Posted

comment, do you have any links to custom sort techniques? I thought I remember seeing one created by you.

I don't think we are there yet. That is, I don't see how would you "manually define where it goes". How do you know where it's supposed to go?

0

Share this post


Link to post
Share on other sites

Posted

This may or may not be a concern for your particular needs, but it's bugging me that it hasn't been brought up yet: How do you want to handle elements that have an order relative to the rest of the data, but not relative to each other? For example, suppose we know that green and yellow both come after red and before blue, but the relative order of green and yellow is unknown?

( red, { green, yellow }, blue )

The data modeling for this can be accommodated by existing suggestions, but how would you handle this in a UI for manipulating the chronology as a whole (as opposed to each element's lists of before and after events)?

0

Share this post


Link to post
Share on other sites

Posted

IMHO, when the relative order of events is unknown, they are presumed to be concurrent. This is somewhat similar to a genealogical solution, where each person belongs to a "generation". In your example, red is a "grandfather" and blue is a "child". Anything in between is in the realm of "parent", so that when sorting "chronologically", both {red, green, yellow, blue} and {red, yellow, green, blue} are equally correct.

A more difficult case can be brought up: consider the events A, B, C, D, E and X with the following relationships:

A < B

B < C

C < D

D < E

A < X

X < E

so that:


A < B < C < D < E

A   <   X   <   E

Now, which "generation" does X belong to? Obviously, it cannot be contemporary with B and C and D, and an arbitrary choice must be made.

0

Share this post


Link to post
Share on other sites

Posted

A presumption of concurrency makes sense for many cases, but it may not for the example here of ordering which business operated at a given location when. Whether representing unknown or concurrent order, it's still tough to represent the data of a directed graph when our primary UI tools for working with multiple records are 1-dimensional lists (list view, portals). In my example, we could choose to sub-sort on some other property, like name. In yours, we could arbitrarily put X at the beginning or end of the possible range. Sorting is easy enough, but the tough part, as I see it, is figuring out what cues to provide to indicate, "this is the order I'm using for display right now for lack of better information, but some other order may turn out to be more historically accurate," and doing so in a way that suggests what other orderings are reasonable based on existing information.

0

Share this post


Link to post
Share on other sites

Posted

I think in your example the presentation would be:

Period #1

• red

Period #2

• green

• yellow

Period #3

• blue

As I said, my example is more difficult and I am not sure what cue could be provided to indicate the difference between B and X, assuming they are grouped together.

0

Share this post


Link to post
Share on other sites

Posted

For your more difficult example, there will have to be a representation of nested groupings for ( A, { X, ( B, C, D ) }, E ). Perhaps something like a folder tree structure with different headings and delimiters for subsets that are ordered vs. not:

1. A

2. (unordered group)

• X

• (ordered group)

1. B

2. C

3. D

3. E

0

Share this post


Link to post
Share on other sites

Posted

Possibly. I see a lot of room for personal preference here. If it were up to me, I wouldn't worry too much about the exact order of a list (which as you noted, cannot be truly representative anyway). Instead, I would export the relationships as a dot file and use a graphic application to turn it into a directed graph.

0

Share this post


Link to post
Share on other sites

Posted

As you said comment, there is lots of room for personal preference. There is also the question of whether the juice is worth the squeeze. I've been waiting to hear from Shrubman for these reasons.

I'm mostly thinking out loud here, but would a hierarchical portal technique work in this scenario? It might not even need to be displayed hierarchically, but that same parent/child data may be able to be used to display the data sorted properly in a portal. I'm also wondering if that structure could be modified to allow for multiple parents; and if that would help solve any of the problems here.

0

Share this post


Link to post
Share on other sites

Posted

would a hierarchical portal technique work in this scenario?

I don't know. I don't see it myself, but ...

Anyway, I don't think display is the issue here. At least I hope not, because Filemaker isn't really equipped to display this type of information in a meaningful manner. In fact, I am not even sure a relational database is the best tool to store such data. The model discussed so far is over-simplified; in real life you are also bound to have testimonies in other forms, e.g:

• Event X is concurrent to Event Y;

• Event Z took place circa 1940.

and of course some of the testimonies will contradict each other. I don't know of a box that would ingest all of that and spit out a coherent "map" of the known facts - and I am not sure Filemaker can be used to build such a box.

In any case, this has to be the most interesting question in a long time.

0

Share this post


Link to post
Share on other sites

Posted

I've been following with great interest all of your responses. I haven't had time to sit down and try to digest them, though. I have to admit I'm not working on the level you guys are!

I'll try to review the thread tonight and add some responses.

Thank you all.

0

Share this post


Link to post
Share on other sites

Posted

Shrubman, are you really using FileMaker 7? If not, please update your profile.

0

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0