Tell me more ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.
up vote 1 down vote favorite

In constrast to

http://programmers.stackexchange.com/questions/123591/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...
share|improve this question
1  
Discussion type questions are deemed Not Constructive per the programmers.stackexchange.com/faq – maple_shaft♦ Dec 13 '11 at 13:34
@ChrisF the OP asks about practices that we should avoid using. Why is it not constructive? – e-MEE Dec 13 '11 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 '11 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 '11 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 '11 at 9:47

migrated from stackoverflow.com Dec 13 '11 at 12:17

closed as not constructive by ChrisF♦ Dec 13 '11 at 12:45

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or specific expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, see the FAQ for guidance.

5 Answers

up vote 8 down vote

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
share|improve this answer
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 '11 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 '11 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 '11 at 12:42
up vote 6 down vote

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...).

share|improve this answer
up vote 4 down vote

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
share|improve this answer
Lack of normalization can sometimes be by design – maple_shaft♦ Dec 13 '11 at 13:36
@maple_shaft: You're right, of course. But all too often, it really isn't. – Kramii Dec 13 '11 at 13:40
up vote 3 down vote

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.

share|improve this answer
up vote 3 down vote

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.

share|improve this answer

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