Ask HN: Relational Databases and Concepts | Hacker News Reader

Ask HN: Relational Databases and Concepts

13 pointswirrbel6 comments

Hi,

I have never really dived into databases a lot, apart from some contact to SQL (wrote some select statements to extract some data, etc.) but I never fundamentally got into theory or on a conceptual level, i.e. an SQL database to me was just a set of tables that were kind of unhandy to work with.

Until recently, when I touched Databases again (using Python SQLAlchemy's query language [not the ORM]). For the first time I was really exposed to the foreign-key concepts etc. and I think I saw quite some parallels to declarative languages such as Prolog, etc.

All of the sudden I am eager to learn some more on the relational database concepts and I wonder where to find good material on that. Unluckily a lot of stuff I found on the web is in-the-trenches tutorials or get-yourself-started-with-php-mysql.

I am especially interested in

- design of good database schemas - refactoring of database schemas - best practices - conceptional treatment of database operations and their relation to logical/declarative programming languages such as prolog.

I would very much appreaciate any input on this.

First, I'll say this, Relational databases are super powerful and extremely valuable in today's world and while no-sql is really awesome too relational won't go anywhere so it is a very valuable skill set to have.

As for learning relational databases. First, research some books on Amazon and look at the reviews, because there just are so many good books on them. In general, remember that even within relational databases there are different design guidelines for OLTP (transaction systems think ecommerce) and OLAP (reporting, analytics) etc. IMO, for now, stick with OLTP (transactions based) books and systems.

Outside of reading some books, also checkout stack overflow and other sites to get examples of good schema's and bad schema's. And review as many schema's as possible so you get a feel for what works and what doesn't. I look at a ton of schema's in consulting and can tell people problems they are having before they have even told me anything about their issues because it is obvious from their data schema. It doesn't necessarily mean their schema is a bad design, just that all of them have tradeoffs and the best way to learn is to see real schema's and datasets.

Also, search for "relational database schema examples" in google image (without the quotes of course), and look at some of the image samples that come up. This will also give you a better feel for how the ER notation or UML notations work. You'll figure them out they aren't that hard.

It really is a huge subject, so hunt around for some online training and then ask people for recommendations after you see a few to see if anyone can give you specific feedback on them.

I strongly recommend the book "Seven Databases in Seven Weeks" if you want to understand a bit of "theory" around databases. The first chapter focuses on a relational database, PostgreSQL, but by learning about the other types (columnar, document-store, graph, etc) you also learn more about relational databases by learning what choices they make and how they could have gone otherwise.

I recommend this, too.

I am currently enjoying the First Edition of Database Systems: the Complete Book. I picked up a very good copy from Amazon for $6.99 [ $3.00 plus $3.99 shipping ].

http://www.amazon.com/s/ref=nb_sb_noss?url=search-alias%3Dap...

I've become a fan of Ullman...Bell Labs plus academia is a pretty comprehensive range of experience.

Also Widom's Introduction to Databases is available as a self-paced course from Coursera. It was among the initial batch of Stanford courses that rolled over into Coursera.

https://www.coursera.org/course/db

Anyway, my take is that the Computer Science of database systems is important enough in the modern world that I should be familiar with it as Computer Science and not so much as a set of copy and paste code from the internet.

Any of the books by Chris Date are a good place to start, although some find him a bit strident.

A lot of people rip on SQL, but knowing it well is extremely helpful. With all the NoSQL hype it's easy to overlook the stuff RDBMSs are very, very good at--but they're not good at everything.

(With the caveat that PG's JSON stuff brings NoSQL into the SQL world.)

There is a good introductory course at Coursera, taught by Professor Jennifer Widom. I liked it, not only because of the contents and the format (which are good, and work for me), but because of how intensely enthusiastic the Dr Widom was. A++, would take a course taught by her again.

Reply on news.ycombinator.com