asked
Design Database Diagram
Hi, I have recently joined new SQL database project and this database having 1000 tables but no any primary key, Foreign Key and any other constraint available. I want to design database diagram for understanding the table references and work flow but I do not understand which software tool should I use and how that software will help me to design database diagram? All database tables have reference column available in each related tables but database constraint is not created instead of this they are handling on application level. I want to clear that we can not go for create constraint on database just I want to create diagram for understand the related tables and modules references. From 1000 tables small-2 group of tables are related to particular business requirement process e.g. Group of tables- (Tables1, Table2, Table3) ---Related to Billing (Table2, Table4, Table5) -- Related to Sales (Table1, Table2, Table6)—Products Please help and suggest better way to handle this?
[HERE](
http://ask.sqlservercentral.com/questions/19683/what-are-good-erd-tools-and-why) I asked awhile back about ERD tools. One of the responses includes a link to a listing of early all the ERD tools and lists out the benefits of each tool.
The link didn't come through correctly. It's actually
http://ask.sqlservercentral.com/questions/19683/what-are-good-erd-tools-and-why.
1000 tables without primary keys & foreign keys?!? That is not a good design, you should try persuade the team to make a new design. SQL server is using foreign keys to optimize queries.
@amardeep Here's how:
http://www.scarydba.com/2010/11/22/do-foreign-key-constraints-help-performance/
@TRAD's [link][1] is, unfortunately, about my experience. I don't like the built-in database diagram generator for SSMS; it feels like a version-1 product that never got improved upon. I use Visio to reverse-engineer my databases and create pictorial data models, and this can be pretty good at picking up some missing constraints (whoops, there's a foreign key I didn't add--that type of thing), I would be afraid to throw a thousand unorganized tables at it. I haven't tried ERwin or Embarcardo, but they might be good to investigate. I agree with @Håkan Winther: it sounds like a pretty bad design. Actually, based on my understanding of your description, it kind of sounds like they started with an OK design (at least for people who don't know how to model databases very well), but then they added new customers. There wasn't any room for that and they made another set of tables for that new customer, and just repeated the process. [1]:
http://ask.sqlservercentral.com/questions/19683/what-are-good-erd-tools-and-why
Thank you, @TRAD, for the tagging assist. Apparently, I fail at Unicode...
@Kevin Feasel The only thing I don't like about the database diagram generator in SSMS is that you cannot generate a diagram without actually persisting the design back to the database objects. However, it is obvious that working on 1000 tables with any tool is going to be overwhelming anyway, so I usually stick with SSMS generator AFTER I script the tables of interest (schema only no data) to a separate instance first. This way I can work with native SSMS IDE and generate the diagrams and thus design table relationships and constraints the way there were supposed to be designed while picking only a subset of tables at once in order to avoid the aggravation caused by the clunkiness of the tool :) I have to admit though that Embarcadero tools are awesome, they really are, specifically for non-Microsoft databases which are known to lack their own decent set of IDEs.
I'm in a agreement with @Kevin Feasel regarding the database diagrame tool in SSMS. It stinks. I've used Embarcadero ERSTudio extensively. It's an excellent, if expensive tool. The problem you're going to hit, with any of these tools, is that they're not magic. You have a database without primary keys or foriegn keys. These tools rely primarily on referential integrity as a means for putting together a model of your database. No RI, no model. However, ERStudio does have the capability to look at column names as a mechanism for establishing relationships in the model. It's a very inaccurate approach, but it might get you where you need to go. I'll just pile on and say the same as everyone else. A system without primary keys and foreign keys is a system that is doomed to failure. I'll bet it doesn't have clustered indexes on most of the tables either. Your company needs some high-end help right away. I'd suggest strongly getting a strong consultant in there who is a data professional.
@amardeep I can't answer all that within 1100 characters. We're stepping up to the line of consulting here. I do that part time if your company would like help I can send you my rate. Here are a couple of comments. 1) You don't have to have an identity column to have a primary key. In fact, you need a business unique identifier more than an artificial key any way 2) Yeah, and how many data errors are there and how can they tell. If it's been running this way for 10 years, I'll put money down, it's a mess. Unless of course your developers write flawless code. If so, they should be making millions of dollars 3) If a table doesn't need an index, don't add one.
I imagine the performance is just oh-so-good as well ;) I feel sorry for anyone trying to tackle that and walking away successfully.
Thanks for your reply, This database is running from last 10 years and initially it developed on MS ACCESS that’s why they did not implement all those things but now I want to convince mine management to take necessary action on these for that purpose I want some strong reasons to explain the importance of all those things. Right now we have Indexes on some high volume tables where we got performance issue but not on all tables. So please help me and give some strong reason for below:- (1) As per existing design we do not have unique increment column in every table so how we can go for create primary key on all tables and Where Primary key will help in performance? (2) On existing system all validation is doing on application code so what will be the use of creating foreign key constraint on database and how it will increase performance of database even foreign key only for understanding of relation between tables and for enforce and validate the correct data. (3) What is the use of creating index on small volume data even it unnecessary required maintenance and db size.
