<p>Does anyone know of papers/books/etc. that document patterns for databases?  For example, one common rule of thumb is that every table should have a primary key and that the key should be <a href="http://en.wikipedia.org/wiki/Surrogate_key" rel="noreferrer">devoid of information content</a>.  So I was wondering if anyone had written a book or published papers regarding design patterns for designing relational databases?</p>

<hr>

<p>@Gaius,</p>

<p>That is the question that a database designer needs to weigh--what is the probable stability of the database structure?  Given a long-enough horizon nothing is stable. Or to say the converse, given a long-enough horizon, everything is subject to change.  A surrogate key (in theory) should never change its meaning because it never had meaning to begin with.  </p>

<p>I guess the other thing to consider in that particular design scenario is who is it that will be seeing the primary key?  If the primary key is something that end-users will actually need to refer to then it makes sense to make it something they can understand.  But I can't think of many cases where an end-user needs to see a primary key; usually the primary key is present to allow the DB engine to speed up certain operations.</p>

<p>My original thought in asking the question was to find design patterns for database design that were codified by more experienced database designers than myself so as to, hopefully, avoid some easily avoidable errors.  It would be interesting reading if anyone had ever codified database design anti-patterns. </p>

[{'answer_id': 44304, 'author': 'hometoast', 'author_id': 2009, 'author_profile': 'https://Stackoverflow.com/users/2009', 'pm_score': 1, 'selected': False, 'text': '<p>To answer exactly: <a href="http://www.amazon.com/s/ref=nb_ss_b/102-6537774-4068964?url=search-alias%3Dstripbooks&amp;field-keywords=database+design+patterns&amp;x=0&amp;y=0" rel="nofollow noreferrer">yes</a>.  There are s*-tons of info written on \'good\' database design.  Although youe example rule of thumb is certainly questionable.</p>\n'}, {'answer_id': 44314, 'author': 'Ethan Post', 'author_id': 4527, 'author_profile': 'https://Stackoverflow.com/users/4527', 'pm_score': 3, 'selected': False, 'text': '<p>Books by E.F. Codd and C.J. Date are the most obvious answers. I have not read this particular book but I am familiar with the authors, it is likely quite good.</p>\n\n<p><a href="https://rads.stackoverflow.com/amzn/click/com/1590597451" rel="noreferrer" rel="nofollow noreferrer">Applied Mathmatics for Database Professionals</a> by Lexx de Haan and Toon Koppelaars.</p>\n'}, {'answer_id': 44336, 'author': 'Kevin Fairchild', 'author_id': 3743, 'author_profile': 'https://Stackoverflow.com/users/3743', 'pm_score': 2, 'selected': False, 'text': "<p>Actually, I think the rule of thumb is typically to use a natural key rather than a surrogate whenever possible...</p>\n\n<p>So if I have, for instance, an Invoice table and an InvoiceDetail table, we can probably use InvoiceNumber as our primary key on the first one. It already exists in our data and (I assume?) would be unique.  For the second table, we are probably going to be stuck needing a surrogate key, however -- whether it's joined to Invoice number as composite or not.</p>\n\n<p>In any event, back to the original question... hometoast's link should get you started.</p>\n\n<p>-- Kevin Fairchild</p>\n"}, {'answer_id': 44388, 'author': 'James A. Rosen', 'author_id': 1190, 'author_profile': 'https://Stackoverflow.com/users/1190', 'pm_score': 1, 'selected': False, 'text': '<p>Using primary keys with business meaning ("natural keys") certainly has its merits, but it can make refactoring your database <em>very</em> difficult.  Use caution, especially if there\'s any reason to believe the database structure will change over time.</p>\n'}, {'answer_id': 44454, 'author': 'Troels Arvin', 'author_id': 4462, 'author_profile': 'https://Stackoverflow.com/users/4462', 'pm_score': 4, 'selected': True, 'text': '<p>Specifically, regarding keys: I strongly disagree with the strange idea that keys must be without meaning. In general, I consider a database a collection of facts; as soon as you start adding arbitrary numbers (like generated keys) and other irrelevant information into it, it should be a warning sign. I recommend <a href="http://www.intelligententerprise.com/030320/605celko1_1.jhtml" rel="noreferrer">this articly by Joe Celko</a> for more on keys.</p>\n\n<p>More general notes:</p>\n\n<p>Suggestions for schema designs/data models for different businesses:\nDavid C. Hay: Data Model Patterns: Conventions of Thought\nRather old, but there is a reason why it\'s still in print\n<br /><a href="http://www.dorsethouse.com/books/dmp.html" rel="noreferrer">http://www.dorsethouse.com/books/dmp.html</a></p>\n\n<p>Maybe not very pattern-like, but still very good:\nStephane Faroult, Peter Robson: The Art of SQL\n<a href="http://oreilly.com/catalog/9780596008949/" rel="noreferrer">http://oreilly.com/catalog/9780596008949/</a></p>\n\n<p>Another one which I can recommend:\nVadim Tropashko: SQL Design Patterns - The Expert Guide to SQL Programming\n<a href="http://www.rampant-books.com/book_2006_1_sql_coding_styles.htm" rel="noreferrer">http://www.rampant-books.com/book_2006_1_sql_coding_styles.htm</a></p>\n\n<p>Systematic text-book about data modelling:\nGraeme Simsion &amp; Graham Witt, "Data Modeling Essentials"\n<a href="http://www.elsevierdirect.com/product.jsp?isbn=9780126445510" rel="noreferrer">http://www.elsevierdirect.com/product.jsp?isbn=9780126445510</a></p>\n\n<p>Maybe you are actually looking for a "style guide"?. I that case:\nJoe Celko: SQL Programming Style\n<a href="http://www.elsevierdirect.com/product.jsp?isbn=9780120887972" rel="noreferrer">http://www.elsevierdirect.com/product.jsp?isbn=9780120887972</a></p>\n'}, {'answer_id': 13140744, 'author': 'memilanuk', 'author_id': 359030, 'author_profile': 'https://Stackoverflow.com/users/359030', 'pm_score': 2, 'selected': False, 'text': '<p><a href="http://pragprog.com/book/bksqla/sql-antipatterns" rel="nofollow">SQL Anti-Patterns</a> by Bill Karwin is very easy to read (not dry) and explains in fairly clear terms a number of different potential pitfalls, how you might find yourself using them, and how/why to do things right.</p>\n'}]
['https://Stackoverflow.com/questions/44281', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2820/']