DB Schema - Differing teminology? DB diagram tools 1

[1DMF]

Hi,

I have been asked to put together a DB schema document detailing our database, including PK/FK , fields, data type and usage.

I did a hunt on the web and some recommended using the SQL server studio database diagram function.

However, this looks like i would actually be setting functional dependencies, entity relationships etc.

This isn't what i want to do.

Is this tool just a drawing program to show the relationship or does this physicaly set the relationship?

Also where do I type next to each field the field usage and other information?

Am I and the boss getting terminology mixed up?

Is it a schema he wants?

Advice on how I produce this easily and the best tools to use is appreciated.

The other alternative is to manually create this in a word document, but i kinda hoped there was an easier more efficient way of doing this.

Thanks,

1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads

 

[gmmastros]

If you use SQL Server Management Studio to create the drawing, and you draw lines between related tables, it will create foreign key relationships for you.

Sometimes this is a good thing, like when you are designing tables. This is not so good if you are just trying to show the relationships where no "formal" relationship exists.

Of course, if you have relationships in your DB that are not "formal" relationships, you should probably make them that way. Specifically, you should define foreign key relationships so that you can make sure your data is accurate. This is tricky to do after the database has been in use for a time because it's possible that you have orphaned child records in some of your tables. So, to create the foreign keys you would first need to make sure that your data is ok before creating the relationship. You'll also need to take a close look at your queries to make sure they are not causing problems.

For example, suppose you have an orders table, and another table for OrderItems. The OrderItems table would have a column for the OrderId. You certainly wouldn't want to have order items with an OrderId that does not exist in the Order table. If there are no foreign keys and you want to delete an order, you can delete from both tables in any order you want. If you have foreign keys set up, and you want to delete an order, you need to delete the OrderItems first, and then delete from the Orders table.

With foreign keys, you can define them in such a way that they can cascade. Specifically, with a cascade delete foreign key, you would only need to delete from the Orders table, and the SQL Engine would automatically delete from the order items table for you.

There is another benefit to Foreign Keys too. When you have them set up properly, they can actually speed up your queries because of optimization the sql engine can take advantage of under the hood.

Back to your question....

There are various tools that you can use to create your schema drawing. This link from Stack Overflow discusses several of them.

http://stackoverflow.com/questions/30474/database-schema-diagram-design-tool

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

thanks George.

I actually have found a program while hunting the web called dbSchema from sun microsystems that I am finding very usefull and really user friendly.

I realised as I was doing some FK definitions it tried to make the physical links and moaned due to duplicate keys / PK restraints(I was merely creating a link to a 'look up' table)

However, there is a tick box on the 'options' tab called 'virtual (only in DB Schema)' so no actual link is created and no alteration to the table is made.

There is also options to enure no action is taken on deletion / updates (no cascades).

Strange as it may seem we have some direct links from one table to another 'history' table, but just because the main record was deleted, we would still want to keep the audit trail (history) records, so cascading deletes in that scenario is not something we want. (bad design perhaps?)

So as long as I am careful and ensure I correctly make these settings it looks like this peice of software is doing the job nicely.

You are right, some of our tables are in a right state with theorhetical links and caluclated FK's not direct 'real' links.

There's crap been bolted on, no normalisation or correct functional dependencies defined.

Does any one have a totaly normalised DB with full PK/FK and FD definitions?

Plus remember, originally the DB was ported to SQL from Access and that was originaly just created by one of the default Access DB templates and then butchered over the years, so as the saying goes 'you cannot polish a turd'

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads

 

[1DMF]

- I just followed your link and that was the exact one I found!.

I tried

http://wwwsqldesigner

- but after unzipping and opening 'index.html' all I got was a grid with nothing on it and no way to draw?

That's when I then tried dBSchema and am loving it!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads

 

[1DMF]

Note: there are some bugs in this dbSchema software, it shows mulitple foreign key popups for the same FK, when you close the program and open it again, it shows correctly.

Also once you create an FK you cannot edit the options they seem permenently ghosted so you have to delete the FK and create it again to correct mistakes or change the type of link.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads