Database Design Study Notes

Alvernia University Database Design Notes

Data Types

  • Importance of Choosing Correct Data Types
    • Essential for effective database design.
    • Incorrect data types may result in:
    • Inoperability of the database.
    • Extreme memory usage.
    • Compromise between memory optimization and data coverage:
    • Selecting a data type should consider both space efficiency and the ability to accommodate all potential data entries.
    • Future expansion and variety of possible data inputs should be taken into account.

Character/String Data

  • Options for String Data:
    • char(n):
    • Stores characters up to a specified length N.
    • Automatically adds padding or whitespace if the entry is shorter than N.
    • varchar(n):
    • Similar to char(n), stores characters up to N.
    • Does not add padding or whitespace; efficient use of memory.
    • text:
    • Unlimited length, capable of storing up to 1 GB of data.
    • Specific to PostgreSQL.
      • Reference: DeBarros (2022)

Discussion on Character/String Data

  • Pros of char:
    • Fixed length might be advantageous for performance in certain indexing scenarios.
  • Cons of char:
    • May lead to wasted memory due to padding.
  • Considerations for using text:
    • When very long strings of text are common, using text is advisable.

Numerical Data

  • Whole Number Data Types:
    • smallint: 2-byte integer.
    • integer: 4-byte integer (default).
    • bigint: 8-byte integer.
  • Auto-Increment Types (PostgreSQL Specific):
    • smallserial: automatic incrementing small integer.
    • serial: automatic incrementing integer.
    • bigserial: automatic incrementing big integer.
  • Standardized Auto-Increment:
    • IDENTITY qualifier can be used across databases.

Discussion on Numerical Data

  • Examples of Data Types for Specific Scenarios:
    • Class Grades: integer (could also consider numeric for precision).
    • Football Stats: integer for passing, rushing, etc.
    • Product Pricing: decimal for precision.
    • Mileage: integer (could also consider decimal for fractional mileage).

Decimal/Floats

  • Types of Storage for Floating-Point and Fixed-Point Values:
    • decimal/numeric: allows fixed-point storage; suited for precise calculations (e.g., financial data).
    • real: represents a single-precision floating-point number.
    • double precision: represents a double-precision floating-point number, more accurate than real.

Dates and Times

  • Four options for storing dates and times:
    • timestamp: stores both date and time.
    • date: stores only date.
    • time: stores only time.
    • interval: represents time intervals, useful for calculating durations.

Converting Data

  • Data Type Conversion in Queries:
    • Use CAST() function or the shorthand :: operator to change column data types within SQL queries.
    • Reference: DeBarros (2022)

Discussion on Data Type Selection for Various Applications

  • Recommended Data Types for Specific Use Cases:
    • Financial Transaction Record: decimal (to maintain precision).
    • Product Descriptions: text (due to variable lengths).
    • ID Number: integer or varchar (depending on format, but generally integer).
    • ZIP Code: varchar (to preserve leading zeroes).
    • State Abbreviation: char(2) (fixed length as it's always 2 characters).
    • Phone Number: varchar (to accommodate various formats).
    • Street Address: text (to allow for varying lengths and non-standard characters).

References

  • DeBarros, A. (2022). Practical SQL: A beginner's guide to storytelling with data. No Starch Press.