Friday, 31 August 2007

MySQL DB Design Tutorial (Section 2) - Database Components

The following are a list of the more common table types that will be used in designing a database:

  1. Stage Tables (Raw data in SQL, to be scrubbed), Base tables, Auxiliary tables, Archival tables (for data warehouse), Audit tables (for recovery and layers) and virtual tables (views, derived tables, CTE)
Types of encoding schemes
  1. Measurement Encoding (a measurement encoding is given in some unit of measure, such as pounds, volts or liters) can be done in one of two ways: A. The column contains an implied unit of measure and the numbers represent the quantity in that unit; B. The column explicitly contains the unit.
  2. Abbreviation Encoding (Abbreviation codes shorten the attribute values to fit into less storate space, but the reader easily understands them) is very handy, but as the set of values becomes larger, the possibility for misunderstanding increases. Example: Consider the ISO 3166 Country Codes, which come in two-letter, three-letter and non-abbreviation numeric forms.
  3. Algorithmic Encoding (Algorithmic encoding takes the value to be encoded and puts it through an algorithm to obtain the encodings). Example: Encription is the most common example of an algorithmic encoding scheme.
  4. Hierarchical Encoding (A hierarchy partitions the set of values into disjoint categories, then partitions those categories into subcategories, and so forth until some final level is reached). Example: The most common example is the ZIP code, which partitions the US geographically.

Define Encoding Schemes In MySQL
  • col_name {CHAR | VARCHAR} (col_length) [CHARACTER SET charset_name] [COLLATE collation_name]

No comments: