(Part 2) Best products from r/Database

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.

You know SQL? That's a great start. Now you have to pick your RDBMS product and platform, and start a deep dive, because being a DBA requires knowledge and experience with:

• Backups
  
  
• File system storage
  
  
• Transaction Logging
  
  
• Memory allocation
  
  
• cores vs sessions vs connections vs threads vs processes
  
  
• clustering (?)
  
  
• networking
  
  
• etc
  
  
  Knowing the areas that you need to acquire knowledge and experience in is the first part of becoming a competent DBA. The details, unfortunately, tend to be product-specific.
  
  For Oracle DB, I can't recommend this book highly enough.
  
  If anyone here knows of a comparable book for MS SQL Server, please let me know, I'd love to get it.

At least you're not using "Excel as your database"...

1. Try to establish connections in a read-only+shared mode if possible.
   
   
2. I would benchmark if doing the queries through linked tables or ODBC links to the file share could improve performance or the amount of time the database/tables are locked for writing. Should be easy to setup and test.
   
   
3. Also, it might be possible to connect to the database, select into an in-memory dataset, and disconnect. Then do all read-only operations on the in-memory dataset instead of over the wire.
   
   
4. Cache commonly used combo box contents in a text file on the client, instead of binding combo-box data to the remote dataset. Update combo-box cache in the application's onopen event.
   
   Good luck...

1. Use a relational data store first: MySQL/PostgreSQL/M$ SQL Server.
   
2. Put CRUD operations behind a web service.
   
3. Instead of arbitrary key/value pairs, try to get as specific as possible about the data (instead of storing everything as strings). Being more specific will help things (usually) to be more performant.
   
4. Build the application on top of the service.
   
5. Scale to the # of users you want to be able to support
   
6. At this point, if you need to move part of the data into a non-relational data store, you can.
   
   I also recommend reading this book: Patterns of Enterprise Application Architecture

Relational databases operate the same way under the hood. There are significant differences between how postgresql and sql server work, but in the end, once you know one relational database, it will translate pretty well to others.

If you really want a tour on different databases, try this:

http://www.amazon.com/Seven-Databases-Weeks-Modern-Movement/dp/1934356921

It showcases 7 different approaches to databases.

I find the free training videos at Pragmatic Works to be very informative. They also offer free to relatively low cost training.

As far as getting a crash course on getting a Data Analyst mindset, I found the Head First Data Analysis book to be a good start, but it only uses Excel and R as the toolset.

Do you expect more than a few thousand people will access this system. If not then definitely stick with an RDBMS. NoSQL is generally only appropriate if you need blindlingly fast reads, tolerably slow writes, and scalability with commodity hardware. RDBMSes give you balance of read and write throughput for a less-than-crazy number of users, and they make it far easier to maintain data integrity.

Working with relational data is as much art as skill. Even for something as simple as an inventory system (the 'Hello World' of databases) there are a number of choices to make and peculiarities to troubleshoot.

In addition to whatever database specific books you may need, I would suggest you pick up
The Art of SQL. It teaches how think about data and relationships declaratively and to understand complex data systems from the outside in.

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?

I published a book last year on database design with MySQL. MySQL Explained (http://www.amazon.com/MySQL-Explained-Your-Step-Guide/dp/151942437X) is written for beginning and intermediate users. It starts with the basics of database technology and uses a number of examples to demonstrate design principles. Even though it focuses on MySQL, most of the information applies to other software as well.

I'm also glad to correspond with readers of the book by e-mail to answer any questions that I can.

The OReilly book "SQL Tuning" has a good explanation of how the various join types work and when each should be used. Even though it was written almost 15 years ago, the information still applies.


https://smile.amazon.com/SQL-Tuning-Generating-Optimal-Execution/dp/0596005733/ref=sr_1_1?ie=UTF8&qid=1496403553&sr=8-1

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

Database Modeling and Design


My ex was building some databases for a job and I found this book (well an older version) to be excellent and concise about explaining the why behind database design.

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.

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