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

[H P Ladds]

Thanks to the two of you. I'll give these a try soon.

2008/10/17 Michael B. Trausch <mike at trausch.us>:
> 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.
>
> _______________________________________________
> Ale mailing list
> Ale at ale.org
> http://mail.ale.org/mailman/listinfo/ale
>
>