[ale] Database conversion -- AKA Slipping the Microsoft mortal coil.

[Michael B. Trausch]

On Fri, 17 Oct 2008 10:53:58 -0400
"H P Ladds" <householdwords at gmail.com> wrote:

> Hey All,
> 
> I'd like to convert a database that was created with a Windows
> application into a database that is usable by OpenOffice Base. Paper
> Tiger is the application that created the database. I use it to help
> organize, store and retrieve my paper files. In short, it allows me to
> do a keyword search and locate just the heck where a particular file
> folder is in my file cabinets.
> 
> I can import the Paper Tiger tables into OObase; however, I am having
> difficulty performing queries as the last column is a comma separated
> list of keywords.

Does OOo Base support a "list" or "array" type?  Not sure if HSQLDB
does or not, but I suspect that if not, then you're probably stuck
using a manually delimited simulation of a list or array.

> 
> I tried to depict the tables below: the first column details file
> numbers, the second column details the group to which a file belongs
> and the third is a list of keywords.
> 
[snip table]
> 
> Must I "normalize" the comma separated list of keywords by giving each
> keyword its own column, or is it possible to construct a query that
> parses through the comma separated list of keywords? Note: OObase
> allows for the structuring of queries as MySQL queries.

I would probably create a relation table to join them together, if you
can do that.  Something like the way Wordpress assigns categories to
posts:

mysql> select * from wp_post2cat limit 10;
+--------+---------+-------------+
| rel_id | post_id | category_id |
+--------+---------+-------------+
|      3 |       3 |           4 | 
|      4 |       3 |           3 | 
|      5 |       4 |           1 | 
|      8 |       6 |           5 | 
|     11 |       7 |           4 | 
|     10 |       6 |           3 | 
|     12 |       7 |           6 | 
|     13 |       8 |           8 | 
|     14 |       8 |           7 | 
|     15 |       8 |           4 | 
+--------+---------+-------------+
10 rows in set (0.00 sec)

This makes it easier to create picky queries that are absolute in
nature, because that way you don't have to parse the values.  It's also
a bit better than assigning each keyword its own column, since it is
scalable.  You'd of course need to have a table with just the keywords
to link them in this fashion.

> 
> I acknowledge that a spreadsheet would be easier. Thought I'd start
> out with a relatively easy database task and build upon that knowledge
> for future projects.
> 
> Paper Tiger is my last tie to the world of Windows computing. If I'm
> freed of it, I will be able to kick the Windows habit -- in my home
> office at least.
> 

Good luck, definitely.  Is always good to get rid of icky-poo
software.  :-)

	--- Mike

-- 
My sigfile ran away and is on hiatus.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 197 bytes
Desc: not available
Url : http://mail.ale.org/pipermail/ale/attachments/20081017/cc1961e7/attachment.bin