Would mySQL be appropriate for...

[MK27]

Here a question I presume someone can easily answer and save me some tedious scanning of text:

I have never used mySQL before, but I am going to (more or less) have to soon because I'm learning "ruby on rails". Anyway, the little project I'm doing to learn ruby syntax and usage is not a web app, but it could use a db to store data between invocations*. In the past, with perl, I've used BerkeleyDB and more recently "Storable" for this. I've also used them to do cgi web stuff and they work fine for that, which is part of the reason I've never bothered with mySQL.

Anyway, since I will have to be learning mySQL anyway, I was wondering if it could be applied this way -- eg, *not in a web context*, but just to create database files that could be used by an executable. From the looks of their site, I am guessing no, so either I have to turn this into a web app (possible) or else find an equivalent to BDB or Store for ruby. Which might be even better if I could then use that while learning rails instead. Or else just use a text file, but since I should really focus on "normative practices" that seems silly.

*nicer and easier than parsing a text file in and out

[later] looks like SQlite is more the thing for this...

[laserlight]

I was wondering if it could be applied this way -- eg, *not in a web context*, but just to create database files that could be used by an executable. From the looks of their site, I am guessing no, so either I have to turn this into a web app (possible) or else find an equivalent to BDB or Store for ruby.

You certainly are not restricted to using MySQL, or any other database system, in a web application. However, you definitely do need to find (or write ) a programming interface that will allow you to access a MySQL database from a Ruby program.

looks like SQlite is more the thing for this...

I agree, if I understand what you are trying to use the database for.

[MK27]

I didn't have a clue what "SQL" was, but I am getting the idea.

The nice thing about "Storable" is that it literally stores any data structure (eg, a hash of hashes of arrays of hashes or whatever) in a (binary) file. That's it. No muss, no fuss: you save your top level referent, and then you can load the whole thing again the same way. I guess the shortcoming is that it is only something that can be used via perl.

I presume the purpose of SQL is to produce databases usable by *any* application or languages that will deal with it. Of course there are ruby modules for this, but it still means having to arrange a data structure into an SQL object, or use such an object directly, which is not quite as versatile to my mind as a good ol' hash. Eg, I presume I won't be able to put references or objects into a table (it must all be human readable data)?

Anyway, I guess that's the industry standard and the normative practice. Oh joy.

[laserlight]

The nice thing about "Storable" is that it literally stores any data structure (eg, a hash of hashes of arrays of hashes or whatever) in a (binary) file.

Ah, that sounds like serialisation.

I presume the purpose of SQL is to produce databases usable by *any* application or languages that will deal with it.

Somewhat, since the purpose of SQL is to provide a language for database declaration and manipulation.

Of course there are ruby module for this, but it still means having to arrange a data structure into an SQL object, or use the object directly, which is not quite as versatile to my mind as a good ol' hash.

Actually, a relational database model is likely to be more versatile than a hash, but less convenient.

I presume I won't be able to put references or objects into a table (it must all be human readable data)?

A BLOB type can be used to store data in non-human readable formats, but if you store a serialised object you would then have the same limitations as with Perl's Storable.

[MK27]

I presume I won't be able to put references or objects into a table (it must all be human readable data)?

A BLOB type can be used to store data in non-human readable formats, but if you store a serialised object you would then have the same limitations as with Perl's Storable.

Well, what I meant here was that with Storable storing a complex data structure as is, I could have one referent "Zoo" at the top, Zoo being a hash each key being a ZooSection each ZooSection being a hash of Cages each Cage in each ZooSection being a hash with Species and so into IndividualAnimals, which is like an Nth dimensional table. A "Storable" would be the contents of Zoo. Probably there is a C library to do something similar with arrays, I don't know. Anyway, conceived of as tables those row values would be object references, which that is what Storable stores.

I guess, writing this now, you *must* be able to create more than just a 2D table with SQL*. I just don't see how yet.

*otherwise I have to save a table of contents with the names of the sub tables, etc...

[edit] well I am thinking a little harder now that it amounts to the same thing, since I am just pulling values out of a "table of content" the same way I would pull keys to a hash. I'm just afraid to use plain words the way I would a handle on a datatype. Any "relational database" veterans wanna clear this up for me?

[laserlight]

I guess, writing this now, you *must* be able to create more than just a 2D table with SQL

I have to go now, but roughly, create tables corresponding to classes with columns corresponding to data members, then join the tables as necessary. There may be more sophisticated object relational mapping stuff that you can use to help you.

[MK27]

Okay. Not sure about the value of this "joining" idea...

[laserlight]

Not sure about the value of this "joining" idea.

In my opinion, the benefit comes from modeling the data into entities and their relationships, or from a different perspective of effectively the same thing, from database normalisation. A join would then be a way to piece together related data such that you can access it in precisely the way that you want (as opposed to storing it in precisely the way that you want to retrieve it, and then having to live with it even when you want to retrieve the data differently).

[Driver]

Joining is an essential concept to any RDBMS programming.
Laserlight has summed it up quite nicely.
Search for "3rd Normal Form" or "3NF" as this is how you get the data stored in the database to be perhaps the most optimal so that you can follow the data flows through and get the information you require.

[MK27]

Laserlight has summed it up quite nicely.
Search for "3rd Normal Form" or "3NF"

Okay, I think I get that part. RonR seems to force 3NF by mapping classes to tables, methods to rows and through the use of an integer primary key "id" for all tables.

Not sure how that relates to "joining" tho: my impression of that is that two tables must have an identical column (not counting the row ordering) in order to be joined?

[laserlight]

Not sure how that relates to "joining" tho: my impression of that is that two tables must have an identical column (not counting the row ordering) in order to be joined?

No, but there is a need for corresponding columns to join on otherwise you get a Cartesian product, which is usually undesirable.