So in this application I inherited the database can only be described as a steaming pile of ****. Today I found yet another gem from the previous programmer.
The users table has no primary key column but it did have an identity surrugate id column. Which I promptly set to PK with a single mouse click (so much work right!). All 16 other columns in the table are nullable with no constraints.
The real fun begins though when I saw the code for the webform that creates/updates users. Process goes something like this:
- Admin fills out new user details and clicks a button to call an ajax function to create the user record in the background.
- If no user id is passed in (ie a new user) the system create a new user record by doing an insert "with all data cols set to null" and returning the new userId.
- Once the new userId is found an update is run to set the other 16 column values.
- Finally the userId is returned to the caller and the system closes the web form
In addition transactioning is not used EVER.
Can anybody else see how a web based app, using ajax, no transactioning and no constraints could create problems? Like say 370 duplicated users some with as many as 6 duplicates.