7 ms·
I've been using natural keys in a project recently without even knowing they were called that, or even that it was a pattern. Is there a book or any other read
by squeaky-clean 9y ago
I've been using natural keys in a project recently without even knowing they were called that, or even that it was a pattern.
Is there a book or any other reading you would recommend to be more familiar with these sorts of details? The longest text I could find about this was the c2 wiki. I'd like to know what else I don't know.
- timClicks 9y agoPretty much any undergraduate database course will discuss this early on. One of the first MOOCs #dbclass covered this in depth.
- squeaky-clean 9y agoMy database courses were pretty terrible, everything besides basic SQL syntax and some implementation specific stuff I've had to teach myself (some classes were pretty much an ad for MS SQL). And judging by many people I've worked with, I think this may be true more often than not. I think I've found the #dbclass series on YouTube, is it the one from Stanford? And thanks for the help.
- Dangeranger 9y agoYes, you want the course taught by Jennifer Widom of Stanford offered on Lagunita. Links below: https://lagunita.stanford.edu/courses/Engineering/db/2014_1/about https://lagunita.stanford.edu/courses/Engineering/db/2014_1/... https://lagunita.stanford.edu/courses/DB/2014/SelfPaced/about https://lagunita.stanford.edu/courses/DB/2014/SelfPaced/abou...
- brid 9y agoCheck out the Data Warehouse Toolkit. You might want to look into natural vs surrogate keys. https://www.amazon.com/Data-Warehouse-Toolkit-Complete-Dimensional/dp/0471200247 https://www.amazon.com/Data-Warehouse-Toolkit-Complete-Dimen...
- squeaky-clean 9y agoFound a pdf copy (just to browse, I promise!) and it looks great. Ordered a used copy, thanks. Edit: no instances of candidate key when I ctrl+f though, still looks pretty good.
- nkozyra 9y agoFundamentals of Database Systems (which is an academic textbook, check Abebooks) covers a lot of this along with underlying design. I'm going through it now for a DB class.
- matwood 9y agoVery rarely have I found natural keys that end up working long term. I always default to a single column surrogate and use natural keys as exceptions. As to your question, any introduction to RDBMSs book will teach all these concepts.
- awj 9y agoThe ultimate problem with natural keys is that their uniqueness is a property of business rules. Unless you live in some magical fairytale land where business rules are stable, sensible, or both, depending on them for uniqueness is asking to have problems.
- squeaky-clean 9y ago> Unless you live in some magical fairytale land where business rules are stable, sensible, or both, depending on them for uniqueness is asking to have problems. Thankfully mine are legally enforced, so it works well :)
- ksenzee 9y agoEven that is not a forever guarantee. Laws can and do change.
- squeaky-clean 9y agoDefinitely true, but at a certain point you need to draw a line. If these things were to change, the entire product would have to change, not just what I use as my keys, so I think natural keys are a safe bet in my case.
- dbenhur 9y agoAnd people act outside the bounds of the law. SSN seems like a good natural key candidate, until you discover some of the people your business interacts with are illegal aliens.
- Delphiza 9y agoAnything more recent by CJ Date. He was at IBM with EF Codd at the beginning of the development of the relational model. Try "Database in Depth: Relational Theory for Practitioners". It's good, when reading his stuff, to remember that SQL is a compromise to fit with a pure relational model, so you see things that make sense, but are not practical. Like choices for candidate keys, I suppose.
- sitkack 9y agoAll of the Joe Celko books and https://www.amazon.com/SQL-Design-Patterns-Programming-Focus/dp/0977671542 https://www.amazon.com/SQL-Design-Patterns-Programming-Focus...