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.
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.
Too Few Fields in the Primary Key –
… But when new data came along that did not have a 1:1 relationship, but actually had a 1:M relationship, this became a problem
By including a third field in the PK, the maximum possible rows per day now became 86,400 squared. Which is, approximately 7.4 billion rows, per code, per day, would be allowed! The problem result: Duplicate data.
The unfortunate thing in database designs is that unlike programs, they are rarely, if ever, reworked. If a program doesn’t compile, the code is corrected until it does compile. And then if the program doesn’t produce the right output, the code is again corrected until it does. I’ve seen one relatively simple program be refined and put into production at least eight times. … The database design is usually thrown up as quickly as possible …