2

In constrast to

Database design good practices

I wanted to ask:
What are the top worst practises you encountered when having to work with other people's database application code & databases ?

Mine so far are:

  • Not putting any foreign key constrains in the database (for ease of web forms development...)
  • SQL queries by string concatenation (no escaping of ', no parameters)
  • Usage of thread-unsafe code (with static variables) to decrypt the database password...
5
  • 1
    Discussion type questions are deemed Not Constructive per the programmers.stackexchange.com/faq
    – maple_shaft
    Dec 13, 2011 at 13:34
  • @ChrisF the OP asks about practices that we should avoid using. Why is it not constructive?
    – e-MEE
    Dec 13, 2011 at 14:21
  • 1
    Because it generates a list of answers, each equally valid. The complementary question is also not constructive for the same reason. Stack Exchange is not built for this type of question.
    – ChrisF
    Dec 13, 2011 at 14:31
  • @ChrisF oh, okay, that makes sense, I just asked because the answers could be useful, and I saw similar (not closed) question on SO
    – e-MEE
    Dec 15, 2011 at 9:46
  • @e-MEE - It's probably an old question. The guidelines on what makes a good question have been tightened up over the life of Stack Overflow/Exchange as experience shows us what makes good questions (and more importantly bad questions).
    – ChrisF
    Dec 15, 2011 at 9:47

5 Answers 5

8

In no particular order

DB

  • Business logic split between the application layer and database (Stored Procs / Packages)
  • Setting user accounts to db_owner (SQL Server)
  • No auditing of sensitive information (E.G., record last updated by, last updated date)
  • Clear text passwords and sensitive information (E.G., credit card numbers) stored in database tables.
  • Nested stored procedures, we have one application at work that has a chain of 18 stored procedures
  • Mixing of naming conventions (tbl_SomeTable, TBL_SOMETABLE, SomeTable, SOMETABLE), just pick one and stick with it.
  • Allowing table sizes to exceed 8192 bytes (SQL Server)
  • No indexes
4
  • 4
    In what way is 18 nested SPs a bad practice? A program is a very deeply nested set of method calls. Is it not the case that the functional encapsulation matters most - that the problem does logically break into 18 re-usable pieces?
    – MatBailie
    Dec 13, 2011 at 12:33
  • I will see if I can post the code and you can make your own call, it aint pretty.
    – Kane
    Dec 13, 2011 at 12:37
  • I'm nt saying it is always okay, I'm just saying that the problem isn't nested SPs per-se, it's how the SPs have been logically broken down.
    – MatBailie
    Dec 13, 2011 at 12:42
  • @MatBailie the problem is that the purpose of database engines is not running programs. It is persisting data.
    – jwg
    Oct 20, 2015 at 16:09
6

N+1 selects problem.

That is, having a secondary query execute multiple times in a tight loop over the results of a previous query (or a tertiary... etc...).

5

Things that I've seen that have become my pet hates:

  • Using strings for all data (instead of dates, integers etc)
  • Lack of normalization
  • Gratuitous use of cursors
  • UI logic in stored procedures
2
  • Lack of normalization can sometimes be by design
    – maple_shaft
    Dec 13, 2011 at 13:36
  • 1
    @maple_shaft: You're right, of course. But all too often, it really isn't.
    – Kramii
    Dec 13, 2011 at 13:40
3

Over-use of triggers. I once worked with a system where much of the actual work happened in triggers in the db. This created bottlenecks that had our DBA wanting to hunt down the original authors and beat them soundly.

3

Lacking any sort of database scheme versioning, such as an incremental series of DDL scripts, is far far too common. If your "how to setup new development environment" requires a copy of the production db this probably applies to you.

Not the answer you're looking for? Browse other questions tagged or ask your own question.