Proposal for schema

This table will list all areaids used anywhere in the database.
Not much format checking will be done, since this may list mistakes
recorded for the use of admins trying to reconcile old data.

Table: areaids
  Column1: areaid text primary key

This table notes which areaids are considered not reusable, but are not
currently in use (in member). This will include things such as old ids,
special ids and objectionable ids.

Table: reserved_areaids
  Column1: areaid text primary key references areaids

This table defines a unique member. Unless we redo everyone's areaids
the table must except obsolete formats, but this should be more restrictive
than anything. Further restrictions should be imposed by applications
used to add areaids, so that new ones correspond to our current standards.
The idtime will note when the current AREA ID was assigned to this member.
lname is the person's last name. It may include more than one word. It doesn't
include generation. If a person has only one name, it is the last name.
fmname is the person's official first name and whatever middle name information
they will provide. Initials shouldn't have periods.
aname is an alternate to fname for people that go by a different name than
their real first name, especially if the variant isn't standard.
For now only US ASCII characters can be used in names. Eventually this
might be expanded (probably first to Latin 1).
gen is the generation number for people who use that as part of their name.
Sr, I -> 1, Jr, II -> 2, III -> 3, etc.
genlab is a th generation label to use in place of a roman numeral.
nametime is the timestamp for when this name became effective for this person
in our database (not when the person got the name in real life). I expect
that we will be needing to handle people who get married or have name changes
for other reasons, and keeping track of when they had different names might
be useful for resolving confusion.
namepriv says who can get access to their name information.
yob is year of birth. This is optional and I envision it being used for
people trying to find other gamers of around the same age. I don't think
there is a lot of need for exact birthdates. They might be useful for
identifying people uniquely, but I don't think this warrants collecting
the information.
yobpriv says who can see the yob information.

Table: members
  Column1: areaid text primary key references areaids not in reserved_areaids
  Column2: idtime timestamp default current not null
  Column3: lname text not null
  Column4: fmname text
  Column5: aname text
  Column6: gen int gen > 0
  Column7: genlab text (gen and genlab should be checked against a small list)
  Column8: nametime timestamp default current not null
  Column9: namepriv text references priv
  Column10: yob int
  Column11: yobpriv text references priv

This table has a list of telephone contacts for members.
Try to work internationally by requiring country code (make sure that
it is easily available for the US case). For US phone numbers require an
area/city code and a 7 digit local number. The comment is used by the end person
for whatever notes they think might be useful to a caller.
phonepriv says who can see the information.
The same phone number might be listed twice with different comments for
different phonepriv levels
There might be interest in searching for people with phone numbers from
the same country or city/area code.

Table: phone
  Column1: areaid text references members not null
  Column2: country text not null
  Column3: areacode text
  Column4: local text not null
  Column5: extension text
  Column6: comment text
  Column7: phonepriv text references priv

This is just a first go at this table. Later this will be expanded to allow
a member to solicit contact for specified kinds of things related to different
games.
interestpriv controls who can see the information

Table: interest
  Column1: areaid text references members
  Column2: gameid text references games
  Column3: comment
  Column4: interestpriv

This table is used to tell where the member lives or to provide alternate
locations where people might find them to play games. A person may have
multiple places of residence or may play games regularly at a location
fairly far from their home. A person can supply a latitude and longitude
for a location to make searching for nearby players possible. Searching
by municipality won't work nearly as well as using latitude and longitude.
The postal address will be pretty much what you would put on an envelope.
This won't be intended for machine use so it won't be heavily checked
for consistancy (of say zipcode and city). Either the postal address or
latlong may be left null, but not both.

Table: location
  Column1: areaid text refernces members
  Column2: postal
  Column3: latlong
  Column4: comment
  Column5: locpriv

This table lists email contacts for people. Email addresses will be used to
verify information submitted by members. A particular email address can
either be sent verification messages by default, backup or not at all.
Backup ones will be used in certain failover conditions. To be able to update
information, a member must have an email address that can be used to verify
information submitted over the web.
emailpriv says who can see the information.

Table: email
  Column1: areaid text references members
  Column2: local
  Coulmn3: domain
  Column4: verify
  Column5: comment
  Column6: emailpriv

This table keeps track of areaids for people that have had more than one
for whatever reason. The comment for the admins is used to note why the
old id existed. The timestamps allow for better figuring of things out
if the area id was used by more than one person (by mistake).

Table: oldareaids
  Column1: areaid text refernces members
  Column2: oldid test references areaids
  Column3: start timestamp
  Column4: end timestamp
  Column5: comment

This table is used to keep track of old member names, mostly to help admins
who get confused looking at old reports listing the old name.
The timestamps are used to indicate when the name was official for AREA
purposes. The comment is for admins to indicate why the name change was made.
The comment is only for admin use, namepriv only covers the name itself.
For minor name changes (say "Rob" to "Robert") an old names record may not
be needed.

Table: oldnames
  Column1: areaid text references members
  Column2: lname text
  Column3: fmname text
  Column4: aname text
  Column5: gen int
  Column6: genlab text (genlab, gen need to be checked against a small list)
  Column7: start timestamp
  Column8: end timestamp
  Column9: namepriv references priv
  Column10: comment text

There is some argument for keeping old contact information to help figure out
who someone is, but I don't think it will be a big enough help to justify
keeping the data.

Tables related to members' ratings will be handling seperately.