10 ms·
Design better databases
- dang 7y agoThe original URL was http://dbpatterns.com/ http://dbpatterns.com/, which no longer points to the right content, so we replaced it with the closest archive.org copy of the original.
- zenogais 10y agoKinda 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".
- koolba 10y agoMore of an ER modeler than a database designer. The interface is pretty slick/simple. Here's a sample I just cooked up: http://dbpatterns.com/documents/570699101514b428de8893a1 http://dbpatterns.com/documents/570699101514b428de8893a1 I can't imagine using it for anything real but it was fun to play with. 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 "You have unsaved content, do you want to save before exporting?" 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 (yeah yeah which is stupid but still..): CREATE TABLE "foo" ( "id" int, "baz" varchar(255), "bar" varchar(255), ""test"" varchar(255) );
- karmakaze 10y agoThanks for your sample link. It's way more interesting than the feed of model snippets in the original link.
- astazangasta 10y agoI found the interface very off-putting. If this is a site about exploring data models, a "public feed" and "search" is not a great way to interact with those. In fact, a timeline chronology of when these things were made seems a great way to expose mostly irrelevant information to a large number of users. How do I browse these models? Is there a taxonomy of organization? How many models are in the system? Are there models relevant to general domains of interest to me? None of this is made apparent.
- zo1 10y agoI think they're aiming at the "jsfiddle" field. I.e. Sharing ERDs and such over SO and other social sites. As opposed to them trying to be some sort of comprehensive database of standardized DB patterns.
- abraae 10y agoThe social aspects of this are awesome. But the actual modelling functionality itself is really lacking. The lines joining the entities don't even indicate cardinality (does a ninja have many ninja_nelts? Or is it the other way around?). That's pretty much 101 for any visual database modelling. Also the lines overlap, meaning you can't see whether ninja_weapon is related to ninja, or to ninja_belt. I can answer these questions myself by perusing the foreign key columns - but that makes the lines redundant. Since several decades ago, Chen, Bachman, Grady Booch and a bunch of others have given ways to denote cardinality etc., and these are widely used. This tool should use one. (apologies in advance if I have this wrong and the tool does support this but the diagram authors elected not to use them).
- collyw 10y agoIsn't that how you design databases? Start with an ER model? Its easy to translate to database tables.
- clay_to_n 10y agoIt's an old-looking website, but I've found this site has some really cool data models: http://www.databaseanswers.org/data_models/index.htm http://www.databaseanswers.org/data_models/index.htm 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) http://www.databaseanswers.org/data_models/patient_data_warehouse/index.htm http://www.databaseanswers.org/data_models/patient_data_ware...
- bochoh 10y agoThanks for this, so much information here.
- sakopov 10y agoI was just about to post this. I've been referring to this site for years all the way since University days. Invaluable resource! Thanks for posting!
- elchief 10y agothose models are actually pretty bad the ones in these books are better: https://dba.stackexchange.com/questions/12991/ready-to-use-database-models-example/23831#23831 https://dba.stackexchange.com/questions/12991/ready-to-use-d...
- theli0nheart 10y agoThis strikes me as a very unhelpful comment. Why are they bad? Why are the ones in these books better?
- jqm 10y agoThe site (databaseanswers) is neat and I've gotten some good ideas from it in the past, but most of the models (that I've looked at anyway) are pretty simple and you almost really wouldn't need to look at a diagram drawn by somebody else to intuitively put something like that together. It seems almost more for someone creating MS Access level applications. Granted there may be some more complex schemas that I didn't see. There might be an argument about excessive normalization in some cases also. Take some of those layouts too far and try to extend them and you might wind up with tons of little tables. Normalization was I think more important back in those days (not that it's not still important... but some of the downsides of going overboard on normalization have become apparent I think.... at least to me).
- whatnotests 10y agoKinda cool but the UX really needs some re-thinking. 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 -- Looks good but please don't consider it "finished" yet.
- cmrdporcupine 10y agoY'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. There's something very therapeutic about organizing data using a system of rules, and the relational data model is a powerful one.
- dv35z 10y agoCompletely agree with you. I have always hoped that some of the GUI modelers would allow you to add notes/explanations on tables & relations, explaining the motivation behind design decisions. Example, "M2M relation here allows us to 'tag' objects with multiple attributes".
- raziel2p 10y agoMost of these seem extremely simple. I would like to see some examples where a good database schema isn't so obvious.
- klodolph 10y agoI just clicked on the second "featured" pattern and found this hot mess: http://dbpatterns.com/documents/5091f74289cbad03bc958bc0/ http://dbpatterns.com/documents/5091f74289cbad03bc958bc0/ 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.
- r0m4n0 10y agoAnd only one comment that says "thhx" haha Like any open advice community like this, its important there are checks in place to not perpetuate bad ideas...
- skrebbel 10y agoI'm daft. What's wrong with using uuids as primary keys everywhere? I'd appreciate it if you could elaborate :)
- clavalle 10y agoThere is no way to enforce data integrity between related tables by virtue of the data model for one thing.
- jtmarmon 10y ago?? foreign keys?
- billybugmaster 10y agoisn't that what the fk are for? food_uuid for example. I concede I wouldn't call it uuid but I don't see a problem keeping the data straight.
- gcr 10y agoI don't get this argument. Foreign key constraints are part of the schema. What should the primary key be if not a UUID?
- jordanlev 10y agoIf 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: http://www.wiley.com/WileyCDA/WileyTitle/productCd-0471380237.html http://www.wiley.com/WileyCDA/WileyTitle/productCd-047138023... http://www.wiley.com/WileyCDA/WileyTitle/productCd-0471353485.html http://www.wiley.com/WileyCDA/WileyTitle/productCd-047135348...
- rlonstein 10y ago+1. Definitely worth reading, I have Vols 1 & 2 (Vol 3 came out later). I found it useful because the author explained the decisions about the models and traps to avoid.
- rukuu001 10y ago> because the author explained the decisions By far one of the most useful things a resource can do. It doesn't just broaden your understanding of a domain, but it can teach you how to think about design problems.
- jdc0589 10y agoI was bored, so here is a quickly thrown together generic "thing" DB model: http://dbpatterns.com/documents/5706a53c1514b428de88940c http://dbpatterns.com/documents/5706a53c1514b428de88940c
- Mister_Snuggles 10y agoWhen looking at an author, I can see all of their patterns. This one, in particular, has a pattern that was forked 13 times: http://dbpatterns.com/accounts/profile/thaichor/ http://dbpatterns.com/accounts/profile/thaichor/ How do you see the forks?
- lcfcjs 10y agoWhy do people use SQL? Performance of SQL is bad when you start hitting any reasonable amount of use in a production environment. Any company will get to a point where they're app is just slow and they need to use something better like MongoDB or Elasticsearch.
- nxzero 10y agoOne 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: http://sqlzoo.net http://sqlzoo.net
- haddr 10y agoCool 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.
- mortehu 10y agoTip for SQL users: 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: SELECT * FROM reservation JOIN guest USING (reservation_id); 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.
- petepete 10y agoOr if you're feeling lucky: SELECT * FROM reservation NATURAL INNER JOIN guest;
- jessedhillon 10y agoErr, be careful with natural joins: suppose both tables have a column called "name" or "created_at" -- natural join will create join conditions from those.
- marcosdumay 10y agoI never understood why the thing named "natural join" don't use relationships to determine the joining columns. Does not look natural for me.
- vog 10y agoIndeed, this is also what always annoyed me. I suspect that this has to do with the fact that NATURAL JOIN is intended to work for sub-SELECTs and VIEWs, too. On those, it is quite hard and error-prone to determine "foreign key" equivalents. Also note that restricting NATURAL JOINs to tables wouldn't be a good solution here, either, because that would prevent you from ever JOINing more than 2 tables: When JOINing 3 tables, you effectively do have some kind of sub SELECT after having joined the first two tables, which is then supposed to be joined with the third one.
- tacone 10y agoI like this site. As simple as it is, it has a lot of potential community-wise. - 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) Lets hope that it gains users, so that the voting system (the star) can become more useful to filter out the garbage. 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?
- deleted 10y ago[deleted]
- gholevas 10y agoI built an electron app that allows you to design, generate, and share Mongoose Schemas if anyone finds it useful: http://mongomulch.com http://mongomulch.com
- burrox 10y agoI 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.
- olalonde 10y agoWow, that's really cool! Would be nice to have a "migration" export format for Rails, Knex.js, etc.
- gherkin0 10y ago> Public Newsfeed > jnichols created new pattern > Penis > 24 seconds ago Ok... maybe they need to start with some patterns about filtering spam and noise.
- barryosull 10y agoLooks 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.
- hghar 10y agoI 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. This should be called something like database designs.
- kazinator 10y agoSo what you're saying is that "design patterns" should only be for code, not for data? If you apply some recipe to structure code, that's a "design pattern"; if we apply some recipe for structuring data, it's a "data(base) design"? But code is data. But sometimes it's not clear whether something is code or data, or whether it's closer to being code than to data. Design patterns incorporate data. The Observer pattern requires a list of observers that are notified; that is a data structure which we can have in a database: an observer table joined to an event source by event source ID. If I make such a database (say for a large number of users to set up persistent notifications about some interesting interesting things), am I doing "database design" or implementing the Observer pattern? :)
- vonklaus 10y agowow. doing my first real data model. tough to find resources like this, started literally resterday. can't wait to check this out
- Scirra_Tom 10y agoHad a quick look through, don't understand what audience this is targeted towards.
- stevesun21 10y agoI thought about the similar idea to create domain models to elaborate business rules with UML ORD rather than database ERD.
- jmcgough 10y agoThe search function doesn't seem to be working - I can't get it to return results ever.
- intrasight 10y agoAn empty schema diagram is to a data modeler what a blank canvas is to an artist or a blank page to a writer - thrilling but somewhat intimidating.
- geniium 10y agoSeems like a big playground...
- moron4hire 10y agoThese 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?"
- igrekel 10y agoInteresting idea but it the way it is currently done isn't that great. 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.
- n13 10y agoWas wondering how difficult is to code a UI like this? i.e. SVG with draggable boxes with the connections/lines?
- iamleppert 10y agoLooking 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. 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. 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. https://eager.io/blog/how-long-does-an-id-need-to-be/?hn https://eager.io/blog/how-long-does-an-id-need-to-be/?hn
- vkjv 10y agoI barely ever use uuids. As you say, it solves a fairly specific problem. Usually when you need to scale to multiple datacenters, it's easier and faster to add a datacenter column and starting using it with id as a composite key. The only time I use UUIDs is when I need to pass that id out, but I want to hidr how many rows there are or the rate that they are being created. However, I usually use COMBs to prevent fragmentation.
- adwf 10y agoAs 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... 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. I even saw a circular ID chain in one. Would be fun setting up foreign key constraints for that!