DB Design Methods
-
March 30, 2026 at 10:44 pm
I've read a few posts regarding what we use to design DB models and document them, and I am curious about what notations/conventions you all use, especially with pen and paper, as you draw out your tables, procs, triggers, functions, etc.
I am working on a new and (to me) large model, with many procs, triggers, etc. I must admit that I used AI to help me design this model as I am not that experienced. I ended up with a lot of things that I don't understand and sometimes lose track of which thing does what and when.
So, I figured I should ask the experts.
- How do you notate, track, and model on paper?
- How granular do you get?
- And how can I best utilize Excel with the paper modeling?
Also, it just came to me how do you organize your tables on paper?
Thanks in advance!
- This topic was modified 2 days, 3 hours ago by mjdemaris.
-
March 31, 2026 at 12:43 pm
Wow! Someone actually designing a database. Cool!
There are modeling tools out there that can do a relational diagram. I work for Redgate and we sell this one. The core concepts here are the same as with any of the tools out there. Show tables, and their relations, one to many, etc. Get the data types. Get the primary keys, unique constraints, and foreign keys. All that together is your data model. Some tools will likely add in views since they function as tables. It's all that. But not on paper. Can you do the same on paper? Yes. Just look at the general notation and you'll see how to do it.
How granular? You can go pretty deep, but usually, tables, data types, keys, is enough. Indexes is a nice to have (except again, keys & unique indexes). Same with constraints. After that, procedures, functions, triggers, etc., I wouldn't, personally.
I've never used Excel to do this so, not a clue. I probably wouldn't. It doesn't have an easy way to show relationships, and that's most of what you need.
So, yeah, no paper. I first started building databases this way back in the 90s. It works. It's just, most modern systems don't do it any more. However, a tool like Data Modeler can be great for documentation, even if the database was build using an ORM tool or some other code-first approach, even an AI generated database.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
March 31, 2026 at 8:40 pm
Yeah, I am pretty much designing, building, maintaining on-premise DB's for our processes. Corp has given each plant some freedom to handle data storage, tracking, HMI integration how we want. Unfortunately, that means we don't have a lot of capital to invest in third party tools, unless there is enough value to justify it and we can sell it to the decision makers.
As for me, my learning comes from training videos, YT and forums like this. And AI. But AI can only go so far and makes quite a few confusing mistakes.
Maybe my process is off a little, maybe my approach is all wrong!
So, how do you map out the procedures, functions, triggers as you design?
-
March 31, 2026 at 9:53 pm
Those are usually combination of needs from the app married with knowledge of the model. Models are insanely helpful when developing. It's just that most places have gone so hard on code first, usually, the model is only documentation after the database is built.
Big thing for procedures, views & functions though is source control. Start working with that early so you learn it. It's the basis for automation of deployments & upgrades & such. However, it has benefits all on it's own. Tracking who made the changes, what were the changes, that even has value if you're the only person working on the db. "What did I do last week again?" Source control is huge.
There's no wrong way to go about this as such. If you make a database and an application and it works, you're winning. Biggest thing is security. Second biggest is backups to ensure, in the event of an emergency (and they happen) you can recover the data. After that, it's a mishmash.
One suggestion though, get a copy of Louis Davidson's book on database design. I know no one learns from books any more, but, I'd still suggest it. Louis really knows what he's doing. It'll be helpful.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
March 31, 2026 at 9:54 pm
Otherwise, for pointers, look up my name and "Database Lifecycle Management." There's a free e-book out there somewhere. It's a bit long in the tooth, but still pretty applicable on the basics.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
