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. If you’re interested in hearing the podcast version, visit Greg Low’s super-excellent SQL Down Under. I also presented a boiled down, ten-minute version at PASS for the Simple-Talk booth. 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 Ignoring Normalization Are there always 12 payments? Poor naming standards Security

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. Volumes have been written on this topic, and entire college degrees have been built around it. However, as has been said time and time again here on, there’s no teacher like experience. I’ll get the show started by listing my five favorite tips and giving a brief explanation of the rationale behind each one.

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 one-to-many relationship On the page about linking tables I showed you how data from different tables can be linked by defining a foreign key relationship. You saw how orders are linked to customers by including the customer_id as a foreign key in the orders table. Another example of a one-to-many relationship is the relationship that exists between a mother and her children. A mother can have many children and each child has only one mother. (Technically it would be better to speak of a woman and her children instead of a mother and her children, because in a one-to-many relationship a mother can have 0, 1 or many children and a mother with 0 children isn't technically a mother. But let's just play along, ok?)

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. How to share data between stored procedures An SQL text by Erland Sommarskog, SQL Server MVP. Most recent update 2013-11-02. Introduction The many-to-many relationship The many-to-many relationship is a relationship where multiple rows from table A can correspond to multiple rows in table B. An example of such a relationship is a school where teachers teach students. In most schools each teacher teaches multiple students and each student can be taught by multiple teachers. The relationship between beer distributors and the beers they distribute is a many-to-many relationship too.

Audit and document Security User Rights Assignment using C# For more information on the Windows Server documentation abilities of XIA Configuration please see the following We've recently been asked about the auditing of User Rights Assignment as seen in the Local Group Policy Editor. Having looked into this issue it is apparent that this cannot be done with any high level API such as WMI and requires the use of P/Invoke and the Win32 method LsaEnumerateAccountsWithUserRight. I've seen many posts on the Internet on the topic but I haven't seen a simplified example of this in use so have writen a quick C# sample application to enumerate the various rights and privileges and display the users assigned to that right in a grouped listview as per the screenshot below.

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.