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

[Joern Thyssen]

On Thu, May 27, 2004 at 02:12:05PM +0100, Jon Kinsey wrote
> I don't think the three tables (person, env and player) are quite
> right.
> Here's how it is now:
>
> environment:
>                                                                               
>                     Name
>                                                                               
>                     of
>                                                                               
>                     place
> person:
>                                                                               
>                     Name
>                                                                               
>                     Notes
> player:
>                                                                               
>                     A
>                                                                               
>                     environment
>                                                                               
>                     A
>                                                                               
>                     person
>
> For example if there were two players both called A in two different
> envs E1 E2 it would have to be stored like this:
>
> environment: E1, E2
> person: (A, Desc of player A)
> player: (A, E1), (A, E2)
>
> The problem is that both players share the same description.

No, this is why the name is not the key on the "person" table. You can
have:


environment: E1, E2
person: (137, A, Desc of player A), (138, A, Desc of player A)
player: (137, E1), (138, E2)

The advantage is that two players can be the same person.

> I think we can remove the person table and just have the other 2:
>
> environment:
>                                                                               
>                     Name
>                                                                               
>                     of
>                                                                               
>                     place
> player:
>                                                                               
>                     A
>                                                                               
>                     environment
>                                                                               
>                     Name
>                                                                               
>                     Notes
>
> So the above example would now look like this:
>
> environment: E1, E2
> player: (E1, A, Desc of player E1A), (E2, A, Desc of player E2A)
>
> Any thoughts?

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".