24 replies to this topic

[Shrubman]

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!

[dansmith65]

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.

[Shrubman]

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.

[comment]

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, 08 February 2012 - 08:37 AM.

[Shrubman]

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.

[comment]

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?

[Shrubman]

If I understand your response...yes.

[comment]

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.

[Shrubman]

Thanks for your input.

[Shrubman]

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.

[dansmith65]

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.

[Shrubman]

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.

[comment]

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}

[dansmith65]

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.

[comment]

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?

[jbante]

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

[comment]

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.

[jbante]

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.

[comment]

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.

[jbante]

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