Another Database Design Mistake To Avoid
One very bizarre design that I saw actually had circular references between tables.
The primary/foreign keys of two tables, were actually found in the other table.
Note: This modeling tool will not allow circular references. The second line, I actually had to draw in manually. Good tool!
Result:
I was asked to insert some rows for the java developers. But it wasn’t possible. 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 there, because it needed to find a row in the first table.
I had to disable the constraints, insert into both tables, and then re-enable them. It took a lot of time.
Disabling constraints is classically something a DBA would do, and only rarely. It is not something a developer would do just to insert some data.
Correction:
There were many other problems with this “design”. Circular references were just one of them.
Fortunately, the “designer” resigned soon after, and we were able to build a better design.