Re: [Bug-gnubg] Not sure about person table in new database

[Jon Kinsey]

Joern Thyssen wrote:

> I see your point, but I'm actually not sure that either schema is
> correct.
>
> We've the following requirements:
>
> (a) it should be possible to have two players with the same nickname but
> different envrionments in the database
>
> (b) it should be possible to group players (e.g., my name is "Jørn
> Thyssen" on IRL, "jthyssen" on GG, etc., but I want to calculate my
> average epm over all these nicks)
>
> Maybe something like:
>
> env: env_id (PK), name
> person: person_id (PK), name, notes
> nick: nick_id (PK), env_id (PK/FK), person_id (FK), nickname
>
> e.g.,  the nickname "nickname" on server "env_id" corresponds to the
> person "person_id". 

This makes more sense, I don't think the person 'name' field is required 
though - I guess the match data would be stored based on the nick_id, so 
for example (without person.name):

* Add match statistics for Player1 vs Player2 (env 0):
        person(0, "Player1 notes")
        nick(0, 0, 0, "Player1")
        person(1, "Player2 notes")
        nick(1, 0, 1, "Player2")
* Now add match statistics for Fibs_P1 (same as Player1 but env 1) vs 
Fibs_P3:
        nick(2, 1, 0, "Fibs_P1")
        person(2, "Fibs_P3 notes")
        nick(3, 1, 2, "Player2")

This covers b) and the previous example (two players both called A in 
two different envs E1 E2):
        person(0, "A on E1")
        nick(0, 0, 0, "A")
        person(1, "A on E2")
        nick(1, 1, 1, "A")

Jon