• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 508
  • Last Modified:

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

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.

  • 2
2 Solutions
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.
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.

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.

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now