Top products from r/Database

I am currently working in a data warehousing and business intelligence role at a bank. Aside from the basics of ETL, SQL and OLAP, I would recommend having at least a basic understanding of financial accounting. I have also found it useful to read The Data Warehousing Toolkit as well as some other Kimball books.

For entry-level work, there are two recommendations of related skill that have served me quite well to get my foot in the door and show added value: Excel and reporting.

Every institution needs reports developed and it amazes me how rare it is to find well-built reports that clearly communicate their intended information. Being able to follow a few simple guidelines for effective layout and design go a long way. Edward Tufte wrote the definitive work regarding this, but I use Stephen Few's work for more up-to-date examples.

Excel has proven itself very useful for quick ad-hoc analysis and manipulations. Also, it is a mainstay application for most financial services companies and being fluent in functions, pivot charts and VBA is quite useful.

I'm no expert in database certification so I won't comment on them, but they sound expensive. I'm sure you could go a long way in improving your skills by working through some free resources and classic texts.

A nice tutorial on fundamentals is:
http://philip.greenspun.com/sql/

A classic introductory to intermediate text is the following. It can get you amazingly far because even advanced topics are explained well:
http://pages.cs.wisc.edu/~dbbook/

Don't get thrown off by the publication year. The fundamentals of relational databases have barely changed for decades.

An excellent in-depth look at database theory is presented in:
http://www.amazon.com/Foundations-Databases-The-Logical-Level/dp/0201537710

For data warehousing and analytical querying (beyond Ramakrishnan et al) this is a great resource:
http://www.amazon.com/The-Data-Warehouse-Toolkit-Dimensional/dp/0471200247

Source: I'm a graduate student in databases.

Trees and hierarchies are a good fit for SQL databases... except that the basic ParentId/ChildId style of implementation is not particularly good.

Check out Joe Celko's Trees and Hierarchies in SQL for Smarties for a really good read on several different ways of doing this. Includes pros, cons, and pretty reasonably good code. I couldn't possibly recommend this book any stronger.

I don't recall if it handled some SQL systems specific support for trees (eg. Oracle has specific support for trees).

For your particular application I would look at OpenStreetMaps. Otherwise...

David Hay's

• [Enterprise Model Patterns][1]. This is a beast of a book, but has some great patterns.
  
• [Conventions of Thought][2]. More stuff on MRP.
  
• [A Meta-Data Map][3] . Haven't read this one.
  
  Len Silverston's
  
  
• [Data Model Resource Book Vol. 1][4]. Your main data model patterns.
  
• [Data Model Resource Book Vol. 2][5]. Case studies by industry.
  
• [Data Model Resource Book Vol. 3][6]. A deeper explanation of Vol 1.
  
  Michael Blaha's [Patterns of Data Modeling][7]. This one has some interesting temporal, graph, and tree models.
  
  Martin Fowler's [Analysis Patterns][8]. This one skims some of the other patterns, but gives accounting a solid treatment.
  
  They are all well-rated, and I have read all but one, and they are all very good. Several of them are available on [safaribooksonline][9].
  
  Also, OASIS's [Universal Business Language][10], schemas
  
  
  [1]: http://www.amazon.com/Enterprise-Model-Patterns-Describing-Version/dp/1935504053/ref=sr_1_1?s=books&ie=UTF8&qid=1346950468&sr=1-1&keywords=enterprise%20model%20patterns
  [2]: http://www.amazon.com/Data-Model-Patterns-David-Hay/dp/0932633749/ref=pd_sim_b_2?ie=UTF8&refRID=1PQPGE4E6T2RPR2XTN80
  [3]: http://www.amazon.com/Data-Model-Patterns-Metadata-Management/dp/0120887983/ref=pd_sim_b_3?ie=UTF8&refRID=1PQPGE4E6T2RPR2XTN80
  [4]: http://www.amazon.com/Data-Model-Resource-Book-Vol/dp/0471380237/ref=pd_sim_b_3?ie=UTF8&refRID=08T9TEZJNZM2EMKZV3AB
  [5]: http://www.amazon.com/Data-Model-Resource-Book-Vol/dp/0471353485/ref=pd_sim_b_3?ie=UTF8&refRID=1D5TDG7479G7TQMBPNWF
  [6]: http://www.amazon.com/Data-Model-Resource-Book-Vol/dp/0470178450/ref=pd_sim_b_4?ie=UTF8&refRID=08T9TEZJNZM2EMKZV3AB
  [7]: http://www.amazon.com/Patterns-Modeling-Emerging-Directions-Applications/dp/1439819890/ref=sr_1_1?s=books&ie=UTF8&qid=1346950554&sr=1-1&keywords=patterns%20of%20data%20modeling
  [8]: http://www.amazon.com/Analysis-Patterns-Reusable-Object-Models/dp/0201895420/ref=sr_1_1?s=books&ie=UTF8&qid=1346961699&sr=1-1&keywords=analysis+patterns
  [9]: http://my.safaribooksonline.com/search?q=data%20model
  [10]: http://en.wikipedia.org/wiki/Universal_Business_Language
  

Since relational databases are a mature technology, there is some well-established theory that is very useful to learn (in conjunction with the more practical, vendor-specific knowledge).

I'd suggest:

Database Management Systems - an undergrad-level textbook with a good balance of theory and practice, foundations and advanced material.

Readings in Database Systems - a compilation of influential academic papers from the database field.

Data Modeling Made Simple - a great little book that bridges the gap between databases and the real-world entities they store.

ETL is the process of populating a data warehouse with data from operational systems. While both involve transferring/updating data, your issue isn't really about ETL. There might be some lessons about copying/updating data in the ETL field, though.

Kimball's books are great; I'd add this one to your reading list. Probably a better starting point on data warehouses than the ETL one.

IMO your problem is hardly database related, even if data stored in a db are involved. It's a pure SW development/programming task outside of the realm of database administration.

Start off by looking for off-the-shelf solutions, i.e. check with your POS supplier if they already support this.

Failing that, you need to build your own software for pushing updates to the remote locations. A service installed on every POS that periodically polls the central server for pricing info is probably your best bet (perhaps not ideal, but should be a serviceable solution for the short run). I'd send a JSON document with every local SKU and expect one back containing current prices -- or ask for changes since last update if you have a lot of products. Make sure nothing stupid happens when a SKU isn't found or the request fails.

Make sure you understand every relevant piece of the POS db's schema. Will updating the base price do, or do you need to consider discounts, currency, taxes and whatnot? You also need to be sure you're asking the right server for pricing info (proper authentication, e.g. something PKI based), and that you have instrumentation in place to notice if a remote location isn't asking for price data.

I strongly recommend Database Design for Mere mortals. Though your question is on SQL, I am not sure if you are that specific.

Query language is a way in which you can express your understanding of the concepts (entities), how they relate to each other (relationships) and the business a particular database is addressing. Being able to write excellent queries depends on how well you understand the database structure itself.

That's why understanding database design is actually more relevant to your query, IMO. Think of it this way - once you understand the database structure, you understand everything about the company's business.

Sounds a bit presumptuous and close-minded of him, like he's got some 'best practice' design in mind and intends to fit it to your business (or your business to it).

Maybe that will work, but without any real due diligence he runs the risk of it not working, finding himself trying to jam a square peg into a round hole long after the peg has been constructed and paid for.

Which may be the way his consulting company works - use the same cookie-cutter approach that works for 80% of businesses, taking acceptable losses on the 20% where it fails. It just sucks for those 20%.

As for your question #1, the most thorough analysis and abstraction of an organization's processes, data, and metadata I have yet come across is detailed in the book Data Model Patterns: A Metadata Map.

It answers the question of how do you model an organization or system when the things being modeled don't all fit into conventional categories. Here's a paper on it I just found on Google too.

As for question #2, I assume he meant by 'plugging some tables into others' that it will be expandable to accommodate future needs, organizational/process changes, and upgrades. Theoretically, a normalized database that correctly specifies and implements all relations b/t in-scope data and metadata should allow exactly that - incorporation of new data or metadata as the business changes (leaving aside for now decisions about stored procs and business logic).

But the way you relate it, it sounds more like he's using that to justify using his canned solution and leaving you guys to complete it later when he's gone and $100k richer.

Beautiful. Thank you. Everything you just described here, falls in the realm of ETL? I just ordered a book on the subject. The logic does make sense explained this way.

http://www.amazon.com/The-Data-Warehouse-ETL-Toolkit/dp/0764567578/ref=sr_1_1?ie=UTF8&qid=1331798466&sr=8-1

http://www.w3schools.com/sql/default.asp

Judith Bowman's book is pretty good.

https://www.amazon.com/Database-Design-Mere-Mortals-Hands/dp/0201752840

I read that book back in college for my Databases class and thought it was a pretty easy read. It's long, but can also surprisingly be a quick read, esp if you just go thru it w/ a highlighter and don't take much notes beyond that.

There's also a newer version of the book available.

Are there any specific questions you have about databases tho, or any specific topics that confuse you? What database software are you guys working with? MySQL? MariaDB? Microsoft Access? PostgreSQL? MongoDB?

You're thinking like a java developer. Stop thinking open source and retrain your mind to think in sets. Once you've got the basics of set-based computing down, the particular DBMS you end up working with is just a matter of learning server config and eventually the nuances of particular execution plan creation and management.

Try something like this that is totally independent of RDMS. Learn the fundamentals of the SQL language, thinking in terms of sets, and basic performance management through indexing and you'll be miles ahead of the game.

For data modeling, I prefer Object Role Modeling (ORM2) over UML. It gives you a conceptual model (ideas and relationships) rather than a logical model (tables and columns), which ends up being more semantically stable as you refine your domain.

The cool thing is that you can generate a 5th Normal Form logical model from it algorithmically. The NORMA tool for Visual Studio can even generate the Barker ER diagram for those who prefer that view.

Full disclosure: I'm a fanatic of ORM2 and have started blogging about fact modeling in my copious spare time.

EDIT: The definitive work on ORM2 is Terry Halpin's book

These two books are helpful for the 11g exam (also try to find some braindumps/practice exams). There are also similar books for the 12c exam if you want to go for that instead:

http://www.amazon.com/Oracle-Database-Fundamentals-Exam-Guide/dp/0071597867/ref=sr_1_2?ie=UTF8&qid=1405176497&sr=8-2

http://www.amazon.com/Oracle-Database-Administration-Guide-1Z0-052/dp/0071591028/ref=sr_1_5?ie=UTF8&qid=1405176497&sr=8-5

Hey u/grumpyyoshi, try this link. I just checked it and it’s showing up as free.

https://www.amazon.co.uk/Learn-SQL-Practical-Database-Fundamentals-ebook/dp/B07D5S2W4Y

Agreed, OP, you're massively overcomplicating this. If you learn a bunch of math and theory to try and become a database professional you will just come across as an academic rather than someone who can get work done. Learn how to get work done. Check out books like SQL Cookbook and SQL For Smarties to learn stuff that will actually help you do what you need to do.

You do want to have normalization down and basically understand set theory, but you're already doing that.

I found this book really useful: Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design https://www.amazon.com/dp/0321884493/ref=cm_sw_r_cp_api_-XVEyb2Y4PZX3

Walks you through the design process, and it's tool/language agnostic, so it explains the concepts without getting into the weeds with the code

Informatica is widely used for ETL tools, but more important is understanding the challenges. How to design staging tables, what update strategy to use, how to design restartable jobs, and so on. This book refers to data warehousing, but the techniques are applicable to most scenarios.

This might be good. You can also read some of the documentation/white papers/marketing materials on otn.oracle.com for Exalytics and/or TimesTen

I'd recommend first, the Inside SQL Server Series mentioned above. Also I'd recommend the Guru's guide to Transact-SQL. I found it really enhanced my skills in T-SQL over the years. I'd run into some difficult problem only to think 'hey, the guru's guide had something on this....'. I tried to re-read it every year. Also, lots of good code samples.

Unless, of course, you need to join a whole lot of things in complex patterns. Then you should check graph databases. I used to work as a MS SQL Server DBA, and I clearly remember the problems we had with hierarchical data. There's even books describing the patterns you can use to work around the limitations of SQL.

(Disclaimer: I work as a dev on the Neo4j team)

Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design

SQL Queries for Mere Mortals
And
Database Design for Mere Mortals

Take a look at:

https://www.amazon.com/Data-Model-Resource-Book-Vol/dp/0471380237

Maybe it's similar to what you're looking for? It's a bit old, though