Heya Dan,<br><br>It would help to have a bit of information about the general structure you are currently using, before giving any ideas about storing the data in a SQL Database. Obviously your doing Directories and Files, but the structure helps in determining the way to make things work in the SQL Database. Poor design of the SQL Database, will lead to poor execution of SQL instructions, as much more is required to get the data you want.<br>
<br>To give you some idea of how I would convert a file system database into a SQL Database, I'll give you an example of a file structure database, and a description of what entry means, then show the resulting database structure for SQL.<br>
An example, of a simple design using Folders:<br><br>db/<br> cust_records/<br> cust1_info.dat<br> cust1_purchase.dat<br> cust2_info.dat<br> cust2_purchase.dat<br> inventory/<br> item1_info.dat<br> item2_info.dat<br>
<br>cust#_info.dat contains: Customer Name, Address, Phone Number, Shipping Info, and such.<br>cust#_purchase.dat contains: Customer's a record of all the purchases that a customer has made.<br><br>item#_info.dat contains: Name of Item, Description, Price, Quantity, Shipping price, Shipping Weight.<br>
<br>Now, to convert this into a SQL Database, I would formulate it as such:<br><br><span style="font-family: courier new,monospace;">db</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;"> customers</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;"> id - INTEGER, PRIMARY KEY, AUTOINCREMENT</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;"> name - STRING</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;"> address - STRING</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;"> phone - STRING</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;"> ship_to - STRING</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;"> transactions</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;"> id - INTEGER, PRIMARY KEY, AUTOINCREMENT</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;"> cust_id - INTEGER, PRIMARY KEY</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;"> item_id - INTEGER</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;"> quantity - INTEGER<br>
purchased_date - INTEGER</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;"> inventory</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;"> id - INTEGER, PRIMARY KEY, AUTOINCREMENT</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;"> item_name - STRING</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;"> description - STRING</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;"> price - STRING </span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;"> total_per_quantity - INTEGER</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;"> ship_price - INTEGER</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;"> ship_weight - INTEGER</span><br>
<br>Now some explaining about what the right hand side is all about in the above layout. The first field in all tables are 'id', which is marked as INTEGER, PRIMARY KEY, AUTOINCREMENT. Integer denotes a number, of course, Primary Key tells the SQL engine to make quick look ups based upon this field being one of the more often checked fields to look up records in a database. Finally the Auto increment (Which is one word in SQL), denotes the fact that each new record put into the SQL database, should take the total number of rows, and add one to that number, to assign the identification number for this record. And lastly, a String is a variable length of text data to be stored. Most SQL engines will allow for 5 or 6 paragraphs worth of text, but this can be expensive in storage and retrieval. If you know that a field is only going to be so many characters, such as Phone, maximum being 13 characters, then you can use VARCHAR(13) as the maximum length of the data that is going to be stored in that field.<br>
<br>There's also FLOAT, which allows for decimal points, but Integers in most SQL Engines will take decimal numbers, and keep the decimals. But it's always best to see what data types a SQL engine supports, before making a final decision. Most DBI's will automatically provide a way to store common data types in the database, at their best formulation to save as much space for the database engine to handle. So look at Perl's DBI for Constructing Tables to see what assistance it will bring you. Lastly, one other data type I didn't cover in the above database, is the BLOB data type. BLOB data types are for storing Binary data in, should you find the need to store some binary data in the database.<br>
<br>With blobs, there are no conversions done to store the data in the database, it's stored as is (As in, as you provide it to the SQL Database), and can contain any valid byte sequence in it. Meaning, anything between 0 and 255 can be stored here. Most SQL Engines will store UTF-8/16 characters in strings without stripping them, but when in doubt, you can use the Blob data type.<br>
<br>Now, with the explanations of the data types out of the way, the structure is efficiently designed, for the simple fact, that if you have the ID of the customer, you can get all the items that they purchased, and get each items information from the id's that you get from the transaction table. You can even use the SQL Instruction JOIN to get all the data you need in a single execute, for example, if you wanted to get the name of the person, the name of the item, and the total cost, you could simply do:<br>
<br>SELECT name, item_name, price <br>FROM transactions <br>JOIN customers<br> ON <a href="http://customers.id">customers.id</a> = transactions.cust_id<br>JOIN inventory<br> ON <a href="http://inventory.id">inventory.id</a> = transactions.item_id;<br>
<br>This will return a list of all transactions in the format of:<br>name | item_name | price<br><br>Examples being:<br><br>"John Doe","ASUS PC",299.99<br>"Mary Johnson", "Microsoft Mouse", 19.99<br>
etc, etc.<br><br>It makes cross-table look ups a lot easier, to get the relevant data for what you need, and only what you need. And all of it is handled by the SQL Engine, not Perl, or whatever high level language you use, so the execution speed is greatly improved.<br>
<br>HTH,<br><br>Mario<br><br><br><div class="gmail_quote">On Fri, Oct 30, 2009 at 1:31 PM, Dan Linder <span dir="ltr"><<a href="mailto:dan@linder.org">dan@linder.org</a>></span> wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
I'm taking on the task of converting our in-house tool to use the Perl<br>
DBI module to replace the Data::Dumper/eval() it currently uses to<br>
store and retrieve data. Not pretty, but it has worked pretty well<br>
for the small data sets we've been using.<br>
<br>
We now have some people commenting on the speed - some have pages take<br>
7+ minutes to bring up waiting for the back-end perl code to ripple<br>
through the directory structure and eval() the necessary files to<br>
build the page. The "eval" function seems to be the bulk of the time<br>
as I expected...<br>
<br>
What I'm looking for is some general comments and discussion about the<br>
mental task of mapping these hash tables into a SQL table. I'm not<br>
really looking for a tool, more a high level discussion about ways to<br>
store the data and still remain flexible.<br>
<br>
Dan<br>
<br>
--<br>
******************* ***************** ************* ***********<br>
******* ***** *** **<br>
"Quis custodiet ipsos custodes?" (Who can watch the watchmen?) -- from<br>
the Satires of Juvenal<br>
"I do not fear computers, I fear the lack of them." -- Isaac Asimov (Author)<br>
** *** ***** ******* *********** ************* *****************<br>
*******************<br>
_______________________________________________<br>
Omaha-pm mailing list<br>
<a href="mailto:Omaha-pm@pm.org">Omaha-pm@pm.org</a><br>
<a href="http://mail.pm.org/mailman/listinfo/omaha-pm" target="_blank">http://mail.pm.org/mailman/listinfo/omaha-pm</a><br>
</blockquote></div><br><br clear="all"><br>-- <br>Mario Steele<br><a href="http://www.trilake.net">http://www.trilake.net</a><br><a href="http://www.ruby-im.net">http://www.ruby-im.net</a><br><a href="http://rubyforge.org/projects/wxruby/">http://rubyforge.org/projects/wxruby/</a><br>
<a href="http://rubyforge.org/projects/wxride/">http://rubyforge.org/projects/wxride/</a><br>