Establishing Table Structures

1. Establishing Table Structures Chapter 7 Database Design for Mere Mortals

2. Defining the Preliminary Table List • Determining implied subjects • review of preliminary field list • group fields by subject • let fields “talk” to you • look at list as objectively as possible

3. Using the list of subjects • cross-check list of subjects (interview) with preliminary field list • objective: to identify duplicate items • if duplicate, are they for different subjects?

4. Using mission objectives • use mission objectives to determine if you overlooked any subjects • underline each subject in the mission objectives • cross check against preliminary table list • if item you underlined in a mission objective statement already appears on the preliminary table list, determine whether the items represent different subjects

5. If the item underlined in the mission objective statement has a name that is synonymous with the name of an item on the preliminary table list and both items represent the same subject, select the name that best identifies that subject and use it in the preliminary table list. • If the item underlined in the mission objective statement represents a mew subject, add it to the preliminary table list.

6. Define the Final Table List • Refining the table names • create a unique, descriptive name that is meaningful to the entire organization • create a table name that accurately, clearly, and unambiguously identifies the subject of the table • Use the minimum number of words necessary to convey the subject of the table • Do not use words that convey physical characteristics

7. do not use acronyms and abbreviations • do not use proper names and other words that will unduly restrict the data that can be entered into the table • do not use names that implicitly or explicitly identify more than one subject • use the plural form of the name

8. Indicating table types • Data : stores data used to supply information and represents a subject that is important to the organization • Linking : used to establish a link between two tables • Subset : contains supplemental fields that are related to a particular data table • Validation : used to implement data integrity

9. Composing the table descriptions (guidelines) • include a definition statement that accurately identifies the table • include a statement that explains why this table is important to the organization • compose a description that is clear and succinct • do not include implementation-specific information in your table description, such as how or where the table is used

10. Do not make the table description for one table dependent on the table description of another table • do not use examples in the table description

11. Interviewing users and management • no longer necessary to involve everyone in organization • use representative group of users and management • you may interview users and management at same time

12. Associating Fields With Each Table • Determine which fields best represent characteristics of the table’s subject and assign them to that table • Use paper, use computer AFTER the design process

13. Refining the Fields • Improving field names • unique, descriptive, meaningful to entire organization • accurately, clearly, unambiguously identifies the characteristics of the field • use minimum number of words • no acronyms, limited use of abbreviations • don’t use confusing names

14. does not identify more that one characteristic • use singular form of name • Using the ideal field to resolve anomalies • it represents a characteristic of the subject of the table • contains a single value • cannot be deconstructed into smaller components

15. does not contain a calculated or concatenated value • unique within the entire database structure • retains all of its characteristics if it appears in more than one table

16. Refining the Table Structures • Redundant data and duplicate fields • redundant data - value that is repeated in a field as a result of the fields’ use as a link between two tables, or is the result of some field or table anomaly • duplicate field - appear in two or more tables, they are used to link a set of tables together, they indicate multiple occurrences of a particular type of value, result of a perceived need for supplemental information

17. Elements of the ideal table • represents single subject - object or event • has a primary key • no multi-part fields • no multi-values fields • no calculated fields • no unnecessary duplicate fields • contains minimum amount of redundant data

18. Resolving unnecessary duplicate fields • Establishing subset tables • refine previously unidentified subset tables • Case Study