3 ms·

Do you have any recommendations for resources to learn best database practices? I'm currently designing my first database and I'm not sure what information is w

Do you have any recommendations for resources to learn best database practices? I'm currently designing my first database and I'm not sure what information is worth storing (like calculations) and how to choose which data to group in tables.

matte_black 9y ago
Ok, first you need to decide: is this going to be a purely transactional database (for business processes) or do you also plan to do data analysis straight inside this database (meaning you won’t be extracting, transforming and loading data into another database and analyzing it there). If it’s transactional, I recommend keeping calculations only if you need to access summarized data frequently. For example, if you are tracking inventory by storing the history of transactions that occur into and out of inventory, it’s trivial to find out how much of each item you have in stock at any point in time by doing a sum of the change in quantities for each item type up to that point. If you were usually interested in the “current” count, it would be expensive to perform this sum every time, so instead you could keep a separate table for calculating the running total of inventory per item and referring to that. Keep this table up to date through the use of triggers on insert events (Note that your log of inventory transactions would thus be an immutable stream of events) An example of something not worth storing is derived data that is a combination of separate columns in a table. For most queries it’s probably trivial to be lazy and wait to perform such a calculation until you actually need it. If you still want to have a ready made “table” that has all the computations you want already entered as columns for easy querying, use a view. If you find yourself making liberal use of views, you’re on the right track. If you are using a separate data warehouse for data analysis, then precompute and denormalize as much as you can. *Disclaimer: most of what I’m talking about is from a Postgres perspective.
jonah 9y ago
I read SQL Antipatterns[0] - Avoiding the Pitfalls of Database Programming by Bill Karwin a while ago and really liked it. [0] https://pragprog.com/book/bksqla/sql-antipatterns https://pragprog.com/book/bksqla/sql-antipatterns
fnord123 9y ago
SQL Antipatterns is good as another commenter recommended. But my favourite book on the topic is Markus Winand's SQL Performance Explained. Most of it is online here: https://use-the-index-luke.com/ https://use-the-index-luke.com/ but I recommend buying it since it's tiny and worth its weight in gold. It's short so you actually read it and possibly reread it. It's to the point. It has pretty pictures. And it had directly applicable advice.
dugmartin 9y ago
I’d suggest learning the fundamental normal forms - once you understand them database design is very straightforward. https://en.wikipedia.org/wiki/Database_normalization https://en.wikipedia.org/wiki/Database_normalization
jmts 9y ago
I'm going to go out on a limb and suggest you start writing the application without a database first. My early education on databases always seemed to follow a "how do we make a database do this?" rationale rather than "what data do we need to store to support these features?", which I think leads to a software design that is too strongly coupled with the database. Software modules end up dependent on database features, or table structure, and refactoring or switching data stores becomes more costly. Instead, start with a simple in-memory data store - a list of objects with some interface for accessing them, will probably your starting point. Add some basic serialisation/deserialisation features (CSV, JSON, etc) when you get past initial testing and require some persistent data. Then, once you have your API in place and your software design is stabilising, you should be able to map that data to a database fairly easily: * The primary structure maps to your main table * Child structures become additional tables, with foreign keys * Data used to lookup records can be indexed for better performance Beyond that, you should profile/benchmark your application to find what needs to be optimised, and then investigate whether your software design or your data store should be doing the optimisation. Let your software's features influence the design of your database. Don't let the database's features influence the design of your software.

matte_black 9y ago

Ok, first you need to decide: is this going to be a purely transactional database (for business processes) or do you also plan to do data analysis straight inside this database (meaning you won’t be extracting, transforming and loading data into another database and analyzing it there). If it’s transactional, I recommend keeping calculations only if you need to access summarized data frequently. For example, if you are tracking inventory by storing the history of transactions that occur into and out of inventory, it’s trivial to find out how much of each item you have in stock at any point in time by doing a sum of the change in quantities for each item type up to that point. If you were usually interested in the “current” count, it would be expensive to perform this sum every time, so instead you could keep a separate table for calculating the running total of inventory per item and referring to that. Keep this table up to date through the use of triggers on insert events (Note that your log of inventory transactions would thus be an immutable stream of events) An example of something not worth storing is derived data that is a combination of separate columns in a table. For most queries it’s probably trivial to be lazy and wait to perform such a calculation until you actually need it. If you still want to have a ready made “table” that has all the computations you want already entered as columns for easy querying, use a view. If you find yourself making liberal use of views, you’re on the right track. If you are using a separate data warehouse for data analysis, then precompute and denormalize as much as you can. *Disclaimer: most of what I’m talking about is from a Postgres perspective.

jonah 9y ago

I read SQL Antipatterns[0] - Avoiding the Pitfalls of Database Programming by Bill Karwin a while ago and really liked it. [0] https://pragprog.com/book/bksqla/sql-antipatterns https://pragprog.com/book/bksqla/sql-antipatterns

fnord123 9y ago

SQL Antipatterns is good as another commenter recommended. But my favourite book on the topic is Markus Winand's SQL Performance Explained. Most of it is online here: https://use-the-index-luke.com/ https://use-the-index-luke.com/ but I recommend buying it since it's tiny and worth its weight in gold. It's short so you actually read it and possibly reread it. It's to the point. It has pretty pictures. And it had directly applicable advice.

dugmartin 9y ago

I’d suggest learning the fundamental normal forms - once you understand them database design is very straightforward. https://en.wikipedia.org/wiki/Database_normalization https://en.wikipedia.org/wiki/Database_normalization

jmts 9y ago

I'm going to go out on a limb and suggest you start writing the application without a database first. My early education on databases always seemed to follow a "how do we make a database do this?" rationale rather than "what data do we need to store to support these features?", which I think leads to a software design that is too strongly coupled with the database. Software modules end up dependent on database features, or table structure, and refactoring or switching data stores becomes more costly. Instead, start with a simple in-memory data store - a list of objects with some interface for accessing them, will probably your starting point. Add some basic serialisation/deserialisation features (CSV, JSON, etc) when you get past initial testing and require some persistent data. Then, once you have your API in place and your software design is stabilising, you should be able to map that data to a database fairly easily: * The primary structure maps to your main table * Child structures become additional tables, with foreign keys * Data used to lookup records can be indexed for better performance Beyond that, you should profile/benchmark your application to find what needs to be optimised, and then investigate whether your software design or your data store should be doing the optimisation. Let your software's features influence the design of your database. Don't let the database's features influence the design of your software.