After discovering so many tables that no primary key, at so many shops, I finally wrote an Oracle script to list all the tables that did not have a PK.
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.
In SQL databases there is no such thing as a “relationship”. There are only foreign key constraints. 🙂
A cartesian product in a single query can make the whole server go slow. The other problem is the result sets, which come back many multiples too big.
No gizmo tool will help you with a cartesian product. No query analyzer will tell you that you have a problem, or how to fix it. Cartesian products are one problem area that only observation, knowledge, and good SQL skills will overcome.
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 …
The startup wanted to insert ONE BILLION rows of data into an Oracle 8i database, every day. Which is about 11,000 rows per second.
A great creative idea, executed poorly, would be crap. You wouldn’t get paid, and you wouldn’t get any referral business.