Could you list db modeling tools you use on daily basis?

Question

Could you list db modeling tools you use on daily basis?

ebooklub

Hall of Fame

Points: 3905
More actions
March 5, 2020 at 4:05 pm

#3731473
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
- This topic was modified 4 years, 8 months ago by ebooklub.

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

jonathan.crawford SSCertifiable Points: 6612 More actions · Answer 1

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

pietlinden SSC Guru Points: 63503 More actions · Answer 2

And a whiteboard, while I'm thinking my way through it. But #1 tool is my brain.

ScottPletcher SSC Guru Points: 101095 More actions · Answer 3

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ScottPletcher SSC Guru Points: 101095 More actions · Answer 4

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 5

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.

http://www.modelright.com/

frederico_fonseca SSCoach Points: 16154 More actions · Answer 6

I've used Sparxs EA Both as individual and within the corporation . Price isn't that bad

Sue_H SSC Guru Points: 90860 More actions · Answer 7

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

Michael L John One Orange Chip Points: 27054 More actions · Answer 8

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/

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 9

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101095 More actions · Answer 10

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 11

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101095 More actions · Answer 12

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 13

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.

rlDawson_SLC Valued Member Points: 61 More actions · Answer 14

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.

https://www.sparxsystems.com/

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