
Ask HN: Designing a Database - bgnm2000
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.<p>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.)?<p>Thanks!<p>-bgnm
======
tom_b
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
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
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).

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

~~~
pbhjpbhj
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> is a bit too simplified but
makes it easy to understand what's happening. This
[http://www.databasejournal.com/sqletc/article.php/1428511/Da...](http://www.databasejournal.com/sqletc/article.php/1428511/Database-
Normalization.htm) appears to be a good overview using a realistic worked
example db.

