Normalization of Dates

[Western_Neil]

Hello again;

Based on the answers of the last two question (Time/Date stamp Location & Handling nonspecific dates), the date field group has expanded to three fields for each table:
Recorded Date …. text type
Accuracy ….. number type
Working Date …..date type
Does it make sense for each date point to use all three fields in each table or to use a link pointing to that date?

Usually within the data, there are many dates that are identically and their accuracy is the same (in fact same source). I’m thinking since each date could change independently I should bite the bullet and handle them all as a group. This could become a real pain as I know a lot of dates are solidly linked (same official source), but not all. (25% of 000's of records still a lot)

Comments ?

Neil

[orange]

Can you give us a few examples of the requirement - Starting data and required output showing some sort samples?
If the field has meaning to you, or is used to derive some info that you require, then separate fields would be a starting point.

[Western_Neil]

Okay Orange
If an organization moved from Brussels to Antwerp in mid-July and changed its name on July 4^th then :
tOrg
___ OrgID = 25

tOrgxName
___ OrgxNameID = 83 ......... = 84
___ OrgID ......... = 25 ......... = 25
___ NameID ...... = 55 ......... = 56

tName
___ NameID ...... = 55 ........................ = 56
___ NameTitle = “International Shipping Company of Brussels” .... = “International Shipping Company of Belgium”
___ NameRDate ... = before July 2016 ... = July 4^th 2016
___ NameDateA ... = -9999 .................. = 0
___ NameDate ..... = 31-6-2000 ........... = 4-7-2016

---

tOrgxLocation
___ OrgxLocationID .... = 47 ........ = 48
___ OrgID ................. = 25 ........ = 25
___ LocationID .......... = 20 ......... = 21

tLocationID
___ LocationID ........ = 20 ........................... = 21
___ LocationName ... = Brussels ................... = Antwerp
___ LocationRDate ... = 2^nd Quarter of 2010 .. = mid-July 2016
___ LocationDateA ... = 45 .......................... = 15
___ LocationDate ..... = 15-5-2010 ............... = 15-7-2016

Note: LocationName should be a pointer because many organizations are in both cities with different dates.

If this organization has some underlings that moved too, then I would have a number of identical records (Even when it started it would have 2+ (Name & Location). Should this be a separate table of dates or repeat the dates all over the place?

Neil

[orange]

Here's a draft model -based on your post - hope it is helpful.
Rank is meant to be a number identifying the confidence you have in the approximate Date(s).

[Western_Neil]

Yes, that is about i was thinking, so back to the original question:

We have 3 fields repeated in OrgName and OrgLocation (plus about 5 more tables). They reflect very similar data, should they be combined? (to reduce duplicated data)

Neil

PS what program was that done in?

[orange]

It seems OrgName relates to changes in an Organization's name. And OrgLocation deals with changes in an Organization's Location. Two different things. An Organization could move without changing its Name. An Organization could change its Name without moving. And an Organization could move to a new Location and change its Name simultaneously.

The software used was ErWin Community Edition.

[Western_Neil]

Yes. That is correct. The other way is to place a pointer (in each table) to a Date Table so the Move & Rename occurrence point to the same record.

My fear is this too much splitting and may lead to data error. I'm looking for comments on which way you (all of you) would go.

Neil