Solved

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

Posted on 2004-03-23
4
402 Views
Last Modified: 2006-11-17
Hello,
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.

Tks.
Ernest
0
Comment
Question by:ErnestLefriteur
  • 2
4 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
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.
0
 
LVL 4

Accepted Solution

by:
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.

0
 
LVL 44

Assisted Solution

by:Arthur_Wood
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.

AW
0
 

Author Comment

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

Bye.
Ernest
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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…

757 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

22 Experts available now in Live!

Get 1:1 Help Now