GUI/Functionality Design: Maintain Name History

UrbanCondor

Hey people,

This is more of a question about GUI design as it is about PHP code itself, however I thought I might post it here just in case anyone has had any great experiences with what I'm trying to do (in PHP).

I am building a program which involves registering a "sport team". One of the attributes of a sports team is "team name", which has a history attached to it. For example, between 1990 and 1995 a team had the name "Hawks" but in 1996 they changed it to "Eagles" and in 1999 then changed it to "Pea****s" (which is what they are currently known as).

I am currently trying to code the functionality which allows the user to adjust the history of their team, for example, they may want to do the following:
- Add the team name "Seagulls" from 1983 to 1990.
- Change the current name to "Crows"
- Adjust the "Eagles" name to start in 19997 instead of 1996

I have started to write the code and have found out that, in order to make it easy on the user, there is a lot of behind-the-scenes functionality involved (e.g. when you change the details of one history record then this may affect the details of other history records). It's taking me a long time and I feel the end result will still not be as user-friendly as possible. Just wondering if anyone has used any online applications where they have had the ability to manipulate the history of something like I am after? Can anyone point me in the direction of some examples? I'm stumped in regards to what phrases I should Google...

Cheers!

bradgrafelman

UrbanCondor wrote:
when you change the details of one history record then this may affect the details of other history records

Why is that?

Based on your description, I would have a separate table containing the "history" as you called it that contains four columns: team_id, team_start_date, team_end_date, team_name. All of the records in the other tables related to this team would share only the team_id value (which should be implemented as something like an AUTO_INCREMENT column).

That way, you'd query this "history" table to get the name given a date (or use the present date if none specified, etc.) like so:

SELECT team_name
FROM team_history
WHERE team_id = 123
    AND '2010-12-28' BETWEEN team_start_date AND team_end_date

UrbanCondor

bradgrafelman;10971259 wrote:
Why is that?

The reason (I think) is to make things more user-friendly, so the user doesn't stuff up the history of the team name.

In the 3rd example I gave:

"-Adjust the "Eagles" name to start in 1997 instead of 1996"

The update command would need to do the following:
1) Update the "Eagles" record to change the the start date from 1996 to 1997
2) Find the previous name record (i.e. the one with an end date of 1996, i.e "Hawks") and change this end date to 1997.

By doing this, there is never a period in time where the team has no name assigned to it. The "Hawks" team name would have an end date of "1996-12-31 23:59:59" and the "Eagles" team name would have a start date of "1997-01-01 00:00:00". The latest/current team name would obviously have an end date of NULL.

See what I mean? Also, the more complicated you get the more you have to adjust other records, e.g. if you wanted to adjust the history to make it that in 1992 the team was knows as the "Sparrows" then you'd have to:
1) Update the "Hawks" record to set the end date to "1991-12-31 23:59:59"
2) Insert the new "Sparrows record from "1992-01-01 00:00:00" to "1992-12-31 23:59:59"
3) Either:
a) Find the closest name record in the future (i.e. "Eagles" and set the start date to "1993-01-01 00:00:00"; OR
b) Insert a new record (called "Hawks" again) and set the start date to "1993-01-01 00:00:00" and the end date to be the same as the start date as the next closest name record in the future (i.e. "Eagles").

Just to explain 3a and 3b a bit better, here is what the history would look like under each:

3a
Hawks (1990 - 1992)
Sparrows (1992 - 1993)
Eagles (1993 - 1999)
Parrots (1999 - NULL)

3b
Hawks (1990 - 1992)
Sparrows (1992 - 1993)
Hawks (1993 - 1997)
Eagles (1997 - 1999)
Parrots (1999 - NULL)

See what I mean? Things get a bit complicated... or am I over-complicating this?