Database Description for DB2000
Table: Members Table holding roster fields and many local fields not
defined in roster.
|
Name |
Type |
Size |
Roster |
Mtd |
Comments |
|
|
Mem_ID |
Number
(Long) |
4 |
No |
DB2000 |
Primary
identifier for member* |
|
|
Cert |
Text |
7 |
Yes |
HQ |
Certificate
number. Secondary identifier |
|
|
Last |
Text |
25 |
Yes |
HQ |
Last name |
|
|
First |
Text |
16 |
Yes |
HQ |
First name
with middle initial (or combo) |
|
|
Rank |
Text |
8 |
Yes |
HQ |
HQ Rank
abbreviation** |
|
|
SquadRank |
Text |
8 |
Yes |
User |
Squadron rank
abbreviation |
|
|
Grade |
Text |
2 |
Yes |
HQ |
Grade
abbreviation |
|
|
Squadron |
Text |
4 |
Yes |
HQ |
Account number |
|
|
Address |
Text |
30 |
Yes |
User |
Member address |
|
|
Address2 |
Text |
30 |
Yes |
User |
Additional
address info |
|
|
City |
Text |
30 |
Yes |
User |
City |
|
|
State |
Text |
2 |
Yes |
User |
State
abbreviation, caps |
|
|
ZIP |
Text |
10 |
Yes |
User |
Zip code*** |
|
|
Phone |
Text |
12 |
Yes |
User |
Telephone
number nnn nnn-nnnn |
|
|
Fax |
Text |
12 |
Yes |
User |
Fax phone |
|
|
Email |
Text |
40 |
Yes |
User |
Email address |
|
|
Spouse |
Text |
30 |
Yes |
User |
Spouse name |
|
|
SB_Address |
Text |
30 |
No |
User |
Dual address
members away addrs |
|
|
SB_Address2 |
Text |
30 |
No |
User |
Dual address
members |
|
|
SB_City |
Text |
30 |
No |
User |
Dual address
members |
|
|
SB_State |
Text |
2 |
No |
User |
Dual address
members |
|
|
SB_ZIP |
Text |
10 |
No |
User |
Dual address
members |
|
|
SB_Fax |
Text |
12 |
No |
User |
Dual address
members |
|
|
SB_Phone |
Text |
12 |
No |
User |
Dual address
members |
|
|
SB_BusPhone |
Text |
12 |
No |
User |
Dual address
members |
|
|
SB_BusExt |
Text |
5 |
No |
User |
Dual address
members |
|
|
SB_BusOK |
Text |
1 |
No |
User |
Dual address
members |
|
|
SB_Email |
Text |
40 |
No |
User |
Dual address
members |
|
|
SB_Status |
Yes/No |
1 |
No |
User |
Is member dual
address? |
|
|
SB_Where |
Text |
1 |
No |
User |
Is DA member
at home or away |
|
|
District |
Text |
2 |
Yes |
HQ |
Squadron
district number |
|
|
GH_P |
Text |
6 |
Yes |
HQ |
Grade History
– |
Pilot grade |
|
GH_AP |
Text |
6 |
Yes |
HQ |
|
Advanced Pilot |
|
GH_JN |
Text |
6 |
Yes |
HQ |
|
Junior
Navigator |
|
GH_N |
Text |
6 |
Yes |
HQ |
|
Navigator |
|
GH_S |
Text |
6 |
Yes |
HQ |
|
Seaman |
|
GH_OT |
Text |
6 |
Yes |
HQ |
|
Operational
Training |
|
GH_CP |
Text |
6 |
Yes |
HQ |
|
Cruise
Planning |
|
GH_EM |
Text |
6 |
Yes |
HQ |
|
Engine
Maintenance |
|
GH_IQ |
Text |
6 |
Yes |
HQ |
|
Instructor |
|
GH_IT |
Text |
6 |
Yes |
HQ |
|
Obsolete
instructor grade |
|
GH_IA |
Text |
6 |
Yes |
HQ |
|
Obsolete
instructor grade |
|
GH_ME |
Text |
6 |
Yes |
HQ |
|
Marine
Electronics |
|
GH_SAIL |
Text |
6 |
Yes |
HQ |
|
Sail |
|
GH_W |
Text |
6 |
Yes |
HQ |
|
Weather |
|
GH_Merit |
Number
(Integer) |
2 |
Yes |
HQ |
Number of
merit marks |
|
|
GH_Senior |
Text |
4 |
Yes |
HQ |
Year senior
member |
|
|
GH_Life |
Text |
4 |
Yes |
HQ |
Year life
member |
|
|
Ed_Pro |
Text |
4 |
Yes |
HQ |
Contained in
module file |
|
|
Ed_Ach |
Text |
4 |
Yes |
HQ |
Contained in
module file |
|
|
CertDate |
Text |
8 |
Yes |
HQ |
Certificate
date |
|
|
TotYrs |
Number (Long) |
4 |
Yes |
HQ |
Total years |
|
|
OS |
Text |
4 |
Yes |
HQ |
Original
squadron |
|
|
OD |
Text |
2 |
Yes |
HQ |
Original
district |
|
|
PS |
Text |
4 |
Yes |
HQ |
Previous
squadron |
|
|
PD |
Text |
2 |
Yes |
HQ |
Previous
district |
|
|
PCert |
Text |
7 |
Yes |
HQ |
Primary
certificate holder |
|
|
Birthday |
Text |
8 |
Yes |
User |
Birth date |
|
|
Memsex |
Text |
1 |
Yes |
User |
Member’s sex |
|
|
Sposex |
Text |
1 |
Yes |
User |
Member’s
spouses sex |
|
|
SpoCert |
Text |
7 |
Yes |
User |
Spouses
certificate number |
|
|
BusPhone |
Text |
12 |
Yes |
User |
Business phone |
|
|
BusExt |
Text |
5 |
Yes |
User |
Business
extension |
|
|
PhoneTag |
Text |
1 |
Yes |
User |
Can bus. phone
be used Y/N |
|
Table: Members, cont.
Fields
|
Name |
Type |
Size |
Roster |
Mtd |
Comments |
|
LastBDU |
Text |
8 |
Yes |
DB2000 |
Last BDU date
database modified |
|
LastHQ |
Text |
8 |
Yes |
HQ |
Last HQ date database
modified |
|
MemTypeStat |
Text |
4 |
Yes |
HQ |
Member
status**** |
|
Squad1 |
Text |
30 |
Yes |
User |
User field
1***** |
|
Squad2 |
Text |
30 |
Yes |
User |
User field
2***** |
|
BoatName |
Text |
30 |
Yes |
User |
Boat
statistics HQ |
|
BoatType |
Text |
30 |
Yes |
User |
Boat
statistics HQ |
|
BoatPort |
Text |
30 |
Yes |
User |
Boat
statistics HQ |
|
BoatOAL |
Text |
6 |
No |
User |
Boat
Statistics Local |
|
BoatBeam |
Text |
6 |
No |
User |
Boat
Statistics Local |
|
BoatDraft |
Text |
6 |
No |
User |
Boat
Statistics Local |
|
BoatPower |
Text |
15 |
No |
User |
Boat
Statistics Local |
|
BoatFuelCap |
Text |
10 |
No |
User |
Boat
Statistics Local |
|
BoatSpeed |
Text |
10 |
No |
User |
Boat
Statistics Local |
|
BoatRange |
Text |
10 |
No |
User |
Boat
Statistics Local |
|
BoatClearance |
Text |
6 |
No |
User |
Boat
Statistics Local |
|
BoatBerths |
Text |
15 |
No |
User |
Boat
Statistics Local |
|
OtherMember |
Text |
20 |
No |
User |
Member has
affiliate status |
|
BDUchange |
Yes/No |
1 |
No |
DB2000 |
BDU changes
pending flag |
|
Tag |
Yes/No |
1 |
No |
User |
Record is
tagged |
|
Wedding |
Text |
8 |
No |
User |
Wedding date |
|
CellPhone |
Text |
12 |
No |
User |
Cell phone
number |
|
SB_CellPhone |
Text |
12 |
No |
User |
Dual address
member cell phone |
|
Partner |
Yes/No |
1 |
No |
DB2000 |
Spousal
relationship established |
|
Xcheck |
Yes/No |
1 |
No |
DB2000 |
Spousal
cross-check made, record flagged |
|
VSC_01 |
Text |
8 |
Yes |
HQ |
Data contained
in download module file |
|
ID_001 |
Text |
8 |
Yes |
HQ |
Data contained
in download module file |
|
* |
Mem_ID is the primary
identifier. It is an auto number in Members table and index in others. |
|
** |
Rank abbreviation is
derived from a number code in roster file. Text conversion is via table
“Rank” in HQ database HQ2000v2.mdb |
|
*** |
Zip code is derived from
two separate fields in roster file. |
|
**** |
Member status is derived
from a code and converted in table “Membertype” in HQ database HQ2000v2.mdb |
|
***** |
User fields maintained by
HQ. These differ from DB2000 user defined fields in that they are public to all
members downloading roster updates. This means that squadrons need a
universal reason for their use |
.
When DB2000 imports a roster it dynamically makes new fields for completed course modules or a Vessel Safety Check approved member. The following are typical entries.
|
Name |
Type |
Size |
HQ |
Comments |
|
AP-000C |
Text |
8 |
Yes |
Date, closed
book |
|
AP-000O |
Text |
8 |
Yes |
Date, open
book |
|
PI-000O |
Text |
8 |
Yes |
|
|
SE-000C |
Text |
8 |
Yes |
|
|
SA-101C |
Text |
8 |
Yes |
|
|
SA-102C |
Text |
8 |
Yes |
|
|
CP-000C |
Text |
8 |
Yes |
|
DB2000 has the ability for the user to define fields. The data is kept in the table UserFields which is synchronized with the Members table. These tables are joined for most operations.
Name |
Type |
Size |
Comments
|
|
Mem_ID |
Number (Long) |
4 |
Primary ID, list same as Members table, used to join. |
|
User defined |
User |
n |
User defined fields will be listed here |
User may define following types:
|
Text |
User defined
length to 255 characters |
|
Number |
Long integer |
|
Boolean |
Yes / No field |
|
Date |
Text field
with formatting provided by DB2000 |
|
Currency |
Floating point
double precision rounded to two decimal places. |
|
Phone |
Text field
with formatting provided by DB2000 |
|
Memo |
Unlimited
size. Will accept simple formatting, CR LF etc. |