Database Structure


< Prev  TOC  Next >

The PET database has three base tables:

  1. Product
  2. Customer
  3. Problem/Enhancement

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.

Product table definition
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  

Product table data
* 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.

Customer table definition
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  

Customer table data
* 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.

Problem/Enhancement (bug) table definition
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  

Problem/Enhancement (bug) table data
* 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





All content Copyright © 1999 Cornel Huth. All rights reserved.