display ER diagram and quickly find out problem schema design problem

Dear all,

start to learn on how to find out schema/ talbe design problem of MS SQL as from time to time developer will come and ask.

So please suggest what kind of tools you guys use to find out table design problem, dbforgo studio ? toad for MS SQL ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior DBACommented:
Hi,

If a MS development shop, see if your MS Office licence includes Viso.

Viso can interrogate a database and suggest an ER diargram.

Regards
  David
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
MS Viso can connect to MS SQL and generate the ER diagram?
0
David ToddSenior DBACommented:
Hi,

Yes. Visio can suggest an ER diagram. Of course, splitting onto different pages is something you'll have to do. But Visio gives the tables and columns etc. It can give relationships based on Foreign Key constraints - if there are no constraints then there is nothing for it (or you or other tools) to really suggest that there is a relationship between tables.

But just double checking, it appears that you can't easily do this with Visio 2013.

Do you have (individually or the company) a MSDN Subscription? There used to be a specific version of Visio in Enterprise Architect.

HTH
  David
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ste5anSenior DeveloperCommented:
Keep in mind: an entity-relationship model contains semantic desciptions. When they are not stored in a separate database or the extenend properties, then it's not possible to generate an ER from the database schema.
0
marrowyungSenior Technical architecture (Data)Author Commented:
David,

"But just double checking, it appears that you can't easily do this with Visio 2013."

what version of Visio can do it?

What other tools  you can suggest ( free of charge perferrable ) , can Toad for MS SQL do this ?

"Do you have (individually or the company) a MSDN Subscription? There used to be a specific version of Visio in Enterprise Architect."

I don't have, so that one is called Visio for Enterprise architect ?

that one will give suggest on what is the problem it found out and what should we do ?
0
ste5anSenior DeveloperCommented:
btw, I'd like RedGate SQL Dependency Tracker. It's a nice and simple tool to inspect database schemas.
0
marrowyungSenior Technical architecture (Data)Author Commented:
ste5an,

so that one can tell Schema problem and give suggestion ?
0
ste5anSenior DeveloperCommented:
No. This is only possible when you have a complete set of requirements. I don't know any tool which can do this.

But as a simple rule of thumb: Examine the schema and data for possible violations of the normal forms. And discuss this with the developers.
0
David ToddSenior DBACommented:
Hi,

For Visio 2007
http://office.microsoft.com/en-nz/visio-help/create-a-database-model-also-known-as-entity-relationship-diagram-HA010115477.aspx

So you can see that I'd assumed this feature was still in existence, but seems to have been culled.

Regards
  David
0
marrowyungSenior Technical architecture (Data)Author Commented:
david,

But that one don't give suggestion, right?

it just show that out !
0
Anthony PerkinsCommented:
So just so that I understand you are looking for a tool that not only displays the current ER diagram, but also shows any problems with the design as it stands?  if so, can you elaborate what you mean by problems?
0
marrowyungSenior Technical architecture (Data)Author Commented:
"So just so that I understand you are looking for a tool that not only displays the current ER diagram, but also shows any problems with the design as it stands?"

yes, this is what I mean and that's why the vendor want us to buy their tools.!

I know dbforgo studio help developer on this area but I am not sure how far it goes .

learn from tools can learn fast !
0
Anthony PerkinsCommented:
Again what specific problems are you hoping this tool will show?  Since you have not told us what you are looking for it is difficult to know for sure.  

As far as I know dbForge Studio and their Database Diagram Tool for SQL Server does not display any problems whatsoever.  I just looked at their website again and I see no indication of anything remotely like that.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"please suggest what kind of tools you guys use to find out table design problem, dbforgo studio ? "

so therefore, I am looking for a tools that can show schema/ER/table design problem.
0
David ToddSenior DBACommented:
Hi

As above, I've used Visio. It is a tool, not a magic wand. I helps me see issues; it doesn't show me issues.

Also I'm in need of a new tool as MS have removed this from Visio.

Others have suggested tools they use. See if there are trials and give them a test.

Regards
  David
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Also I'm in need of a new tool as MS have removed this from Visio."

WHY ????

ok, any good book for me to learn what is a good table design ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
or any URL for that learning is good !@
0
ste5anSenior DeveloperCommented:
C.J.Date's books  are pretty good ones.
0
marrowyungSenior Technical architecture (Data)Author Commented:
tks, I take a look but there are a lot of them, which one you recommend ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
it is sad that no tools on earth can help quickly pinpoint the problem of schema design.
0
Anthony PerkinsCommented:
As previously mentioned a magic wand has not been invented yet.  Or to put it another way, unless there are simple problems like a circular reference, it is non-trivial or impossible, depending on what specific problems you are trying to address.  And there again lies the problem, you have not stated what specific problems you are trying to address.  So until you can produce a coherent list of requirements or at the very least specifications of what you expect to get out of it, it is a tad difficult to even speculate.  Stating that you need a tool that "can help quickly pinpoint the problem of schema design" is telling us nothing.
0
marrowyungSenior Technical architecture (Data)Author Commented:
Anthony Perkins,

they all understand what I am saying and they suggest books too !

I like them.
0
marrowyungSenior Technical architecture (Data)Author Commented:
what I can see is the Toad data modeler can do sth on it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.