The database “design” was a binary tree! The nodes were actually being used for both storage, and indexing! In order to find any data, you had to write a routine to traverse the tree. That is, instead of using standard tables, and standard SQL Select statements, with Where clauses to filter the rows.
There were circular references in the foreign keys. You couldn’t insert into first table, because it needed to find the row (via the foreign key) in the second table. But when you went to insert into the second table, you couldn’t insert there, because it needed to find a row in the first table.
Don’t create the wrong object. I’ve seen some strange “designs”. Ie. Another database was created instead of a schema. A table was created instead of a simple function. A series of five tables and views was created when a single field would have sufficed!
Another Database Design Mistake to Avoid Recursive programming is used in a few places in computer science. Most notably in…
The rule of thumb for the number of tables:
Many to Many (M:N) 3 tables
One to Many (1:M) 2 tables
One to One (1:1) 1 table
Creating a LOB for every customer record, whether it was used or not, caused some serious problems.
A 1:1 relationship was split into two tables. Now a table join was required for almost every query. Querying on a single table would take 10 to 15 seconds. After joining to the second table to get a few more fields, the response time slowed down to about 45 to 60 seconds! About 4 times as long.
Here is an Oracle script to find the tables that don’t have any foreign keys.
Tables that are neither a parent table, or a child table.
Another Database Design Mistake to Avoid is the Redundant Foreign Key.
There are three ways to implement Referential Integrity in a database system. Primary and Foreign Keys, Triggers, Application code. And each method results in different database performance.