5 ms·

Ask HN: Designing a Database

4 points by bgnm2000 17y ago

I'm primarily a designer with a good amount of code experience (classes in school, messing around on my own etc.) I started to learn rails over the summer, and built one pretty messy app. That said, I'm gearing up to build my second app, this time around I've been thinking a lot about the database and how everything needs to connect etc. rather than doing it as I go. So far I've drawn it on paper (looks like something out of MS Access) but I was wondering if there are any other recommended steps I should take before I start coding this all out (besides the usual flow charts etc.)? Thanks! -bgnm

fragmede 17y ago
Make sure you've read Wikipedia's article on normalizing databases. http://en.wikipedia.org/wiki/Database_normalization http://en.wikipedia.org/wiki/Database_normalization
- pbhjpbhj 17y ago
  If you understand such gems as: [For 3NF ensuring that] Every non-prime attribute is non-transitively dependent on every key of the table Then that's the perfect reminder article. I did an undergrad course in db design covering db normalisation (years ago) and still found that page to be a bit dense. I'd look elsewhere for a primer. http://www.databaseprimer.com/normalization.html http://www.databaseprimer.com/normalization.html is a bit too simplified but makes it easy to understand what's happening. This http://www.databasejournal.com/sqletc/article.php/1428511/Database-Normalization.htm http://www.databasejournal.com/sqletc/article.php/1428511/Da... appears to be a good overview using a realistic worked example db.
tom_b 17y ago
Do you need your system to be transaction-oriented (think ATMs and banking) or analytic (for doing slice and dice reporting of data)? You'll do well to check out normalization for the first and understand star schemas for the second. It's awesome you are thinking about db design upfront, just think about what your data will be, how it will be used, and how you will want to move it around for your app. Don't discount not using a db at all (and I'm a db guy for work) - for one of my current projects, I'm simply dumping small data objects into individual JSON files. Easy to fetch with Ruby/Sinatra and easy for the GUI part of the app to play with (Javascript eats up JSON on that side). Not to discount Rails at all, but going with a minimal toolset like Ruby/Sinatra/Sequel might be just the ticket for you roll simple web apps really quickly. Heck, this forum uses flat files for storing submissions, comments, and user data.
- bgnm2000 17y ago
  Thanks for the reply! I'd say the data is definitely going to be analytical - but some parts will be transaction oriented, if that makes sense (people will be able to pay for greater functionality). I don't think I know enough about flat file systems to code one up myself at this point either (not that I know enough about DB's - but enough to create something). Any good resources for understanding star schemas?
  - tom_b 17y ago
    Sure, grab one of the data warehouse books from Kimball. Inmon is the other big data warehouse author out there, but I'm not as familiar with his texts. Kimball's stuff is an easy read and not platform focused in any way. If you are aiming to do business visualizations with the data, you might want to check out Stephen Few - he's written several books on business data visualization (think Tufte distilled for charts and dashboards).
- cheald 17y ago
  If you're storing/reconstituting JSON, check out mongodb. It's basically a cracked-out high-performance JSON store.

4 points by bgnm2000 17y ago

fragmede 17y ago

Make sure you've read Wikipedia's article on normalizing databases. http://en.wikipedia.org/wiki/Database_normalization http://en.wikipedia.org/wiki/Database_normalization

pbhjpbhj 17y ago
If you understand such gems as: [For 3NF ensuring that] Every non-prime attribute is non-transitively dependent on every key of the table Then that's the perfect reminder article. I did an undergrad course in db design covering db normalisation (years ago) and still found that page to be a bit dense. I'd look elsewhere for a primer. http://www.databaseprimer.com/normalization.html http://www.databaseprimer.com/normalization.html is a bit too simplified but makes it easy to understand what's happening. This http://www.databasejournal.com/sqletc/article.php/1428511/Database-Normalization.htm http://www.databasejournal.com/sqletc/article.php/1428511/Da... appears to be a good overview using a realistic worked example db.

tom_b 17y ago

Do you need your system to be transaction-oriented (think ATMs and banking) or analytic (for doing slice and dice reporting of data)? You'll do well to check out normalization for the first and understand star schemas for the second. It's awesome you are thinking about db design upfront, just think about what your data will be, how it will be used, and how you will want to move it around for your app. Don't discount not using a db at all (and I'm a db guy for work) - for one of my current projects, I'm simply dumping small data objects into individual JSON files. Easy to fetch with Ruby/Sinatra and easy for the GUI part of the app to play with (Javascript eats up JSON on that side). Not to discount Rails at all, but going with a minimal toolset like Ruby/Sinatra/Sequel might be just the ticket for you roll simple web apps really quickly. Heck, this forum uses flat files for storing submissions, comments, and user data.

bgnm2000 17y ago
Thanks for the reply! I'd say the data is definitely going to be analytical - but some parts will be transaction oriented, if that makes sense (people will be able to pay for greater functionality). I don't think I know enough about flat file systems to code one up myself at this point either (not that I know enough about DB's - but enough to create something). Any good resources for understanding star schemas?
- tom_b 17y ago
  Sure, grab one of the data warehouse books from Kimball. Inmon is the other big data warehouse author out there, but I'm not as familiar with his texts. Kimball's stuff is an easy read and not platform focused in any way. If you are aiming to do business visualizations with the data, you might want to check out Stephen Few - he's written several books on business data visualization (think Tufte distilled for charts and dashboards).
cheald 17y ago
If you're storing/reconstituting JSON, check out mongodb. It's basically a cracked-out high-performance JSON store.