Jump to content

Recording and Maintaining Relative Dating Among Records


Shrubman

This topic is 4430 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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}

  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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)?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 4430 days old. Please don't post here. Open a new topic instead.

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
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.