[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

650 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