7 ms·
> secondary primary key This is called a "candidate key" in existing literature. much has been written about such things. Both UUID's and auto ID's are "surro
by platz 9y ago
> secondary primary key
This is called a "candidate key" in existing literature. much has been written about such things.
Both UUID's and auto ID's are "surrogate keys" because they are arbitrary with respect to the data.
lastly, "natural keys" are combinations of columns that consist of the business data.
- squeaky-clean 9y agoI'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...
- tomcam 9y agoThis is super helpful for a mediocre devops guy like me. I had thought through all the issues in the article but didn't know if it was prudent to follow these principles. Now I know what to look for in the literature.
- dghf 9y ago> > secondary primary key > This is called a "candidate key" in existing literature. much has been written about such things. To be picky, a "primary primary" key is also a candidate key. A superkey is any set of columns the values of which are guaranteed to be unique in any row in a given table; a candidate key is a minimal superkey (i.e., if you remove any column from the key, you destroy its uniqueness). (Relational purists will want to read "attribute" for "column", "tuple" for "row", and "relation" for "table".) From a logical perspective, the designation of a candidate key as the primary key is more or less arbitrary; as per this article, that may well not be the case from a physical perspective.