Search code examples
databasedatabase-designrelational-databasetheorydatabase-theory

Database Normalization and Structure

I have a data stucture that has the following tables

Customers
Transactions (Type A)
Transactions (Type B)

We are adding a Comments Table

Customers have one more more of Transactions A and Transactions B Comments can be related to either the Transactions or the Customer

We are having an internal discussion on the database format.

One side wants to create a Comments table and 3 cross tables. One side wants to create a Comments Table with a foreign key to the customer and 2 nullable keys to the transactions.

Is there a Normal Form rule that says one is better than the other? Is there any consensus?

EDIT:

More answers and specifics

  • Comments will never be associated with more than one customer
  • Comments will never be associated with more than one transaction
  • Comments will only ever be associated with Transaction A, Transaction B, or neither; but never both
  • Customers and Transactions may have 0 or more comments
Solution

  • The most important Normal Forms (1NF-6NF,BCNF and derivatives) don't allow nulls in tables because all of them are based only on relations with values, not nulls. More usefully there is a design principle called the Principle of Orthogonal Design which specifies that tuples with the same attributes should not be permitted to appear in multiple places in your schema. It seems likely that your two transaction tables and having comments in multiple places would break that rule of Orthogonal Design.

    You could create a parent transaction table that combines the common attributes of your two transaction tables, including the comment attribute (supertype/subtype pattern).