OLTP Database Table Analysis

1
OLTP DatabaseTable Analysis

• Alan Schneider

2
Overview

• The Preliminary Table List
• Define Table Elements
• Guidelines for Creating Table Names
• Guidelines for Composing a Table Description
• Keys

3
The Preliminary Table List

• To develop this list
• Use the Preliminary Field list
• Use the List of Subjects gathered during the
  interviewing process
• Cases/Claims
• Providers
• Patients
• Diagnosis
• Insurance Companies
• Procedures
• Use the Mission Objectives

4
Why Review Preliminary Field List First

• It helps you to identify subjects from an
  unbiased viewpointyou're letting the fields
  "talk" to you.
• It's crucial that you look at this list as
  objectively as possibleas though you've never
  seen it beforewithout any of the biases you've
  collected during the interview process

5
Preliminary Field List Review

• Ask yourself whether a certain set of fields
  defines or describes a particular subject

6
Analyzing the List of Subjects

• Merge with the List of Subjects created during
  the User and Management interviews by
• Resolving duplicate items
• Resolving items that represent the same subject
• Combining the remaining items together into one
  list

7
Analyzing the List of Subjects

• If you find a duplicate item
• Determining whether the items represent different
  subjects
• If they do represent different subjects,
• Rename each occurrence so that it accurately
  identifies the subject it represents
• Add both items to the Preliminary Table List

8
Analyzing the Mission Objectives

• Identify the subjects represented in that
  statement
• If it already appears on the Preliminary Table
  list, determine whether the items represent
  different subjects.
• If they do, assign an appropriate name to each
  occurrence, and add them to the Preliminary Table
  List.

9
Define Table Elements

• Table Type
• Used to identify tables that function in a
  similar manner
• Table Name and Description
• Used to explicitly define the table and state its
  importance to the organization
• If you are unable to explain why a table is
  important to the organization, then you need to
  investigate when and how the table was identified
  and whether it really is necessary at all

10
Table Types

• There are four table types
• Data
• Linking
• Subset
• Validation/Lookup

11
Table Types

• Data (case_main)
• Stores data used to supply information and
  represents a subject that is important to the
  organization
• Linking (case_main_ltgt_xref)
• Used to establish a link between two tables in a
  many-to-many relationship

12
Table Types

• Subset (case_patient)
• Contains supplemental fields that are related to
  a particular data table and further describe the
  subject of that table in a very specific manner
• Validation/Lookup (ref_diagnosis)
• This type of table is used to implement data
  integrity

13
Guidelines for Creating Table Names

• Create a unique, descriptive name that is
  meaningful to the entire organization.
• Using unique names helps to ensure that each
  table clearly represents a different subject
• Choose names that are descriptive enough to be
  self-explanatory
• "Vehicle_Maintenance" is an example of a good,
  descriptive name

14
Guidelines for Creating Table Names

• Create a table name that accurately, clearly, and
  unambiguously identifies the subject of the
  table.
• "Pieces" is an example of a name that is vague
• "Engine_Components" and "Body_Components."
• Use the minimum number of words necessary to
  convey the subject of the table.
• Everyone in the organization should be able to
  identify what the table represents without having
  to read its description.

15
Guidelines for Creating Table Names

• Avoid using a minimalist approach to names
• Consider, for example, the table name "TD_1."
  Unless you know the meaning of each character in
  the name, you won't have the slightest idea what
  this table represents.
• Avoid going to the opposite extreme as well.
• "Multi-Use Vehicle Maintenance Equipment" is much
  too long and can easily be shortened to just
  "Equipment."
• Do not use words that convey physical
  characteristics.
• Steer clear of words such as "File," "Record,"
  and "Table," they add a level of confusion that
  you don't need--a data-file connotation

16
Guidelines for Creating Table Names

• Do not use acronyms and abbreviations.
• Acronyms are hard to decipher
• Abbreviations convey little or no meaning
• Example A table named SC."
• Administrative gt "Steering Committees
• information Systems gt "System Configurations"
• Security gt "Security Codes.".

17
Guidelines for Creating Table Names

• Do not use proper names and other words that will
  unduly restrict the data that can be entered into
  the table.
• For example, a name such as "Southwest Region
  Employees" severely restricts the data that can
  be entered into this table.
• When the organization grows, what will be done
  about employees from other regions?
• Northwest Region Employees
• Midwest Region Employees
• It is poor database design practice to have
  duplicate structures

18
Guidelines for Creating Table Names

• Do not use names that implicitly or explicitly
  identify more than one subject.
• "Facility/Building" and "Space or Area" are
  examples of ambiguous names

19
Guidelines for Creating Table Names

• Use the plural form of the name
• For example
• A sales rep wants to maintain data on all his
  customers, not just a single one
• A car rental business wants to keep track of all
  its vehicles, not just the blue BMW

20
Guidelines for Creating Table Description

• Include a definition statement that accurately
  identifies the table
• Everyone should be able to easily determine the
  identity of the table from its description
  without any confusion or ambiguity
• Make it clear and succinct -- don't be too brief
  or too verbose

21
Guidelines for Creating Table Description

• Avoid restating or rephrasing the table name in
  your table description
• Do not include implementation-specific
  information in your table description, such as
  how or where the table is used
• Do not make the table description for one table
  dependent on the table description of another
  table.

22
Guidelines for Creating Table Description

• Poor Example
• Suppliers The companies that supply us with
  ingredients and equipment
• Good Example
• We need the Suppliers table to keep track of the
  names, addresses, phone numbers, and contact
  names of all our suppliers. Supplier information
  is vital to the bakery because it allows us to
  maintain a constant supply of ingredients and to
  ensure that our equipment is always in working
  order

23
Why Keys Are Important

• They ensure that each record in a table can be
  properly identified
• They are used to establish table relationships
• With the appropriate keys, you guarantee that
• The table structures are sound,
• Redundant data within each table is minimal
• The relationships between tables are solid

24
Primary Key

• It must uniquely identify each record in the
  table
• It must contain unique values
• It cannot be null
• It should contain the minimum number of fields
  necessary to define uniqueness
• It is not optional in whole or in part
• Its value can only be modified in rare or extreme
  cases

25
Artificial Primary Key

• Implemented if choose not to use one of the
  fields in a table, either singularly or as a set
• Establish by creating a new field in the table
  that conforms to the elements of a Primary key

26
Primary Key Rules

• Each table must have one and only one Primary key
• Each Primary key within the database should be
  unique. That is, no two tables should have the
  same Primary key

27
Foreign Keys

• References the Primary Key from another table
• Used to enforce referential integrity
• Ensure that a pair of tables always contain
  matching values