background preloader

Ten Common Database Design Mistakes

Ten Common Database Design Mistakes
No list of mistakes is ever going to be exhaustive. People (myself included) do a lot of really stupid things, at times, in the name of “getting it done.” This list simply reflects the database design mistakes that are currently on my mind, or in some cases, constantly on my mind. I have done this topic two times before. Before I start with the list, let me be honest for a minute. So, the list: Poor design/planning Ignoring normalization Poor naming standards Lack of documentation One table to hold all domain values Using identity/guid columns as your only key Not using SQL facilities to protect data integrity Not using stored procedures to access data Trying to build generic objects Lack of testing Poor design/planning “If you don’t know where you are going, any road will take you there” – George Harrison Prophetic words for all parts of life and a description of the type of issues that plague many projects these days. Ignoring Normalization Are there always 12 payments? Maintainability Related:  shiny stuffdatabase

Five simple database design tips A flawed database can affect all areas of your application, so getting the design right is of paramount importance. Check out Builder's five simple design tips, and share some of your own. If an enterprise’s data is its lifeblood, then the database design can be the most important part of an application. I’ll get the show started by listing my five favorite tips and giving a brief explanation of the rationale behind each one. #1: Use meaningful field namesI once worked on a project I inherited from another programmer who liked to name fields using the name of the onscreen control that displayed the data from that field. Unless you are using a system that restricts you to short field names, make them as descriptive as possible—within reason, of course. #2: Do your homeworkNot only should you research your business needs when designing a new database, you should check out the existing system, as well. Restrict the user from editing the field after the record’s creation.

Using Connection String Keywords with SQL Server Native Client There are two ways OLE DB applications can initialize data source objects: IDBInitialize::Initialize IDataInitialize::GetDataSource In the first case, a provider string can be used to initialize connection properties by setting the property DBPROP_INIT_PROVIDERSTRING in the DBPROPSET_DBINIT property set. In the second case, an initialization string can be passed to IDataInitialize::GetDataSource method to initialize connection properties. Both methods initialize the same OLE DB connection properties, but different sets of keywords are used. The set of keywords used by IDataInitialize::GetDataSource is at minimum the description of properties within the initialization property group. Any provider string setting that has a corresponding OLE DB property set to some default value or explicitly set to a value, the OLE DB property value will override the setting in the provider string. connection-string ::= empty-string[;] | attribute[;] | attribute; connection-string empty-string ::=

Unity 3 – April 2013 patterns & practices Developer Center April 2013 Unity is a lightweight, extensible dependency injection container with support for instance and type interception. Overview Unity is a lightweight, extensible dependency injection container with support for constructor, property, and method call injection. Simplified object creation, especially for hierarchical object structures and dependencies. Unity is a general-purpose container for use in any type of Microsoft.NET Framework-based application. In addition, Unity is extensible. This release adds support for Windows Store apps as well as the registration by convention feature to ease the task of configuring Unity. Audience Requirements These reusable components and guidance are intended primarily for software developers and software architects. Contents of This Release Microsoft Unity 3 contains the following: Binaries. System Requirements Supported architectures: x86 and x64. For a rich development environment, the following are recommended:

How to share data between stored procedures An SQL text by Erland Sommarskog, SQL Server MVP. Most recent update 2013-11-02. Introduction This article tackles two related questions: How can I use the result set from one stored procedure in another, also expressed as How can I use the result set from a stored procedure in a SELECT statement? In this text I will discuss a number of possible solutions and point out their advantages and drawbacks. Here is a summary of the methods that I will cover. At the end of the article, I briefly discuss the particular situation when your stored procedures are on different servers, which is a quite challenging situation. A related question is how to pass table data from a client, but this is a topic which is outside the scope for this text. Examples in the article featuring tables such as authors, titles, sales etc run in the old sample database pubs. OUTPUT Parameters This method can only be used when the result set is one single row. Rewrite this procedure as: Table-valued Functions Inline Functions

AngularJS — Superheroic JavaScript MVW Framework Ten Common SQL Programming Mistakes Database delivery patterns & practices STAGE 2 Continuous Integration: Fundamentals It is not always easy to spot "antipatterns" in your SQL, especially in more complex queries. In this article, Plamen demonstrates some of the most common SQL coding errors that he encounters, explains their root cause, and illustrates potential solutions. Programming in SQL can be both a fun and a challenging task. My intention with this article was to highlight some of the more common mistakes that I've seen people make when coding SQL. SELECT TOP(10) mistake FROM CommonSQLProgrammingMistakes Without further ado, here is the list: NULLs and the NOT IN predicate Functions on indexed columns in predicates Incorrect subquery column Data type mismatch in predicates Predicate evaluation order Outer joins and placement of predicates Subqueries that return more than one value Use of SELECT * Scalar user-defined functions Overuse of cursors Colors table: color Black Blue Green Red Products table: 1 Ball Red 2 Bike Blue

c# - Best Practices of fault toleration and reliability for scheduled tasks or services A Visual Explanation of SQL Joins I love the concept, though, so let's see if we can make it work. Assume we have the following two tables. Table A is on the left, and Table B is on the right. id name id name -- ---- -- ---- 1 Pirate 1 Rutabaga 2 Monkey 2 Pirate 3 Ninja 3 Darth Vader 4 Spaghetti 4 Ninja Let's join these tables by the name field in a few different ways and see if we can get a conceptual match to those nifty Venn diagrams. There's also a cartesian product or cross join, which as far as I can tell, can't be expressed as a Venn diagram: SELECT * FROM TableA CROSS JOIN TableB This joins "everything to everything", resulting in 4 x 4 = 16 rows, far more than we had in the original sets.