have address columns in each table or an address table that is referenced by the other tables?

by

Database Normalization is all about constructing relations (tables) that maintain certain functional
dependencies among the facts (columns) within the relation (table) and among the various relations (tables)
making up the schema (database). Bit of a mouth-full, but that is what it is all about.

A Simple Guide to Five Normal Forms in Relational Database Theory
is the classic reference for normal forms. This paper defines in simple terms what the essence of each normal form is
and its significance with respect to database table design. This is a very good “touch-stone” reference.

To answer your specific question properly requires additional information. Some critical questions you have to ask
are:

  • Is an Address a simple fact (e.g. blob of text) or a composite fact (e.g.
    composed of multiple attributes: Address line, City Name, Postal Code etc.)
  • What are the other “facts” relating to “Accommodation”,
    “Airport” and “Train Station”?
  • What sets of “facts” uniquely and minimally identify an “Airport”, an “Accommodation”
    and a “Train Station” (these facts are typically called a key or candidate key)?
  • What functional dependencies exist among Address facts and the facts
    composing each relations key?

All this to say, the answer to your question is not as straight forward as one might hope for!

Is there such a thing as “over normalization”? Maybe. This depends on whether the
functional dependencies you have identified and used to build your tables are
of significance to your application domain.

For example, suppose it was determined that an address
was composed of multiple attributes; one of which is postal code. Technically a postal
code is a composite item too (at least Canadian Postal Codes are). Further normalizing your
database to recognize these facts would probably be an over-normalization. This is because
the components of a postal code are irrelevant to your application and therefore factoring
them into the database design would be an over-normalization.

More Related Contents:

  1. Is storing a delimited list in a database column really that bad?
  2. What are database normal forms and can you give examples? [closed]
  3. Normalization: What does “repeating groups” mean?
  4. In terms of databases, is “Normalize for correctness, denormalize for performance” a right mantra?
  5. Minimum no of tables that exists after decomposing relation R into 1NF?
  6. What’s the better database design: more tables or more columns?
  7. Should each and every table have a primary key?
  8. What is the minimal proof that a database relation is not in BCNF?
  9. Database, Table and Column Naming Conventions? [closed]
  10. How are super- and subtype relationships in ER diagrams represented as tables?
  11. What is the best design for a database table that can be owned by two different resources, and therefore needs two different foreign keys? [closed]
  12. Difference between scaling horizontally and vertically for databases [closed]
  13. Relational table naming convention [closed]
  14. When I should use one to one relationship?
  15. Why use a 1-to-1 relationship in database design?
  16. What is the optimal length for an email address in a database?
  17. Supertype-subtype database design
  18. Database optimization orders
  19. In what way does denormalization improve database performance?
  20. What’s wrong with nullable columns in composite primary keys?
  21. Best representation of an ordered list in a database?
  22. Limit an sqlite Table’s Maximum Number of Rows
  23. NULL permitted in Primary Key – why and in which DBMS?
  24. What are some best practices and “rules of thumb” for creating database indexes?
  25. When we need to use 1-to-1 relationship in database design?
  26. What exactly does database normalization do?
  27. Databse architecture (single db vs client specific db) for Building Enterprise Web (RIA) application on cloud
  28. Derived account balance vs stored account balance for a simple bank account?
  29. In a StackOverflow clone, what relationship should a Comments table have to Questions and Answers?
  30. uncertainty in developing a database model

Leave a Comment