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

Posted on 2004-03-23
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
  • 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 50 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 50 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Do we need servers??? 5 196
Update in Sql 7 37
Need a starter for ETL protocol? 4 55
Server configuration for MS Access - SQL Server app 8 69
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Creating and Managing Databases with phpMyAdmin in cPanel.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

813 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now