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?
@TRAD's [link] 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. :
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.