September 10, 2015 at 9:08 pm
Comments posted to this topic are about the item ER Modeling
September 10, 2015 at 10:29 pm
This is a great topic, mostly because answers to it will illuminate critical processes around database design and development.
You could actually ask an inverse question: how many of you take the time to back up your databases? Nearly 100%.
So how could so many companies pay so little attention to the core of their entire dev efforts? At many of the firms where I've worked, an initial ERD might be created but from that point on, rarely if ever is it updated.
I come from the opposite direction. I always create an ERD to start my design/dev work, and always keep it updated by first revising the ERD and using the modeling tool (ErWin, Xcase, ERStudio) to generate the DDL, etc. It takes extra discipline to always do it this way, since when there's a crunch, the tendency is to go straight for the end zone...but there's always a future cost.
For me, since I work on complex models, it is far easier for me to look at one of my diagrams for table structures/relationships instead of walking down a long list of table names in SSMS.
September 11, 2015 at 12:27 am
Interesting one. We only use SSMS, and use the database diagram feature to visualize the ER-diagram. To back up the diagrams I use a script, that’s generate DDL-script to restore the diagrams, and of course a full backup of the database. I am pushing our team to start using an ER-tool to maintain the databases.
September 11, 2015 at 1:13 am
The whole point of these models is that they are communication tools and I've found that people generally want one and curse the fact that one doesn't exist but are rarely prepared to pay for them in either manpower, time and the tooling cost to do it properly.
Then there is the level at which the diagram is pitched.
The latter can be reverse engineered from the physical database provided you have a disciplined environment setup process.
The logical model takes discipline, tooling and skills. Having tried the disciplines I found it immensely valuable in driving conversations at technical and business level. The problem comes in that such data disciplines have to be championed at a senior level and the value recognised. Otherwise when the person driving the use of the model leaves the disciplines are not maintained and the ERD eventually falls into disuse.
It is the same with a data dictionary or metadata thesaurus. Anyone who has used one realizes the immense value but the organizational maturity required to adopt its maintenance as simply a BAU activity is far higher than most organisations aspire to.
September 11, 2015 at 1:17 am
I have always kep Visio diagrams of our databases as part of the systems documentation, using the reverse engineering feature of Visio to create them.
However Microsoft have ruined Visio by now removing that feature from the product just as they did with the forward engineering that was in the original Visio product that they bought and was a great tool for database designers.
September 11, 2015 at 1:35 am
My 2 Cents, when working with large and often complex schema, cluttering a detail level ERD into a single diagram does neither make sense nor serve any purpose (apart from underlining the job's complexity to the management;-) ). Maintaining a high and medium level ERDs with smaller detailed ERDs reflecting (blow-up) the HLD/MLD elements makes much more sense, is easier to maintain and more legible.
😎
September 11, 2015 at 2:32 am
I find I have to very disciplined to make sure the development environment and ERD are kept in sync. The thing that makes it awkward is that you may update the ERD to give increased or additional functionality and then find, when implementing, that a change such as an additional field or different data type is needed.
I did get very annoyed recently when someone spotted two tables (outside the area they were working on) were not in sync (development and ERD) and escalated it. A real political animal (idiot actually).
September 11, 2015 at 3:47 am
Our main application was put into production in 1999. During development the developers kept a fairly updated ER-diagram. Efforts were also made the first few years in production to keep the ER-model updated. Since 2007-08 sometime nothing has been done to keep the model updated anymore. We still make numerous changes to the database every year, but we simply don't have time nor the resources to do this kind of work anymore.
September 11, 2015 at 5:33 am
We printed a big one out for our CEO awhile back to help him understand the major changes we made to the model. He put it on his wall and constantly referenced it when working with me on new analytical reports. He would simply point to what he wanted to reference and we made it happen.
It worked out pretty nicely until he memorized everything and had a solid understanding of the relations and multi-dimensional models. So now, that big print out is just rolled up and sitting in the corner of his office collecting dust.
For me, I've tried to maintain it with all my changes, but I have since given up. I don't really have any good tools that make it easier to manage or update other than Visio. I find that Visio is difficult for large diagrams that include multiple fact tables and dozens of dimension tables.
I just try to do my best and document what I can including updating the metadata along with providing executable scripts (i.e.: script table to SQL) that allow you to recreate the model easily.
I keep reminding myself that I need to find a good tool to help me maintain and plan the model better such as possibly using Workbench or something, but always stick to straight text documents with lists instead.
September 11, 2015 at 5:58 am
Typically I start with a high level ERD and then work on the detailed one as I build out the DDL.
I think it's very important to have an up to date ERD since it maybe a long time between revisiting that particular database, or somebody else needing to work on it for the first time.
September 11, 2015 at 6:08 am
I have to use 'Dia' as it is free (open source) software. Like the previous comment about Visio I find that 'Dia' is quite awkward for the full schematic. It's OK if you are doing only a few related tables such as a Client and directly related information but layout is a pain when you get to the full layout - in fact I refer to it as 'Dire'.
Before I do any major changes I print out the various tables and lay them out on the boardroom table (when not in use 🙂 ). I just have to put up with the comments "Michael is doing his origami again" as it is easier to lay out everything in 'Dia' if you know beforehand where you are putting it.
September 11, 2015 at 6:36 am
No ERDs to be seen anywhere around here, but I don't sit with the dev team.
I just yesterday finished getting all Pubs/Distributors/Subscribers and their data paths into Visio for the boss. I new we had some re-publishing/daisychaining happening, but found some unexpected ones as well.
My diagram still needs to be "pretty-fied"...looks like a wiring schematic for my home AC unit more than a replication topology 🙂
------------------------------------------------------------------------------------------------Standing in the gap between Consultant and ContractorKevin3NFDallasDBAs.com/BlogWhy is my SQL Log File HUGE?!?![/url]The future of the DBA role...[/url]SQL Security Model in Plain English[/url]
September 11, 2015 at 6:36 am
Back in the day when our software process was more "waterfall" we did "formal" ER diagrams. Not so much anymore. We use the diagrams in SSMS to visualize the relationships between database entities, that's about it.
September 11, 2015 at 6:47 am
Eirikur Eiriksson (9/11/2015)
My 2 Cents, when working with large and often complex schema, cluttering a detail level ERD into a single diagram does neither make sense nor serve any purpose (apart from underlining the job's complexity to the management;-) ). Maintaining a high and medium level ERDs with smaller detailed ERDs reflecting (blow-up) the HLD/MLD elements makes much more sense, is easier to maintain and more legible.😎
This is a big, real issue for anyone working on large/complex dbs. I've used ErWin, ERStudio, and Xcase modeling tools and for me and my diagramming style, Xcase's ability to create drillable sub-models on a master model page is the cat's meow. Envision having subject areas of a database like sales, inventory, customers, suppliers, etc...each of those subject areas is a sub-model on the "surface" of the main model, all FK relationships retained at the main and sub-model levels and between sub-models...it's really a beautiful thing. Allows you to print/plot any/all, etc. The other products do this, just not as elegantly, IMO.
September 11, 2015 at 6:55 am
Hello from Brazil,
Yes we do. For the seventy something system we have here. We use power designer to help out and everything is a question of data administration. We have two persons who take care of that. They are the "Oracle" and the link between the business and "IT" world. So if you need to add an "People" table or create that num_orders column for your system, they are the ones asking the whys and hows. (Ex.: if we already have one sitting right there on the schema) or helping you to get the best modeling solution for the problem you have. They are also the responsibles to do an impact analysis of the changes. What if that column i think i do not need anymore is used in a table shared with other systems?
So in the data level they are responsible for nothing to break and to avoid the best they can we developers do not duplicate stuff. Good to have them.
Of course if you ask for the whole model you get a spyder web of tables and foreign keys you cannot remotely even read. Power designer comes handy again as if you ask for the model of your system you get a neat print (sometimes one or two A3 sheets) with a readable model.
Regards,
Marcelo
Viewing 15 posts - 1 through 15 (of 53 total)
You must be logged in to reply to this topic. Login to reply