Re: [Nolug] Database design

If you are considering alternatives to SQL or the whole RDBMS model
you might want to look at document-oriented databases like MongoDB (http://www.mongodb.org/
), CouchDB (http://couchdb.apache.org/), etc.

Here's a debrief on the recent "No SQL" event held out in California: http://blog.oskarsson.nu/2009/06/nosql-debrief.html

They run down various non-RDBMS data stores that were presented there.

I have at least two projects coming up this Fall/Winter that I may
implement MongoDB or something like it for non-relational storage...

-James

On Jul 13, 2009, at 8:49 PM, Ron Johnson wrote:

> On 2009-07-13 16:26, Chris Jones wrote:
>> I might be working on a database-driven app pretty soon, but have a
>> question on what might be the best approach for database design.
>> Let's say you're doing something like a recipe database. You would
>> have multiple recipes, obviously. I'm just trying to figure out the
>> best way to do the table layout.
>> Each recipe would look like this:
>> Aunt Sue's God Awful Cobbler
>> =======================
>> 2 cups flour
>> 3 eggs
>> 2 cups sugar
>> 3 lbs of tripe
>> Mix all ingredients, throw in the oven at 400F for 30 minutes.
>> Eat. Vomit.
>> So, you are storing various types of information on each recipe, such
>> as the recipe's name, the directions on how to make it, but then you
>> have an ingredients list, which would essentially have to be a 2D
>> array. It would store the # of units, the unit type, and what
>> exactly
>> the ingredient is...times however many ingredients that particular
>> recipe would have. That's where a bigger part of the problem comes
>> in, that each recipe could have 3 ingredients, or 20.
>
> In DB lingo, this is a denormalized design. Very bad.
>
>> I'm not sure if you should just have a ton of columns of data for
>> your
>> recipe table to accomodate for all the possible ingredients.
>> My own wisdom of database design says that's probably a bad idea.
>
> Correct. That's why it's a Very Bad idea.
>
>> So, should I just have an ingredients table that looks like this:
>> (first column is an ID number corresponding to the recipe it is for)
>> 1, 2, cups, flour
>> 1, 3,, eggs
>> 1, 2, cups, sugar
>> 1, 3, lbs, tripe
>> 2, 1, cups, flour
>> 2, 3, tsp, vanilla
>> 2, 2, cups, sugar
>> And that is basically ALL ingredients of ALL recipes, then you just
>> do
>> a SELECT WHERE ID = 1 to pull all the items for recipe # 1.
>> Is that the way to go for that sort of database? Or is there yet
>> another way that would be even better?
>
> Now your design is in 1NF, the First Normal Form.
>
> I wonder, though, if an SQL database isn't the wrong technology for
> a recipe database, since not all recipes are "mix it all together
> and shove it in the oven".
>
> Think, instead, of a hypercard-style solution. PyCard, maybe, which
> (I think) also allows for full-text search.
>
> --
> Scooty Puff, Sr
> The Doom-Bringer
> ___________________
> Nolug mailing list
> nolug@nolug.org

___________________
Nolug mailing list
nolug@nolug.org
Received on 07/14/09