The original URL was <a href="http://dbpatterns.com/" rel="nofollow">http://dbpatterns.com/</a>, which no longer points to the right content, so we replaced it with the closest archive.org copy of the original.
Tip for SQL users:<p>If you give all your ID fields unique names, e.g. by calling your field "reservation_id" instead of "id", even in the reservation table, you can do stuff like:<p>SELECT * FROM reservation JOIN guest USING (reservation_id);<p>By doing "USING (reservation_id)" instead of "ON reservation.id = guest.reservation_id", the field will be automatically deduplicated, so you don't have to qualify it elsewhere in the query, and "SELECT *" will return only one copy.
It's an old-looking website, but I've found this site has some really cool data models:
<a href="http://www.databaseanswers.org/data_models/index.htm" rel="nofollow">http://www.databaseanswers.org/data_models/index.htm</a><p>As someone in the healthcare space, looking at some of these models gives me a better idea of how various aspects of the healthcare industry work, and the things they interact with. Ex) <a href="http://www.databaseanswers.org/data_models/patient_data_warehouse/index.htm" rel="nofollow">http://www.databaseanswers.org/data_models/patient_data_ware...</a>
I just clicked on the second "featured" pattern and found this hot mess:<p><a href="http://dbpatterns.com/documents/5091f74289cbad03bc958bc0/" rel="nofollow">http://dbpatterns.com/documents/5091f74289cbad03bc958bc0/</a><p>It has the "let's put a UUID on every row" disease common to designers who have never really learned anything other than object oriented design. Price is a string (I guess so you can put "market price" on the fish?), and there's a currency symbol on every "delivery". The whole thing just makes no sense. 18 stars. Unbelievable.
Kinda misleading title. Doesn't really have anything to do with database design. It has to do with sharing Entity-Relationship diagrams. Maybe "Design better relational models".
If anyone is interested, there is a 2-volume set of books called "The Data Model Resource Book". They've been around for a while, so for more traditional businesses, but very thorough and broken out by industry:<p><a href="http://www.wiley.com/WileyCDA/WileyTitle/productCd-0471380237.html" rel="nofollow">http://www.wiley.com/WileyCDA/WileyTitle/productCd-047138023...</a><p><a href="http://www.wiley.com/WileyCDA/WileyTitle/productCd-0471353485.html" rel="nofollow">http://www.wiley.com/WileyCDA/WileyTitle/productCd-047135348...</a>
More of an ER modeler than a database designer. The interface is pretty slick/simple. Here's a sample I just cooked up: <a href="http://dbpatterns.com/documents/570699101514b428de8893a1" rel="nofollow">http://dbpatterns.com/documents/570699101514b428de8893a1</a><p>I can't imagine using it for anything real but it was fun to play with.<p>If the creator is reading this, here's a bug report: If the user hasn't hit "Save" when they export the schema, they get an empty text file and they'll think the app is broken. It should either auto-save or prompt the user "<i>You have unsaved content, do you want to save before exporting?</i>"<p>UPDATE: One more bug report, the export doesn't escape the columns names at all. It just naively adds double quotes around them. This breaks with double quotes in field names (<i>yeah yeah which is stupid but still..</i>):<p><pre><code> CREATE TABLE "foo" (
"id" int,
"baz" varchar(255),
"bar" varchar(255),
""test"" varchar(255)
);</code></pre>
Y'know I really miss designing relational schemas. As much as I hate SQL the language I love the relational model that it is a (butchered and ugly and compromised) implementation of. But working where I do on the systems I do now this is something I never have to deal with anymore.<p>There's something very therapeutic about organizing data using a system of rules, and the relational data model is a powerful one.
Looking around I see lots of people just blindly using UUIDs for everything. At a prior job, I had a boss who forced me to use UUIDs for everything, before we had even collected a single row of data.<p>It hurts my pragmatism to solve a distributed systems problem before we even had a distributed system! Don't be a sheep and use UUIDs, rather than a simple integer primary key or composite key that is natural to the table. People say it's just an id, who cares? But I say people making these kinds of decisions are probably making other poor choices backed by something they read and don't understand.<p>Here's a great article on locality and the need for uniqueness and why UUIDs should only be introduced when needed and to solve very specific distributed systems problems. The reality is almost all applications will never need to have multiple concurrent writers.<p><a href="https://eager.io/blog/how-long-does-an-id-need-to-be/?hn" rel="nofollow">https://eager.io/blog/how-long-does-an-id-need-to-be/?hn</a>
I built an electron app that allows you to design, generate, and share Mongoose Schemas if anyone finds it useful:
<a href="http://mongomulch.com" rel="nofollow">http://mongomulch.com</a>
These schemas seem like pretty simple, straightforward adaptations of specific problems. This doesn't actually look like any sort of actionable advice on how to design better schemas. Are we supposed to just osmose the knowledge somehow? I was expecting something more along the lines of recommendations like "foreign key relationships should always be indexed, nobody ever came up with a realistic example where they shouldn't! Why the hell isn't this the default behavior?"
Interesting idea but it the way it is currently done isn't that great.<p>Most of the patterns I've seen are obvious simple things but they are missing a lot of content like :
- What was the design's intended properties?
- Expected volumes, access patterns?
- Good places for indices?
Maybe the comments could be of some help but really haven't seen much.
I've actually found more interesting content in the HN comments than on the site itself.
I don't think this must be called "patterns" in computer science patterns are a model applied to give solutions to recurring problems. I was expecting something like Martin Fowler Patterns-Enterprise-Application-Architecture but for databases.<p>This should be called something like database designs.
One of the keys to understanding SQL, and on that note, I would highly recommend SQLZoo's interactive tutorial that allows someone to query a (fake) database and progress using baby steps:
<a href="http://sqlzoo.net" rel="nofollow">http://sqlzoo.net</a>
Cool website, for off the shelf models. For anything more advanced you should be careful, as modeling real world scenarios is rarely so abstract and mamy times you need to make some scenario-specific tradeoffs during the modeling phase to fulfill your requirements.
Looks like they’re trying to create a repo of open source DB schemas for domains.
That's putting the cart before the horse in my mind. People don’t start with DB schemas when building domains, they end up with DB schemas after modelling them.
I like this site. As simple as it is, it has a lot of potential community-wise.<p>- it could allow sql exports for various platforms
- it could feature an API so people can write their own framework drivers (for example creating migrations, importing existing schemas etc)<p>Lets hope that it gains users, so that the voting system (the star) can become more useful to filter out the garbage.<p>Edit: please remove the login wall to see the starred items, it raises the entry barrier quite a bit. Do you really want new visitors to see low rated schemas as the first thing?
As a database guy this makes me feel good. From looking at a lot of these schemas, my job is not going to disappear anytime soon...<p>Most egregious example for me is probably the prevalence of a lot of "type" tables when a simple enum column would do. Or maybe the sheer number of UUIDs that are being thrown around.<p>I even saw a circular ID chain in one. Would be fun setting up foreign key constraints for that!
When looking at an author, I can see all of their patterns. This one, in particular, has a pattern that was forked 13 times: <a href="http://dbpatterns.com/accounts/profile/thaichor/" rel="nofollow">http://dbpatterns.com/accounts/profile/thaichor/</a><p>How do you see the forks?
I was bored, so here is a quickly thrown together generic "thing" DB model: <a href="http://dbpatterns.com/documents/5706a53c1514b428de88940c" rel="nofollow">http://dbpatterns.com/documents/5706a53c1514b428de88940c</a>
I like it, I think there's a real use case for it. I spent quite some time looking for examples on how to create an schema for an activity based workflow webapp.
Kinda cool but the UX really needs some re-thinking.<p>Simple things like dialogs getting stacked on top of each other, using `prompt("...")` sometimes and modals other times, foreign key relationship arrows not indicating cardinality, etc --<p>Looks good but please don't consider it "finished" yet.
> Public Newsfeed<p>> jnichols created new pattern<p>> Penis<p>> 24 seconds ago<p>Ok... maybe they need to start with some patterns about filtering spam and noise.