Comment by el_benhameen
2 years ago
Lots of interesting comments on this one. Anyone have any good resources for learning how not to fuck up schema/db design for those of us who will probably never have a DBA on the team?
2 years ago
Lots of interesting comments on this one. Anyone have any good resources for learning how not to fuck up schema/db design for those of us who will probably never have a DBA on the team?
Good question. We don't have a DBA either. I've learned SQL as needed and while I'm not terrible, it's still daunting when making the schema for a new module that might require 10-20 tables or more.
One thing that has worked well for us is to alway include the top-most parent key in all child tables down yhe hierarchy. This way we can load all the data for say an order without joins/exists.
Oh and never use natural keys. Each time I thought finally I had a good use-case, it has bitten me in some way.
Apart from that we just try to think about the required data access and the queries needed. Main thing is that all queries should go against indexes in our case, so we make sure the schema supports that easily. Requires some educated guesses at times but mostly it's predictable IME.
Anyway would love to see a proper resource. We've made some mistakes but I'm sure there's more to learn.
Not to pick on you, but is SQL not basic knowledge for every software engineer these days? Or have times changed?
Perhaps I undersold myself a little. By the time I got my first job I was fairly well versed in SQL querying, and these days I feel comfortable writing what I'd consider complex queries. That is with various window functions, nested queries, recursion (though I try to avoid that) etc, and I have a good handle on what the query optimizer likes and doesn't like.
But schema design is something else. I still take my time doing that.
Especially since our application is written with backwards compatibility in mind, so changing schema after it's deployed is something we try very hard to avoid.
But yeah, when hiring we require they are comfortable writing "normal" SQL queries (multiple joins, aggregation etc).
Times have changed. If you have C# programmers and they can't do it in Entity Framework/LINQ, then they can't do it.
4 replies →
> not to fuck up schema/db design
The neat thing is, you don't. Nobody ever avoids fucking up db design.
The best you can do is decide what is really important to get right, and not fuck that part up.
Wow, what an astute comment! Thank you!
P.S. to the original person concerned about this though… for your own sake and your successors, please keep trying.
Assuming that was sarcastic, you are free to try, I guess everyone needs to try it once.
Just do the exercise of deciding what is really important first, so you can make sure you succeed for that stuff.
1 reply →