March 5, 2020 at 4:05 pm
Hi,
while looking for new position I see beside DBA and SQL skills(SSRS, SSIS, Powershell ..) company adding "database design/modeling "
Question:
What tools currently you use on daily basis for physical and logic database modeling
I used to model in Erwin, but might be the is new less costly and more simple tools used by small -medium size companies on market right now
Thank you
March 5, 2020 at 6:03 pm
Visio, Excel, pen and paper. And a whole boatload of questions
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
March 5, 2020 at 7:50 pm
And a whiteboard, while I'm thinking my way through it. But #1 tool is my brain.
March 5, 2020 at 8:34 pm
ERwin is great, one of the best tools I've ever used, but it is expensive.
An excellent feature to look for is a "data dictionary" / "data element" component. That is, a place to document a piece of data separate from and above where it is stored. Take, for example, client_id. You'd want a place to document what it is, and its related rules, independent of any table or file.
Reverse engineering is also extremely helpful (forward engineering as well, but any tool should have that).
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
March 5, 2020 at 8:36 pm
The single most important thing to understand, though, is that data modeling is a logical process, not a physical one.
Physical things -- identities, files, etc. -- do not exist in a logical model. Storage, cpu, etc. are effectively considered infinite during the logical modeling. You'll deal with all that stuff only when you convert the logical model to a physical model.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
March 5, 2020 at 8:45 pm
Like others, I tend to use paper/pen. I often will get this moved to some electronic medium, like Visio, but it's a manual thing. There are tools, but they tend to be $$$$.
I've had a few people recommend ModelRight, but I haven't tried it.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 5, 2020 at 9:02 pm
Primarily pen and paper. But I have been using Apex SQLModel for the past year or so. It's a pretty good and it's free. For now anyway. https://www.apexsql.com/sql-tools-model.aspx
Sue
March 5, 2020 at 9:15 pm
My ears, my eyes, a yellow legal pad, a few #2 pencils, and a whiteboard.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 7, 2020 at 1:26 am
Very close to what Michael John posted, the most import tool of them all is... you brain. It totally doesn't matter what tools y0u use, whether or not you succeed or fail depends entirely on your brain. Period. You mention Erwin as a tool... absolutely wonderful tool when used by someone with a brain... absolutely worst tool when used by someone that doesn't get it.
And to both Michael's and Steve's points, you don't actually need a tool other than paper and pencil if you have the right brain for it. Tools don't make you smart... they make smart people faster.
--Jeff Moden
March 9, 2020 at 1:58 pm
Sure, you could do a design with only pencil and paper, just as you can eventually get across town by walking. But for anything but the most trivial design, you will do massively more work by hand than with a tool. Just producing whatever form of ERD your shop prefers is a ton of work without a tool to do it. And maintaining a data dictionary is even harder: so much harder, in fact, that it just won't be done, which will be a tremendous loss overall from the design.
I 100% understand the price issue, but, worst case, just pick the best free tool you can find.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
March 9, 2020 at 2:04 pm
Don't get me wrong. I totally agree with you on big stuff about pencil and paper not being the way to go. My point was meant to be that no tool is a replacement for the brain. SOOOOOOO many people think tools will do it all for you auto-magically and while some come close, you still have to keep your hand on the helm and, by that, I mean that if you tell it to do something stupid, it probably will.
To use one of your favorite examples, if you tell it to add an IDENTITY column to every table and then always use that as a Clustered Index, you've just used the tool to commit murder.
--Jeff Moden
March 9, 2020 at 2:12 pm
Yes, that is common but horrible, but at least it would not affect the logical design, which could still be forward engineered to proper physical form. Just keeping accurate logical vs physical models straight with pen and paper was very complex. We had to do it back in the day, but now you don't, there's some kind of tool you can use. Actually, post-it notes were our main tool then, very flexible!
Which leads back to the core issue again: most people don't separate logical from physical, and thus just can't do a proper design, period. There's a reason there's a separation.
I was taken off International Paper's (IP) logical design team simply because I had physical design experience: they decided they wanted only people who did not do physical designs, and had never really done so, on that team. I thought I had been distinguishing between the two, but they decided they wanted no overlap at all with physical designers. Not a bad approach really.
IP had (at least) 3 different major dbmses it used: we often didn't know until the logical design was complete which dbms it would be implemented on. But, for a proper logical design, that doesn't matter anyway, you could do a corresponding physical for 1, 2 or all 3 of the dbmses without changing the logical design. In some cases, we did do 2 different physical implementations.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
March 9, 2020 at 6:38 pm
A simple but free tool is ApexSQL Model:
https://www.apexsql.com/sql-tools-model.aspx
I've used it here and there before, it can do reverse engineering of a model from an existing database, as well as produce script to create a database from a model.
I currently use erwin, and for the longest time I did hold on to Visio 2010 before I got erwin.
March 13, 2020 at 1:38 pm
I'll give a second vote for Sparx Enterprise Architect. It includes multiple layers of modeling so you can do your concept, logical and physical all with the same tool. It gives you that oh so important documentation needed in these days of PCI-DSS, SOC and SOX.
Of course, before you ever get to that you still have to do the due diligence of getting the required data elements and arranging them into appropriate objects and attributes. So, you'll also likely use Excel, pencil and paper, endless meetings and hair-pulling (yours and maybe theirs) to get the appropriate info.
-RD
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply