OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

making a database with keywords

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
Squirrel7
Newbie
Newbie


Joined: 05 Jan 2012
Posts: 3

PostPosted: Thu Jan 05, 2012 11:53 am    Post subject: making a database with keywords Reply with quote

I have done a bit of google searching and searching the forums and have not found anything related exactly to what I need (or if I have, it was written too technically for me to understand), so here it is, and maybe someone can either help me do this with Base or point me towards a program or something that can do what I need.

I have lots of music files and CDs that I want to be able to locate quickly for teaching purposes, and my idea is for a database where I can assign keywords to each piece so when I am teaching a lesson and need some music that is fast, I can search the database and get a list of all the pieces with the keyword "fast."

This is my current project. A future project is similar, to catalog saved emails, blog posts, magazine articles, etc. with the ability to add keywords so I can easily find all ideas and resources related to a topic, like all lessons on "tempo" or everything related to playing the recorder, or all my lesson ideas that use picture books.

I have read a good bit of the tutorials and how-tos of basic database creation, but so far I haven't found much that wasn't highly technical related to using keywords, both for use of entering info into the database (rather than having to figure out all the possible categories and making separate checkboxes for each, like I was doing with my first attempt at indexing the info in a spreadsheet). I use Google Bookmarks to index all kinds of teaching-related websites, and I like how easy it is to file each bookmark by putting multiple keywords in one box, and then it is easy to look through the keywords list and see all the web pages that go with a particular keyword. I want something like that (not quite so simplistic, but you get the idea) for finding music files. Can I do this fairly easily, or does it require too much database experience and technical know-how? If this is not something that is easily achievable in Base, what are your suggestions for ways I can do this?
Back to top
View user's profile Send private message
peterroots
Super User
Super User


Joined: 01 Nov 2006
Posts: 591
Location: United Kingdom

PostPosted: Thu Jan 05, 2012 1:42 pm    Post subject: Reply with quote

if you look at your CD and think 'what info do I need to record about it?' you will come up with things like
Title, track title, artist or artists, composer or composers, your key words etc. Helps to scribble on a bit of paper. you could then shove this in a table DON'T I only said you could!
With what is basically a mess to start with i.e. one horrendous and unmanageable table you need to start breaking things down
Now what is unique to the CD? The title and probably the track titles (for practical purposes) and some unique key to hold everything together later. This is now one table in your database.
Composer would make a nice simple table - Name (or names) any other details that might be handy (birth death nationality) and a unique key - this gets used to provide the link between the CD table and composer table. Same sort of idea for artist.
your keywords work the same way - table full of words (that can be added to as and when needed) each identified by a unique key. The key is used to link the words to the CD table again.
This way a cd links to the key words table by storing the keys not the words one CD may link to many words but a word may be linked to many different CDs so you have a many to many relationship. These are a bit more difficult to do than one to many or many to one relationships.
I have not done this for a long time but I think you need three tables to provide each many to many link.
| cd title | CD ID |.............| CD ID | Word ID |..........| Word ID | Key Word |
| aaaaa | 0001 |..............| 0001 | 000001 |.......... | 000001 | dsksjdjjdj |
..........................................| 0001 | 000002 |...........| 000002 | gfgfgfgfg |
| bbbbb| 0002 |..............| 0002 | 000001 |...........| 000003 | rerereree |

so you can see that CD links to key words 1 and 2 while cd 2 only links to word 2. Nothing yet links to word 3. Same set up would be needed for Artist and composer.
Why this complicated? no duplication of data and no data inconsistency - 500 instances of fast Fast or FAst with a faast thrown in or one instance of Fast in the word table.

You need to be googling things like table relationships, many to many, to get some ideas of how this all works (file normalisation if you want a tech term to look for)

Hope these thoughts give you some idea of where to start with designing a database. Start on paper with little boxes for files filled with your fields and lines linking your data with the keys. If you can visualise it like this you can make it work. Don't even try and enter anything in base until you get that far.
_________________
Kubuntu 11.10 LibreOffice3.4.4
Back to top
View user's profile Send private message
Squirrel7
Newbie
Newbie


Joined: 05 Jan 2012
Posts: 3

PostPosted: Fri Jan 06, 2012 2:38 pm    Post subject: Reply with quote

Thanks for your input. I'm mainly going to be linking things to individual tracks/pieces - what CD it comes from so I can find it to use it, who composed the piece, who the artist is, etc. So that means I'll have to have a separate table for each descriptor I might possibly want to look for for each piece, right? Separate tables for dynamics, tempo, featured instruments, type of ensemble, etc, with all the different possibilities for that category, and link that to the piece?

I think I can make sense out of something like that, and come up with all the tables I need for this particular project (the other I mentioned...different story). At this point, I see one issue - what if one of the categories has several possibilities that might apply to the same piece? Like, it features a piano and a clarinet, and I want to be able to search for "piano" and have the record come up, and ditto for "clarinet" - this is where I need keywords or tags where I can just type words associated with something, and search for individual entries in that word list later on.

It seems like there should be some easier way to do what I want to do - maybe Base isn't the right program. Is there something else I should be looking at instead?

Thanks in advance for any assistance anyone can give me...
Back to top
View user's profile Send private message
peterroots
Super User
Super User


Joined: 01 Nov 2006
Posts: 591
Location: United Kingdom

PostPosted: Sat Jan 07, 2012 2:27 am    Post subject: Reply with quote

key words must be in a table if you just type them in at random you may have variations stored piano, pianos and variations with typos in them.
one cd record can link to several key words in the key word table (or instrument table if that is a separate one) so flute, piano, cello may all link to a cd and a query will find that cd whether you ask for any one of them (or even any cd with both piano and cello).

The form you use to view/enter data for the cd will have a sub form for each linked table where you will be able to see a list of instruments or other key words.

This sort of thing is exactly what base is for (though there are many other database solutions out there as well)
_________________
Kubuntu 11.10 LibreOffice3.4.4
Back to top
View user's profile Send private message
Squirrel7
Newbie
Newbie


Joined: 05 Jan 2012
Posts: 3

PostPosted: Sat Jan 07, 2012 1:07 pm    Post subject: Reply with quote

Ok, I think I see what would be involved in making this work for me...thanks for patiently explaining Smile

I probably could make it work eventually, but I think it's a more involved project to make happen than is warranted for what I want to be able to do, considering my low skill level/experience with Base. I have played around with setting up a notebook in OneNote where I could put pages for tracks and type in my keywords and all, and use OneNote's text search function to search the notebook and show me instances of whatever I'm looking for - not the most elegant solution, but it will suffice for my needs and more importantly, I can get it set up and to a useful state much quicker!

It seems like there should be a database program that will set up a database for someone who doesn't know much about setting up databases so that you can tell it what you want to do and it takes care of all the tables and links and subforms and such...

Anyway, thanks for trying to help me, peterroots! I do appreciate your time.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group