Ask HN: Where to Learn Database Design?

[lilsoso]

What are the best resources for learning database design?

In interviews for back-end software engineering jobs, it's common to write out models and relationships as an exercise. Yet I struggle to find resources to practice this further and learn more.

[0x54MUR41]

Have a look this one http://www.databaseanswers.org/data_models/index.htm?

You can learn the database design by example.

[roundthecorner]

Data Model Resource book by Len Silverstone. Would advise to read and understand all three volumes though.

[adventured]

I'm going to advocate strongly for getting your hands in the dirt immediately and leaning on that more than traditional database design books (they have their place, however I think they're a relatively weak benefit when starting out, and often more of an added intellectual burden that you don't need yet).

I learned everything I know about database design from building a lot of Web services over a long time. Starting from very simple concepts and gradually increasing complexity as my competency increased. I think you can simulate that process and skip some of the time to an extent however (ultimately design & theory must meet real-world use to become great at it). The only books I read were on the SQL language. I added to that by researching optimizations online. After learning SQL and a standard relational database approach well I found everything else was comparably easy to pick up. Good books on SQL will often cover (to an extent) connected topics, including limiting the duplication of data and indexes. If you already know SQL, then even better.

To compress your learning time, I would set up a relational database (using whatever you prefer, MySQL or Postgres for example). I would come up with a fake application concept, something with size to it (not just a blog application or note keeper; unless you need to start at that level of simplicity, then by all means). Maybe lay out your own version of a Stack Overflow Q&A type site (ideal because of its structure and semi-obviousness), strictly only on paper so to speak (do not write a single line of application code, do not worry about scaling, backups, etc. etc.). If you happen to get stuck, in the case of Stack Overflow you can reference their actual database design online.

To start I would probably map out the relationships on paper in combination with the actual database you're working on putting together. Later on you'll generally find you can keep that in your head without any effort, it'll become second nature.

Once you feel like you have a good schema, I would stuff your tables full of large amounts of fake data and stress the system with manual queries, to begin simulating performance and learning the benefits of proper indexes and how queries are impacted variously.

Do this over and over and over again. You should be able to do one of these per week in your spare time (or more, depending on how much time you want to throw at it and how much you want to accelerate your knowledge). Every time you do this, you'll get better at it. It'll also be helpful to redo old designs, to see how you'd improve upon them as your skill advances. And obviously improvise where you think it makes sense.

[macando]

I'd say the OP is not a complete beginner. Anyhow, people should provide more context when asking technical questions. Your approach will get him to pass a standard interview for a DB dev role. However, if one wants to understand how to map a domain model to a DB model and gain knowledge transferable between roles and teams - learning how to model data properly is the only way to go. In the end, not every data storage is a SQL DB. When it clicks, everything else will be much easier.

To be more to the point: OP, if you just want to pass the interview take the advice from 0x54MUR41

[macando]

If you want to learn more and morph into a more architectural role: Data Modeling Essentials, Graeme Simsion, third edition, 517 pages.