The PET database has three base tables:
The Product (P) and Customer (C) tables could be used, and probably would be, by other MIS personal (for example, sales). The Problem and Enhancement (bug) (B) table would be used by the development team. For this database all three tables are used. The B table is a combined problem and enhancement table, with both problem reports and enhancement requests in the same table. This table could be split, but this tutorial is using the assumption that an enhancement request is the same as a problem.
The Product table stores product name, number, and version. It
is possible to have more than one entry for a product if the version
number differs. The primary key is a composite key made from the product ID code and
version number: PID+PVER. The key length is 7 bytes (3+4 bytes).
The product code is a number (in ASCII text) from 401 to 499. This field could have been typed as an N field, but since it's domain is limited to a small set, it is simpler to deal with this field as a C field (including a 0T). The version number is alphanumeric (may be all numbers, all letters, or a mix). The version number is always 4 characters. The product name may be up to 53 characters and is terminated by a 0. It, as all C field types in this database, is left-justified within the field. Other likely fields, such as release date and support period, are not included.
fieldName | type | size | example | notes |
---|---|---|---|---|
unnamed | n/a | 1 | ' '
| * (ASCII 42) marks record as deleted
|
PID | C | 4 | "401"
| PK comp 1 of 2, ASCII text (0T, "401" to "499") |
PVER | C | 5 | "1200"
| PK comp 2 of 2, ASCII text (0T, alphanumeric, always 4 chars) |
PNAME | C | 54 | "UmaNet 12.00"
| zero-terminated string (53 max chars + 0T, left-justified within field) |
64 | = record length |
* | PID | PVER | PNAME |
---|---|---|---|
401 | 1200 | UmaNet 12.00
| |
405 | 0100 | UmaScan 1.00 |
The Customer table stores customer ID, name, address, phone, and e-mail. There should not be more than one entry per customer, but it's possible that this can occur if, for example, the customer has a new address. For this tutorial, it is expected that the above cases are not going to happen, and that the customer will always remember his CID in all transactions.
The primary key is the CID field. This field is generated at data entry, by the program, by combining the last name (first 4 characters, with the space character used to pad out to four), the first initial, and the first 3 characters of the zip code (or 999 if no postal code). The key length is 8 bytes (4+1+3 bytes), even though the CID field in the data record is sized to 9 bytes (SUBSTR() is used in the key expression to use only the first 8 characters of this field). I've sized the data field to 9 bytes to allow a 0T in the data record; this is a design decision, and could be 8 bytes if the 0T were not wanted.
For this tutorial, the field count has been kept low so the name and address fields are just one field each. It might be useful to break out the name field to first, last, middle, and the address to company, street, city, state, zip, country. All the fields in the Customer table are C type, and their data (strings) are zero-terminated.
fieldName | type | size | example | notes |
---|---|---|---|---|
unnamed | n/a | 1 | ' '
| * (ASCII 42) marks record as deleted
|
CID | C | 9 | "NaugP782"
| PK, 0T, lastname(4),first(1),zip(3) |
CNAME | C | 40 | "Peggy S Naughton"
| 0T |
CADDR | C | 96 | "PSN Inc/90 N St/Town/ST/78230/US"
| 0T |
CPHONE | C | 30 | "8885551234"
| 0T |
CEMAIL | C | 64 | "psn@naughton.com"
| 0T |
240 | = record length |
* | CID | CNAME | CADDR | CPHONE | CEMAIL |
---|---|---|---|---|---|
NaugP782 | P Naughton | Street/City/ST/Zip/Ctry | 8885551212 | psn@naughton.com | |
AngsS844 | S Angstl | Street/City/ST/Zip/Ctry | 999 | mimuelle@sozw.fh-muenchen.de |
The Problem/Enhancement (bug) table stores the ID of the product (product ID plus product version), the sequence number for this BID, the date, report type, priority (severity), the customer ID, resolution status, a short description, and a long description.
The primary key is a composite key made from the BID and BSEQ fields. The BSEQ field is a counter of the number of entries for this BID. The BID is also a foreign key into the Product table. The customer ID field, BCID, is used as a foreign key into the Customer table.
As a problem report comes in the data for this table is entered. If this is a first-time
customer a customer entry is made first. The product table is already up-to-date. To get
a new BSEQ number, a key of BID+BSEQ with BSEQ set to 999 is used in a call to
BltGetEqualOrLesser
. If a current entry for this BID exists, the last BSEQ
used can be determined and then incremented by one for this entry. If none currently exists,
the BSEQ for this entry would be set to 1 ("001"). (It may be tempting to allow duplicate
BID keys and forgo the need for BSEQ, but doing so can make extensions to this database
impossible.) The other fields are set as required. The SUBSTR() operator is used on the
BID and BSEQ fields to skip their trailing 0Ts when building the composite key. This
technique is shown in Part III.
fieldName | type | size | example | notes |
---|---|---|---|---|
unnamed | n/a | 1 | ' '
| * (ASCII 42) marks record as deleted
|
BID | C | 8 | "4011200"
| 0T, pid(3), pver(4); foreign key into Product; PK comp 1/2 |
BSEQ | C | 4 | "001"
| 0T, sequence for this BID; PK comp 2/2 |
BDATE | D | 8 | '19980923'
| |
BTYPE | C | 1 | 'e'
| report type (b=bug report, e=enhancement request) |
BP | C | 1 | '4'
| priority code (0 to 9, 0=highest priority) |
BCID | C | 9 | "NaugP782"
| 0T, foreign key into Customer |
BST | C | 1 | 'o'
| status ('o'=open, 'x'=closed) |
BSDESC | C | 53 | "docs not completed"
| short description |
BLDESC | M | 10 | '0000000002'
| long description (memo) |
96 | = record length |
* | BID | BSEQ | BDATE | BTYPE | BP | BCID | BST | BSDESC | BLDESC |
---|---|---|---|---|---|---|---|---|---|
4050100 | 001 | 19980901 | b | 8 | AngsS844 | x | driver loads even if H/W missing | 0000000001 | |
4011200 | 001 | 19980923 | e | 4 | NaugP782 | o | docs not completed | 0000000002 |