December 1, 2018 at 9:33 am
Good topic! I've been databases with miserable naming conventions. Names so short that it was impossible to discern what the table or column names were for. Unfortunately, I've been involved in making such tables, against my will. But I think a lot of that was due to what the tooling was like in the past. I've been using SSMS, even Enterprise Manager (I think that was the name of the UI for SQL Server before SSMS), where there wasn't any Intellisence when writing T-SQL code. Therefore, the shorter the names of tables and columns, the easier it was to write T-SQL. Thank God for newer versions of SSMS and Azure Data Studio! And I've seen databases that are easy to discern what the tables for and the columns are for. But this is something I still struggle with. Glad you brought it up, Steve.
I've never used ERWin, but colleagues of mine have. At my current job they really love TOAD for SQL Server, so I've got that on my work desktop. It seems nice. I like how, if the database has PK's and FK's it can document them, generate a diagram of relationships, etc.
Rod
December 1, 2018 at 12:17 pm
roger.plowman - Friday, November 30, 2018 9:26 AMFrom an existing database? Yes, absolutely. Anything in the database that was created with DDL scripts can be automatically reverse engineered into a new ER diagram.This includes:
*Table and column names (including any table/column comments that used the MS_Description extended property)
*All table constraints
*All column constraints (including defaults)
*All indexes (including clustered key index, and all non-clustered indexes)
*All table relationships (including cardinality)
*Database rolesThe things you have to add after the reverse-engineering creates the ERD include any domains (user defined data types), comments (both notes (ERD only) and comments for anything that didn't have a MS_Description extended property.
Once you have a nicely commented ERD ModelRight can then turn around and create an empty database with the original schema plus notes that can be viewed with SSMS.
In my opinion ModelRight is absolutely worth buying, but be aware if you want to get the full benefit there's a fairly steep learning curve and the documentation on the program is only meh.
Thanks for the reply and suggesting ModelRight! I am seriously considering getting a license for that, since as a beginner that will help me tremendously in a professional setting. You mentioned the learning curve is steep, can you recommend any resources (besides the official manual) that are great learning and training resources for this? Thanks again!
December 3, 2018 at 6:27 am
Just remember that "all relationships" are normally based on the presence of FKs. A whole lot of 3rd party vendors don't even know how to spell "FK". I've not worked with "Model Right" but, unless it interrogates both database and managed code for JOINs and WHERE clause relationships (especially for APPLY and other types of correlated subqueries), a lot of so called "Reverse Engineering" data model programs still end up relying on FK relationships, which may fall woefully short of the actual relationships established in code.
--Jeff Moden
December 3, 2018 at 6:43 am
primitivefuture2006 - Saturday, December 1, 2018 12:17 PMThanks for the reply and suggesting ModelRight! I am seriously considering getting a license for that, since as a beginner that will help me tremendously in a professional setting. You mentioned the learning curve is steep, can you recommend any resources (besides the official manual) that are great learning and training resources for this? Thanks again!
No, I'm sorry, there isn't any that I could find. Conceptually ModelRight resembles Erwin or Rational Rose, and is a bit like Toad data modeler, if that's any help. I've used all of the above and ModelRight compares favorably to Rational Rose, at a fraction of its price.
The basics of ModelRight are pretty straight-forward, especially reverse engineering an existing database. It's when you get into the customization of the tool and the more advanced features that the documentation really falls down. ModelRight already assumes you have a solid grounding in your database of choice (such as SQL Server) and doesn't bother to explain a lot of the vocabulary of database design, i.e. relationships, domains, etc. If you're a beginner in database design I would recommend getting a solid book on "Database Design for Dummies" kind of thing.
That will give you the background you need to really dig into ModelRight's more advanced (read labor saving) features.
Don't get me wrong, even for non-database people ModelRight's reverse engineering feature and its defaults go a LONG way toward creating a good data dictionary/documentation tool. If that's all you need then the curve is shallower. But if you want to design a database from scratch the ROI of learning the more exotic features of ModelRight will be enormous.
The few times I've reported bugs or asked about some feature I really needed their tech support was fast, helpful, and very friendly. Thus it's one of the only tools I bother with keeping the yearly tech support subscription to. A bit pricy but when you need it you REALLY need it. 🙂
They do seem to have a disdain for documentation though, the one time I suggested a book they said it wasn't a priority for them. Might be a good opportunity for a technical writer to make some money on the side!
December 3, 2018 at 6:49 am
Jeff Moden - Monday, December 3, 2018 6:27 AMJust remember that "all relationships" are normally based on the presence of FKs. A whole lot of 3rd party vendors don't even know how to spell "FK". I've not worked with "Model Right" but, unless it interrogates both database and managed code for JOINs and WHERE clause relationships (especially for APPLY and other types of correlated subqueries), a lot of so called "Reverse Engineering" data model programs still end up relying on FK relationships, which may fall woefully short of the actual relationships established in code.
Heh.
While I agree with you, relationships that rely on code aren't actually relationships, developers ideologies be damned. "Relationships" that are enforced by stored procedures (or shudder actually coded in the front end) don't get captured by ModelRight's reverse engineering. It does do views though, IIRC.
December 3, 2018 at 7:37 am
roger.plowman - Monday, December 3, 2018 6:49 AMJeff Moden - Monday, December 3, 2018 6:27 AMJust remember that "all relationships" are normally based on the presence of FKs. A whole lot of 3rd party vendors don't even know how to spell "FK". I've not worked with "Model Right" but, unless it interrogates both database and managed code for JOINs and WHERE clause relationships (especially for APPLY and other types of correlated subqueries), a lot of so called "Reverse Engineering" data model programs still end up relying on FK relationships, which may fall woefully short of the actual relationships established in code.Heh.
While I agree with you, relationships that rely on code aren't actually relationships, developers ideologies be damned. "Relationships" that are enforced by stored procedures (or shudder actually coded in the front end) don't get captured by ModelRight's reverse engineering. It does do views though, IIRC.
Let's not mince words here, eh? Remember, the goal is to find out how the database works. The fact is that someone wants the relationship to exist by the nature of their code and you're not going to find out what that relationship is if the FKs to support the relationship aren't there. There are also "relationships" in code that simply don't need an FK especially when it comes to temporal data or things like names of people.
--Jeff Moden
December 3, 2018 at 8:21 am
Jeff Moden - Monday, December 3, 2018 7:37 AMroger.plowman - Monday, December 3, 2018 6:49 AMJeff Moden - Monday, December 3, 2018 6:27 AMJust remember that "all relationships" are normally based on the presence of FKs. A whole lot of 3rd party vendors don't even know how to spell "FK". I've not worked with "Model Right" but, unless it interrogates both database and managed code for JOINs and WHERE clause relationships (especially for APPLY and other types of correlated subqueries), a lot of so called "Reverse Engineering" data model programs still end up relying on FK relationships, which may fall woefully short of the actual relationships established in code.Heh.
While I agree with you, relationships that rely on code aren't actually relationships, developers ideologies be damned. "Relationships" that are enforced by stored procedures (or shudder actually coded in the front end) don't get captured by ModelRight's reverse engineering. It does do views though, IIRC.
Let's not mince words here, eh? Remember, the goal is to find out how the database works. The fact is that someone wants the relationship to exist by the nature of their code and you're not going to find out what that relationship is if the FKs to support the relationship aren't there. There are also "relationships" in code that simply don't need an FK especially when it comes to temporal data or things like names of people.
Can you give an example for the names of people? I'm not quite following how this would be a relationship?
December 3, 2018 at 12:02 pm
While on the topic of ISV databases with incomprehensible data models, PeopleSoft comes to mind.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply