Database Design
Click here to Download a printable version (PDF)
The principal key field for the database is the dog ID number. This is a unique field for each shiba entered in the database. We suggest a sequential number generator providing the field internally. This field will generally be transparent to the user.
The overall design will represent many separate data files interacting together to give the user the appearance of one database file. The use of this design is to eliminate dead space or blank fields in the dog record and to optimize the database for quicker response time while giving the user flexibility to design ad hoc reports.
Dog Id File
Fields
Dog ID Number
The file will consist of only one field. As a new shiba is added to the database, this number increases by one (1) and that number is assigned to the new record. As records are deleted, the file may be compressed from time to time to reorder data. Deletions may occur early in the process, but will occur rarely when the database is considered functional. Care should be used if the Dog ID File is re-sequenced to reflect any ID change to the other files where this field serves as a data pointer or key.
Dog Name File
Fields
Dog ID Number
Dog Name
This file links a given spelling or translation of a given dog’s name to a specific dog. This accommodates issues with translation variations, as well as punctuation variables. The user should be able to enter a given name and have all variations for a given dog appear on the main screen. Early in the process we may find a dog listed several times in the database due to the name variations. As the records are linked, the duplications will be deleted. Additionally, a wild card function should be provided in that a user can enter the first few characters of a name and the system should provide a list of all of the dogs’ names that match the text string. Additionally, the system should have a wild card function to locate an embedded text string. For example, if the user enters the test string “red” the system will locate all names where that text string occurs: Red Rover’s Buddy, Big Red Dog, & Drop Dead Fred. Searches should not be case sensitive.
Registry File
Fields
Registry Name
Registry Acronym
This file is the list of registries that we wish to acknowledge. This assures consistency as well as the ability to preclude specific registries, if desired. As a user is entering data for a given dog, the user may select the appropriate registry from the designated list. Options may include: AKC, Nippo, JKC, Spain, etc. or No Registry. No registry will accommodate shibas such as rescue dogs, whose health data should not be precluded from the database.
Dog Registry File
Fields
Dog ID Number
Dog Registry
Dog Registration Number within this Registry
This file links a given registry and registration number to a specific dog. This accommodates issues with dogs that may have multiple registrations. The user should be able to enter a given name and have all registry information appear. Registry information will not link to name, as variations of the name may occur within the same registry. From the Add a Dog screen, the user should be given the option to add another registry.
Pedigree File
Fields
Dog ID Number
Sire Name
Dam Name
The optimal design of this file requires the addition of parents prior to the addition of a given dog. While a dog is being added, the user will be able to select the sire or dam through the search function. Once the user selects a specific dog, the system will assign the internal ID to the data record. Rather than storing the name or registration number of the parents, due to the various name spellings and registry options these fields should be populated with the internal pointers. Adding another generation (n) to a pedigree report simply requires the program to locate parentage n levels deep. If this approach is used care should be exercised if a compression program is used to re-sequence deleted entries.
Individual Dog File
Fields
Dog ID Number
DOB
Place of Birth
Breeder ID
Current Owner ID
Gender
Coat Color (Phenotype)
Coat Color (Genotype)
Markings
Undercoat Color
Coat Texture
Coat Length
Eye Color
Pigmentation
Adult Height
Adult Weight
DNA Profile ID
DNA Sequence
Microchip ID
Microchip Registry
Tattoo
Date Deceased
This file contains most of the physical information pertaining to the dog. Health information is stored in a different file. Breeder information can be singular or plural. Current owner information can be singular or plural. Coat color, Markings, Undercoat Color, Coat Texture, Coat Length, Eye Color and Pigmentation are selected from the defined matrix. Height and weight can be entered in either US or metric with the system converting to display the measurement in both systems.
Coat Color (Phenotype) Matrix
R = Red
Intensity numeric designation 1-5 R1 = Pale red, R3 = Orange, and R5 = mahogany red (brownish red)
RS = Red Sesame (Red visually dominant)
BS = Black Sesame (Black visually dominant)
BT = Black & Tan
Intensity numeric BT1 Black with red tint, B2 Intense Black
C = Cream/White
BD = Brindle
BN = Brown
BK = Black
RN = Roan
O = Other/Unknown
Only one entry for this field is permissible.
Coat Color (Genotype) Matrix
RR = Clear Red (Has not produced cream offspring)
RC = Clear Red (Has produced cream offspring)
SS = Genetic Sesame (Has not produced cream offspring)
SC = Genetic Sesame (Has produced cream offspring)
BB = Black & Tan (Has not produced cream offspring)
BC = Black & Tan (Has produced cream offspring)
CC = Cream
Only one entry for this field is permissible.
Marking Patterns (More than defined as Urajiro)
SBE = Socks below elbow
SAE = Socks above elbow
TSL = Large spot on tail
STR = Stripe on face
CLR = Collar
PTR = Spots on body (pinto type patterning)
Multiple entries for this field are permissible.
Undercoat Color Matrix
B = Brown
C = Cream
G = Gray
W = White
Only one entry for this field is permissible.
Coat Texture Matrix
1-5
1 = Very Soft to 5 = Very Coarse
Only one entry for this field is permissible.
Coat Length Matrix
1-5
1 = Very Short to 5 = Long Coat
Only one entry for this field is permissible.
Eye Color Matrix
BR = Brown
BL = Blue
1-5 denotes color depth BR1 = yellowish brown BR5 = Deep Brown
Only one entry for this field is permissible.
Pigmentation Matrix
NB = Black Nose
NL = Liver Nose
NS = Pink Stripe or edging on Nose
NP = Pink Nose
EB = Black Eye Rims
EL = Liver Eye Rims
EP = Pink Eye Rims
GB = Black Gums & Lip Line
GL = Liver Gums & Lip Line
GP = Pink Gums & Lip Line
BA = Black Anus
LA = Liver Anus
PA = Pink Anus
TW = White Toenails
TB = Black Toenails
MW = Mixed –Predominantly white
MB = Mixed – Predominantly black
SP = Black Spots on Tongue
Multiple entries for this field are permissible.
Breeder File
Fields
Breeder ID
Last Name
First Name
Middle Initial
Address Line 1
Address Line 2
City
State
Zip
Phone
Email
NSCA Member
Breeder and Owner fields are self-explanatory. These can be separate or combined fields. They may be a reason to separate breeders from owners for certain mailing or contact functions. Another option if files are combined is to include a field to identify the individual as a breeder. The NSCA member field is populated with a Y or N. At some point we may elect to give members a higher security clearance than non-members or we may wish to use this field as a sort or contact option.
Owner File
Fields
Owner ID
Last Name
First Name
Middle Initial
Address Line 1
Address Line 2
City
State
Zip
Phone
Email
NSCA Member
Fields are defined the same as in the Breeder file.
Handler File
Fields
Handler ID
Last Name
First Name
Middle Initial
Address Line 1
Address Line 2
City
State
Zip
Phone
Email
NSCA Member
Fields are defined the same as in the Breeder file.
Litter File
Fields
Dog ID Number
Whelp Date
Litter Number
Litter Registry
Total Puppies
Stillborn Puppies
Total Males
Total Females
Caesarian Section
Whelping Notes
Dog ID Number represents the Dam ID Number. Puppies’ fields are numeric and C-Section field represents a Yes/No response. We could include mating information such as natural breeding, fresh chilled or frozen semen, if this is of interest for tracking. Due to the possibility of multiple sires in a litter, the sire identification should attach to the individual puppy, as opposed to the litter. Another option might be to add a field for multiple sires. In this situation a sire(s) field could be included.
Individual Dog Vaccine File
Fields
Dog ID Number
Vaccine ID
Combination Vaccine
Vaccine Date Given
Vaccine Date Due
Vaccination Manufacturer
Vaccination Lot Number
Separating the vaccination from the record of the individual dog will save space if the data is not created and may provide if there is ever a need to sort or search for vaccine/illness correlation. Vaccine ID is provided from the vaccine matrix. Combination vaccines should allow for the entry of each component, and the system should generate the individual records. We may wish to add a Manufacturer Matrix to eliminate error and make it a little easier on the end user. A possible addition to the matrix might be common combination vaccines, such as DA2PP. If this option is included, the system should recognize the individual components for these combination vaccines.
Vaccine Matrix
ADN = Adenovirus
BRD = Bordatella
CRN = Corona
DST = Distemper
LEP = Leptospirosis
LYM = Lyme Disease
PIN = Parainfluenza
PRV = Parvo
RAB = Rabies
Health Registry File
Health Registry Code (Acronym)
Health Registry Name
Screening Offered
Findings Offered
This is the listing for each health registry we will acknowledge, along with the screenings and findings offered. A link to the registry website may be an option to provide registry contact information.
Health Screening File
Fields
Dog ID Number
Registry Code
Registry Screening
Date of Screening
Registry Number
Finding
This file ties the results of a particular screening to an individual dog. For example the registry code might be OFA, the Registry screening might be Hips and the finding would indicate the health screening results. The user screen might ask the Registry Code from a matrix that might include OFA, CERF, PHIP or VET, where VET indicates veterinary results not affiliated with a registry. The user interface might prompt the user based on the Registry Code with a menu of options. For example if the code is OFA, the user may be prompted to select Patella, Hip, Elbow, etc. The code VET may prompt the user for such items as dentition chart, patella check, etc. In certain cases, such as CERF there may be multiple similar entries where the only variable field is screening date or result.
Health Issue Occurrence File
Fields
Dog ID Number
Diagnostic Category
Diagnostic Description
Date of Diagnosis
Diagnosing Veterinarian
Document ID
Notes/Treatment/Comments
This record is the individual occurrence file for disorders or diseases. This record may accompany veterinary reports. We could scan these reports to make them available and we may want to create a document number field in this data record to provide a link to a specific report on file. Using this approach an individual dog may never have an occasion to appear in this file or an individual dog may appear multiple times for different issues. The Dog ID field will link this to a specific dog’s record and will allow for viewing as well as ad hoc search reporting. The note/comment field allows the user to provide notes or updates if applicable.
Veterinary Diagnosis Matrix
This should contain a list of veterinary diagnostic categories and the associated descriptions of the diagnosis.
Diagnostic Category
Diagnostic Description
Upon request a veterinary diagnosis matrix will be supplied. This matrix, as with many of the others should be populated as an administrative function. The preliminary list of categories is: allergy, bacterial, behavioral, cardiac, critical care, dentistry, dermatology, ear/nose/throat, emergency, endocrine (hormonal), internal medicine, neurological/neurosurgery, oncology/cancer/radiation, ophthalmic, orthopedic, parasitic, protozoan, reproductive, surgery, viral, and zoonotic.
Individual Dog Title File
Dog ID Number
Discipline
Titling Body
Title Code
Title Designation
Title Description
Date Earned
This file will contain the individual occurrences of a title or designation for a dog. Discipline would include the major categories such as agility, companion, conformation, obedience, rally, and other performance might include areas such as tracking. The Titling Body is the organization that tracks the requirements and offers the title. Title Code is the character string assigned to represent the title, such as CH. The Title Designation refers to the placement of the code as either a suffix or prefix. Initially, these titles will be provided from a defined list of AKC or NSCA titles, other titling bodies may be added as required.
NOTE: Design should use either the description in this section or the Prefix/Suffix option below. The system can operate in either manner. A final determination should be based on ease of reporting or ease of programming.
Prefix Title File
Fields
Dog ID Number
Discipline
Titling Body
Title Code
Title Description
Date Earned
This file structure separates the prefix title from the suffix titles for ease of printing in the screen and report functions. However, the files could be combined with the addition of a position field with the acceptable values of P or S to indicate prefix or suffix, such as is outlined in the previous section. All other fields are the same.
Suffix Titles
Fields
Dog ID Number
Discipline
Titling Body
Tile Code
Title Description
Date Earned
This file structure separates the prefix title from the suffix titles for ease of printing in the screen and report functions. However, the files could be combined with the addition of a position field with the acceptable values of P or S to indicate prefix or suffix, such as is outlined in the previous section. All other fields are the same.
Title Discipline Matrix
Discipline
Initial Disciple options will include: agility, companion, conformation, obedience, rally, and other performance.
Titling Body Matrix
Titling Body Acronym
Titling Body Name
This matrix includes those entities whose titles we wish to recognize. The acronym, such as AKC is the designation frequently used to identify the organization, where the Name, such as American Kennel Club represents the official name of the titling body.
Title Matrix
Discipline
Titling Body Acronym
Title Code
Title Designation
Title Description
Administrator Functions
The system design should allow for and accommodate at least two system administrators within the NSCA organization to oversee basic housekeeping operations. The administrator is different from the operator and there should not be an overlap between administrators and operators.
The Administrator will have responsibility for updating the matrix files that are used to populate the drop down screens for data entry operations. The administrator should be able to add an entry or option to any of these records/files and upon completion have the user screen present this entry/option to the user. The administrator will also maintain and assign operator privileges and routinely monitor the operator activity for correctness. In the case of disputed information, the Operators will review the situation with regard to policy and make a recommendation to the Administrator for action. The Administrator may execute the recommended action or in the case of lack or clarity or confusion as to policy refer the information to the NSCA Board for a final decision. The administrator will also have access to and maintain the user password file.
Administration Files Maintained
Operator Identification and Password files
This file allows the administration to add an operator to the system with all inherent operator privileges. The Operator file should include the operators name as well as a unique identifier, such as initials for the operator as well as the operator password.
Registry File
Defines for the user the registry options available for population in the registry lookup function or screens.
Coat Color (Phenotype) Matrix
Defines the options available to the user in the lookup function or screens. Initially: R = Red, RS = Red Sesame, BS = Black Sesame, BT = Black & Tan, C = Cream/White, BD = Brindle, BN = Brown, BK = Black, RN = Roan, O = Other/Unknown
Coat Color (Genotype) Matrix
Defines the options available to the user in the lookup function or screens. Initially: RR, RC, SS, SC, BB, BC, or CC.
Marking Patterns (More than defined as Urajiro)
Defines the options available to the user in the lookup function or screens. Initially: SBE, SAE, TSL, STR, CLR, PTR, or OTR.
Undercoat Color Matrix
Defines the options available to the user in the lookup function or screens. Initially: B, C, G, or W.
Eye Color Matrix
Defines the options available to the user in the lookup function or screens. Initially: BR or BL.
Pigmentation Matrix
Defines the options available to the user in the lookup function or screens. Initially: NB, NL, NS, NP, EB, EL, EP, GB, GL, GP, BA, LA, PA, TW, TB, MW, MB or SP.
Vaccine Matrix
Defines the options available to the user in the lookup function or screens. Initially: AND, BRD, CRN, DST, LEP, LYM, PIN, PRV, RAB. We may wish to include popular combo shots, or the vaccine specifics, such as modified live, etc.
Health Registry File
Defines the options available to the user in the lookup function or screens. Initially: OFA, CERF, PHIP, VET.
Health Issue Matrix
This should contain a list of veterinary diagnostic categories and the associated descriptions with any alternate descriptions.
Diagnostic Category
Defines the options available to the user in the lookup function or screens. Initially: allergy, bacterial, behavioral, cardiac, critical care, dentistry, dermatology, ear/nose/throat, emergency, endocrine (hormonal), internal medicine, neurological/neurosurgery, oncology/cancer/radiation, ophthalmic, orthopedic, parasitic, protozoan, reproductive, surgery, viral, and zoonotic.
Diagnostic Description
The list of Diagnostic Descriptions below is a product of Canid’s research and is proprietary. This list was used for the initial design function. Another vendor such as Gary is likely to have compiled a similar listing.
Notes:
Many health issues can be placed into more than one category and overlap multiple disciplines. Also, some disorders are initially seen by internal medicine and then referred to surgery and vice versa.
Title Discipline Matrix
Defines the options available to the user in the lookup function or screens. Initially: agility, citizen, combo, conformation, obedience, rally, register of merit and tracking.
Titling Body Matrix
Defines the options available to the user in the lookup function or screens. Initially: AKC – American Kennel Club, NSCA – National Shiba Club of America.
Title Matrix
Discipline: Agility
Titling Body: American Kennel Club
Title Designation: Prefix/Suffix
Title Title Description
AJP Excellent Agility Jumpers with Weaves "A" Preferred
AX Agility Excellent
AXJ Excellent Agility Jumper
AXP Agility Excellent "A" Preferred
FTC FAST Century
FTC2 FAST Century 2
FTC3 FAST Century 3
FTC4 FAST Century 4
FTC5 FAST Century 5
FTCP FAST Century Preferred
FTCP2 FAST Century Preferred 2
FTCP3 FAST Century Preferred 3
FTCP4 FAST Century Preferred 4
FTCP5 FAST Century Preferred 5
MFP Master Excellent FAST Preferred
MJP Master Excellent Jumpers With Weaves "B" Preferred
MX Master Agility Excellent
MXF Master Excellent FAST
MXJ Master Excellent Jumpers With Weaves
MXP Master Agility Excellent "B" Preferred
NA Novice Agility
NAP Novice Agility Preferred
NAJ Novice Agility Jumper
NF Novice FAST
NFP Novice FAST Preferred
NJP Novice Jumpers with Weaves Preferred
OA Open Agility
OAJ Open Agility Jumper
OAP Open Agility Preferred
OF Open FAST
OFP Open FAST Preferred
OJP Open Jumpers with Weaves Preferred
PAX Preferred Agility Excellent
PAX2 Preferred Agility Excellent 2
PAX3 Preferred Agility Excellent 3
PAX4 Preferred Agility Excellent 4
PAX5 Preferred Agility Excellent 5
XF Excellent FAST
XFP Excellent FAST Preferred
Discipline: Citizen
Titling Body: American Kennel Club
Title Designation: Prefix/Suffix
Title Title Description
CGC Canine Good Citizen
Discipline: Combo
Titling Body: American Kennel Club
Title Designation: Prefix/Suffix
Title Title Description
VCD1 Versatile Companion Dog 1
VCD2 Versatile Companion Dog 2
VCD3 Versatile Companion Dog 3
VCD4 Versatile Companion Dog 4
Discipline: Conformation
Titling Body: American Kennel Club
Title Designation: Prefix/Suffix
Discipline: Obedience
Titling Body: American Kennel Club
Title Title Description
CD Companion Dog
CDX Companion Dog Excellent
UD Utility Dog
UDX Utility Dog Excellent
UDX2 Utility Dog Excellent 2
UDX3 Utility Dog Excellent 3
UDX4 Utility Dog Excellent 4
UDX5 Utility Dog Excellent 5
Discipline: Rally
Titling Body: American Kennel Club
Title Designation: Prefix/Suffix
Title Title Description
RA Rally Advanced
RAE Rally Advanced Excellent
RAE2 Rally Advanced Excellent 2
RAE3 Rally Advanced Excellent 3
RAE4 Rally Advanced Excellent 4
RAE5 Rally Advanced Excellent 5
RE Rally Excellent
RN Rally Novice
Discipline: Register of Merit
Titling Body: National Shiba Club of America
Title Designation: Prefix/Suffix
Title Title Description
ROM Register of Merit
Discipline: Tracking
Titling Body: American Kennel Club
Title Designation: Prefix/Suffix
Title Title Description
TD Tracking Dog
TDX Tracking Dog Excellent
VST Variable Surface Tracking
User Inquiry Options
From the main data base screen any user may opt to review or inquire about a shiba in the database. Users should be able to enter a registry and registration number for a lookup on a given dog. Additionally, a name search should be available where the user may enter a text string, and the system should respond with the shibas matching the search string. For example, if the user enters “shiba”, all of those names in the database with the text string “shiba” within the name should be displayed. The user may then highlight the dog chosen for review by selecting the desired shiba from the list. To streamline the search we may chose to require at least 5-8 characters to limit the usage of system resources.
When a dog is selected, the information for that individual dog should be displayed. The user should have the opportunity to view pedigree information, name information, registry information, title, health screens, individual dog information, vaccination information and health information. Also desirable, is the ability to link to parents, grandparents, siblings, half siblings, and littermates.
Reporting Options
The function of the data base should facilitate ease of reporting. Prior to ramp up the administration function will create a list of “canned” reporting options. The system documentation will provide the command structure for the generation of ad hoc reports. A portion of the system design will be for the supplier to educate the administrative and documentation groups as to the commands inherent to the data base underlying program, as well as any required operators and system limitations. Typical reports may include such items as list of progeny for a specific sire or dam, list of shibas with a particular disorder, sibling report, in-breeding or line breeding coefficients, etc.
User Addition and Change Options
Individuals wishing to add or change records must sign into their account. If they do not have an account, they must create one prior to accessing any features that can result in a change to the database. The account creation is baseline for the owner file, during initial setup the account owner may also indicate whether they breed or handle to create entries in those files. The sign in process should link the user id to a secure password file.
After a user is signed in, the system should allow them to navigate to add and change functions.
Policy and Procedure Issues
A fundamental element of the design will be to determine who has the authority to add or change records. Is the system voluntary in that only the owner of the dog is allowed to add or change the record? What about co-owners? What about the breeder? Is there a distinction between pedigree information and health information? Can anyone provide pedigree information or should there be restrictions? If a user owns a particular dog, are they entitled to enter the information on the parents of their dog, if they are not owners or breeders of the dog? Who may enter health information? If we directly import information from health registries, such as CERF, do we assume that since the user allowed the publication of the information with the health registry, they are also providing consent for us to use the information? Are there any copyright issues with directly importing information from any registry health or pedigree? How do we handle any disputes that arise between the owners or breeders regarding published information? Must all agree to disclosure?
Once a user adds a record or changes a record, the information should post to a suspense file. The changes will not be authorized for data base inclusion, until verified by an operator. A list of acceptable methods of verification, as well as the acceptable formats for these verification documents should be listed for each type of change. The verification documents should be assigned a document number and the operator must store the documentation to create a document trail prior to approval.
Policies and procedures should be developed for disciplining any user or member intentionally providing false or misleading information. We must make every effort that our database is not used as a tool for fostering animosity or as a tool to discredit individuals involved in disputes between owners and breeders.
The policies and procedures manual should specify for each diagnostic description what documentation is required and potentially assign it a reliability factor (1-5). For example, if an owner is submitting information that their shiba is missing a pre-molar or is a cryptorchid, we may opt to allow that type of information without a veterinary record. In this case we may elect to give it a reliability factor of 2. However, if the owner submits a veterinary statement or billing record indicating the cryptorchid shiba was neutered, we may opt to give it a reliability factor of 5. These are merely discussion items for possible inclusion in the policies and procedures documents. If we elect to assign a reliability factor to the information, this field would be added to the appropriate files.
