How SQL SERVER Database Diagram is important in feeding a dynamic website?

Posted on 2004-03-23
Medium Priority
Last Modified: 2006-11-17
How SQL SERVER Database Diagram is important in feeding a dynamic website?

I'm a newbie in MS SQL, coming from Access. Succeded in setting up 2 dbs for 2 different servers, imported my Access data fine and running apparently smoothly for a few weeks.
My sites are Coldfusion powered.
I created my 1st storedprocedure and seems ok too. So far so good.
Just researching and wondering what important setups in my dbs I should make to improve performance and/or to minimize bad incidents.
Was wondering whether making a Database Diagram and create the links between the keys would improve something or just don't any better as I double check that I always have a primary key in every table and the key linking is done in the query at the script level on the webpages.
Welcome any consideration from you guys.

Question by:ErnestLefriteur
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 44

Expert Comment

ID: 10661235
The Database diagram in SQL Server is the logical equivalent of the Relationships page in Access.  The Daigram wizard in SQL Server will establish the PK-FK relationships, and allow you to turn on Cascade Update and Delete, if you so desire.  However, most of that type of integrity management is better handled with Triggers in SQL Server, so that you, as the DBA have more direct control over the events.

Accepted Solution

xassets earned 200 total points
ID: 10662562
I wouldn't do a database diagram unless you actually need to print database diagrams

But you should create all foreign key and primary key relationships asap to ensure that database integrity is maintained. You don't need a diagram to do this although I guess it helps visually, personally I use home grown validation software to check that database standards have been implemented.

If you always have a PK in each table thats great. Make sure each FK has a relationship and that an index exists on every FK and every field you search on or join on (application of common sense also required). If you are always joining on INT datatypes and all your PKs are ints thats very good.

I wouldn't use triggers except in response to specific circumstances such as implementing unusual business rules. A fully normalised database should not require triggers. And I'm not keen on "cascade delete" relationships, better to get an error message if you haven't specifically coded for it in the application server. (Sorry Arthur, I have a long background in dealing with troublesome apps with business logic inappropriately embedded in sp's and triggers, maybe its got to me too much !)

If you start hitting performance or recompilation bottlenecks look at stored proc development to improve scalability.

LVL 44

Assisted Solution

Arthur_Wood earned 200 total points
ID: 10663472
As xassets points out, the Database Diagram is primarily for 'documentation', and having or not having one (several) will NOT affect your performance in any way, whatsoever.  I am working on a fairly large app (about 100 tables, about 400,000 records in the largest single table), and there are NO database diagrams (in SQL Server at least - the Diagrams were done externally with ERWIN), and it runs just fine.


Author Comment

ID: 10682073
Tks to both of you guys. This is really the statement I like to hear.


Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question