Re: [libreoffice-users] Can I use a listbox to query database

Bob,
Can I make the suggestion that you use 3 tables - one for the presenters,
one for the presentations, and one for the topics.  You may need another
one but we will get to that in a minute. I am suggesting a separate table
for presenters as one presenter may make a number of presentations over
time and so you will not have to repeat data in the tables - one of the
rules of data normalisation.  Each presentation will also link to a number
of topics.  It is possible to setup the tables so that each presentation
can link to any number of topics.  The structure outlined above will waste
database space if there are fewer than 3 topics assigned and will have a
problem if you wish to assign more than 3 topics.

The presenters table would contain:
presenterID - primary key
firstname
lastname
any other field you wish to add regarding the presenter

The presentations table would contain:
presentationID - primary key
date of presentation
title of presentation
presenterID - foreign key into the presenters table
other fields relating to the presentation

This sets up a one - many relationship between presenters and presentations
ie one presenter can be linked to many presentations

With the topics table, a suitable structure may be:
topicID - primary key
topic_name
other fields relating to the topic

The relationship between presentations and topics is not a simple
one-to-many relationship.  As you have described, a single presentation may
be associated with many topics, and a single topic may be associated with
many presentations.  This is a many-to-many relationship and is best
expressed via an intermediate table - presentation_topic

The presentation_topic table would have the following structure
presentationID
topicID

The two fields together form the primary key for this table.

The relationship structure for the database is then as follows:
The presenter table links to the presentation table via the presenterID
field and the presentation table links to the topics table via the
presentationID+topicID fields in the intermediate table.

This structure will allow you to query the database and ask the following
questions
Who presented on a particular presentation
How many presentations has a particular presenter made, when were these
made, and what were the topics
What presentations were made in a particular date range
What topics were allocated to a particular presentation
What presentations have been made concerning a particular topic, or number
of topics.

Once that data structure is in place, add some test data to see that the
structure is working and you are able to retrieve the required data.  Once
this is successful, it is time to prepare some forms and reports so that
users of the database can easily use the system.  This would include
drop-down lists in the forms.

Mike



On Thu, Dec 12, 2013 at 2:01 PM, Bob Muir <rmuir2@triad.rr.com> wrote:

> I am new to databases in general and LibreOffice Base in particular.
> I am using LO Version: 4.1.2.3 installed into Windows 7 Home Premium
> Service Pack 1
> I want to create a database with information for about 100 presentations.
>  It would contain the name of the presenter, the title, and three
> “keywords” or topics.
> There are two tables consisting of a main table (that contains the
> PresenterID, PresenterName, Date, Title, Topic1, Topic2, and Topic3) and
> the table of topics for the listboxes.
> I have figured out how to create a single form to fill in the data for the
> two tables that uses 3 listboxes to present the choices for the topics.
>
> I want to make the database easily searchable for people with little
> computer experience.
> Is there a way to present a screen to such users with dropdown lists of
> the topics (same entries as in the Topics table) that will produce a list
> of the relevant presentations?
> I don’t what them to have to deal with creating a query or a report.
>
> Am I asking for too much?
>
> Thanks
> Bob
> --
> To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
> Problems?
> http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
> List archive: http://listarchives.libreoffice.org/global/users/
> All messages sent to this list will be publicly archived and cannot be
> deleted

-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted