http://www.sqlservercentral.com/Content tagged Database Design, Basics posted on SQLServerCentral.comen-us360sjones@sqlservercentral.com (Steve Jones)Two weeks ago Andy started his wildly successful series on Worst Practices. This week he continues that series discussing why failing to use primary keys and clustered indexes are worst practices. Agree or disagree, read the article and join the discussion. One thing you'll have to admit, reading an article by Andy is a lot more interesting than reading Books Online! http://www.sqlservercentral.com/articles/Miscellaneous/worstpracticesnotusingprimarykeysandclusteredindex/488/2008/05/09http://www.sqlservercentral.com/articles/Miscellaneous/worstpracticesnotusingprimarykeysandclusteredindex/488/Jambu Krishnamurthy brings us a look at the various options you have available with views and some of the techniques you can use to build a better database by incorporating them in your design. http://www.sqlservercentral.com/articles/Basics/2936/2008/03/25http://www.sqlservercentral.com/articles/Basics/2936/The classic paper from Dr. Codd.http://www.sqlservercentral.com/redirect/articles/3047/2007/06/25http://www.sqlservercentral.com/redirect/articles/3047/Jambu Krishnamurthy brings us a look at the various options you have available with views and some of the techniques you can use to build a better database by incorporating them in your design. http://www.sqlservercentral.com/articles/Basics/2936/2008/03/25http://www.sqlservercentral.com/articles/Basics/2936/When designing and creating a data model for a data-driven application, the records in tables must each be uniquely identifiable. By having a unique value associated with each record, individual records can be selected, updated, or deleted. Being able to uniquely identify records is so important and standard in a database system that databases allow those designing a database table to specify what column (or columns) make up the primary key.http://www.sqlservercentral.com/redirect/articles/2721/2006/12/22http://www.sqlservercentral.com/redirect/articles/2721/Data space can be a concern in many situations as more and more data is being stored in your systems. Designing your schema to take this into account is important for a DBA and Steve Jones takes a look at how design decisions can be used to solve a business problem. http://www.sqlservercentral.com/articles/Basics/2730/2006/11/23http://www.sqlservercentral.com/articles/Basics/2730/Relational tables often describe more than one type of real world entity. In this tip, Bob Watkins covers some things to think about when designing such tables.http://www.sqlservercentral.com/redirect/articles/2672/2006/11/13http://www.sqlservercentral.com/redirect/articles/2672/The alternative to surrogate keys is natural data keys. In order to understand the many substantial benefits of surrogate keys it's necessary to discuss some background on the issues involved.http://www.sqlservercentral.com/redirect/articles/2240/2006/01/10http://www.sqlservercentral.com/redirect/articles/2240/Whether you are building SQL Server 2000 applications or getting ready for the new features in SQL Server 2005, there are many fundamental database design principals that should be followed. New author Ranga Narasimhan brings us a look at how he handles reference tables when designing a database.. http://www.sqlservercentral.com/articles/Basics/databasedesignandreferencetables/2053/2005/09/27http://www.sqlservercentral.com/articles/Basics/databasedesignandreferencetables/2053/Two weeks ago Andy started his wildly successful series on Worst Practices. This week he continues that series discussing why failing to use primary keys and clustered indexes are worst practices. Agree or disagree, read the article and join the discussion. One thing you'll have to admit, reading an article by Andy is a lot more interesting than reading Books Online! http://www.sqlservercentral.com/articles/Miscellaneous/worstpracticesnotusingprimarykeysandclusteredindex/488/2008/05/09http://www.sqlservercentral.com/articles/Miscellaneous/worstpracticesnotusingprimarykeysandclusteredindex/488/This chapter covers the building blocks available to the database designer and database user to create and modify database objects and data. The SQL Data Types will be covered along with data representation as literals (constants) and expressions.http://www.sqlservercentral.com/redirect/articles/1769/2005/03/09http://www.sqlservercentral.com/redirect/articles/1769/Mindy explores the metadata stored in SQL 2000 to show you how to produce a simple and useful data dictionary! http://www.sqlservercentral.com/articles/Installation/datadictionaryfromwithinsqlserver2000/607/2005/02/04http://www.sqlservercentral.com/articles/Installation/datadictionaryfromwithinsqlserver2000/607/Fabian Pascal, famed database author and writer takes his shots at a recent thread on another SQL site. He makes some good points about normalization.http://www.sqlservercentral.com/redirect/articles/1668/2005/01/20http://www.sqlservercentral.com/redirect/articles/1668/Designing a SQL Server database is a challenging task. Making decisions about how to build tables, their relations, etc. can be a full time job for any DBA helping to build an application. Lookup tables are a part of just about every application that you work with or build a back end for. Leo Peysakhovich brings us an article on database design that deals specifically with lookup tables. http://www.sqlservercentral.com/articles/Basics/lookuptabledesign/1424/2004/07/27http://www.sqlservercentral.com/articles/Basics/lookuptabledesign/1424/Two weeks ago Andy started his wildly successful series on Worst Practices. This week he continues that series discussing why failing to use primary keys and clustered indexes are worst practices. Agree or disagree, read the article and join the discussion. One thing you'll have to admit, reading an article by Andy is a lot more interesting than reading Books Online! http://www.sqlservercentral.com/articles/Miscellaneous/worstpracticesnotusingprimarykeysandclusteredindex/488/2008/05/09http://www.sqlservercentral.com/articles/Miscellaneous/worstpracticesnotusingprimarykeysandclusteredindex/488/Disk space is getting cheaper everyday. Why should you worry about the amount of space your data is consuming? This article by Neil Boyle presents some great reasons why you should be concerned. http://www.sqlservercentral.com/articles/Performance+Tuning/savingspace/208/2003/02/21http://www.sqlservercentral.com/articles/Performance+Tuning/savingspace/208/Most databases designs nowadays seem to have at least a few if not many lookup or reference tables. This article helps you define a strategy in how to design, approve, and deploy them. http://www.sqlservercentral.com/articles/Basics/lookupstrategy/921/2003/02/20http://www.sqlservercentral.com/articles/Basics/lookupstrategy/921/For most DBAs, normalization is an understood concept, a bread and butter bit of knowledge. However, it is not at all unusual to review a database design by a development group for an OLTP (OnLine Transaction Processing) environment and find that the schema chosen is anything but properly normalized. This article by Brian Kelley will give you the core knowledge to data model. http://www.sqlservercentral.com/articles/Basic+Querying/normalization/584/2003/01/13http://www.sqlservercentral.com/articles/Basic+Querying/normalization/584/A few months ago Greg Larsen ran across a big problem with SQL Server collation when querying. He shares his bruises in this quick article. http://www.sqlservercentral.com/articles/Administering/collate_part1/875/2002/12/19http://www.sqlservercentral.com/articles/Administering/collate_part1/875/In this short article by Jamie Voss, he shows you the data model behind much of his task tracking system and the reasoning behind it. http://www.sqlservercentral.com/articles/Basics/designingflexibletaskdbpart2/827/2002/10/17http://www.sqlservercentral.com/articles/Basics/designingflexibletaskdbpart2/827/The strength and holy grail of relational databases lies in the very name: relations. Microsoft has put a good deal of intelligence and cunning into query optimizations, caching, indexing and execution plans to make the process of finding related records even smoother and faster. This small article, however, will try to shatter the very sacred notion of relational databases. http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/relationaldatabasewithoutrelations/733/2002/07/29http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/relationaldatabasewithoutrelations/733/An extremely useful tool to reverse engineer a database is Visio 2000. It also has the ability to generate a database. This generation takes place after Visio has been used to draw the tables and relationships. This process can be a huge time saver. If a database needs to be tweaked and reworked it can be done in Visio and then regenerated. This article is intended to provide a nice introduction to using Visio 2000 to generate a database. http://www.sqlservercentral.com/articles/Basics/visiodesign/738/2002/07/22http://www.sqlservercentral.com/articles/Basics/visiodesign/738/Bill Wunder has donated his utility for those who have a free SQLServerCentral.com mebership. The DDL Archive Utility will look into a database and automatically archive the DDL from the database into Source Safe saving hours of hassles! http://www.sqlservercentral.com/articles/Administering/729/2002/07/11http://www.sqlservercentral.com/articles/Administering/729/Binary data can be stored as integers in a table. This article explains how to query an integer field to return the bits represented by the integer. http://www.sqlservercentral.com/articles/Basic+Querying/queryresponse/717/2002/06/28http://www.sqlservercentral.com/articles/Basic+Querying/queryresponse/717/This article demonstrates how to store checkbox results as integers in a database...perfect for surveys! http://www.sqlservercentral.com/articles/Basic+Querying/storingresponses/718/2002/06/27http://www.sqlservercentral.com/articles/Basic+Querying/storingresponses/718/In this article by Jamie Voss, he shows the methodology he used to develop his task management database. http://www.sqlservercentral.com/articles/Basics/designingaflexibletaskdb/688/2002/05/20http://www.sqlservercentral.com/articles/Basics/designingaflexibletaskdb/688/Andy read this book recently and gave it a great review. Even though it's not a SQL book! Seriously, take a look at the review. It's a good book that is fun to read. http://www.sqlservercentral.com/articles/Miscellaneous/reviewofdebuggingthedevelopmentprocess/612/2002/02/26http://www.sqlservercentral.com/articles/Miscellaneous/reviewofdebuggingthedevelopmentprocess/612/Mindy explores the metadata stored in SQL 2000 to show you how to produce a simple and useful data dictionary! http://www.sqlservercentral.com/articles/Installation/datadictionaryfromwithinsqlserver2000/607/2005/02/04http://www.sqlservercentral.com/articles/Installation/datadictionaryfromwithinsqlserver2000/607/Last week Andy started a discussion of the various ways you can reduce the number of round trips to the server. This week he continues by looking at a method he used recently to do client side caching of data to eliminate the round trip altogether. Gotta read it! http://www.sqlservercentral.com/articles/Performance+Tuning/reducingroundtripspart2/588/2002/02/06http://www.sqlservercentral.com/articles/Performance+Tuning/reducingroundtripspart2/588/Identity columns are last years news. Have you experimented with uniqueindentifiers - better known to programmers as GUID's? Guaranteed to be unique in the world, they offer a powerful alternative to identity columns. http://www.sqlservercentral.com/articles/Basics/usinguniqueindentifierinsteadofidentity/439/2001/09/17http://www.sqlservercentral.com/articles/Basics/usinguniqueindentifierinsteadofidentity/439/