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

[Solved] Question on Parent/Child, SuperType/SubType tables

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


Joined: 17 Oct 2011
Posts: 2

PostPosted: Mon Oct 17, 2011 10:29 am    Post subject: [Solved] Question on Parent/Child, SuperType/SubType tables Reply with quote

I am creating a database for a volunteer orginization that matches students up with tutors. Both the students and tutors have mostly the same contact information. Both the students and the tutors may also be volunteers and members in addition to being students or tutors.

What I am looking to create is several Forms. One each for Contacts, Students, Tutors, Volunteers, and Members. The Students, Tutors, Volunteers and Members forms would display (read only or editable, I don't care) the information from the Contacts table.

What is the best practice for this in Open Office Base? Or, to turn it around, Is this possible in Open Office Base?

I know that for major SQL databases I would be factoring this out into:


    Contacts
      ContactID
      First Name
      Last Name
      Date of Birth
      Address
      City
      State
      Zip
      Phone Number
      ...

    Tutors
      ContactID
      Accepting Students
      Region
      Started Date
      ...

    Students
      ContactID
      Currently Looking
      Skill Level
      Goals
      ...

    Volunteers
      ContactID
      Started Date
      Acceptable Tasks
      ...

    Members
      ContactID
      First Paid Dues
      Last Paid Dues
      Secondary Contact (For Companies)
      Dues Amount
      ...



I have looked at all the published documentation I could find and searched via Google and the form but have not found anything. The only document that even touched on this was Base Tutorial: From Newbie to Advocate i n a one, two.. three! on pages 52 and 53 regarding duplicity of roles and super classes.


Last edited by Caementum on Tue Oct 18, 2011 2:03 pm; edited 1 time in total
Back to top
View user's profile Send private message
sainttomn
OOo Enthusiast
OOo Enthusiast


Joined: 18 Feb 2011
Posts: 131
Location: Two gangster planets and a cowboy world

PostPosted: Mon Oct 17, 2011 2:36 pm    Post subject: Reply with quote

Hi Caementum, and welcome to the forums.

What you describe can certainly be done. With regard to the design of your database, is the purpose to simply allow each type of person (Tutors, Students, etc.) to view information? Do you need the information to be filtered by type of person, or will each person be able to view all records?

Is there anything else the database needs to do?

That aside, OpenOffice should be able to accomplish what you need. A word of warning though, if you intend for this to be accessed by multiple individuals, you'll need to set-up OpenOffice as a front for an actual SQL database. OO is intended as a single-user platform, if you're using the built-in SQL.

-Tomn
_________________
"I'm a loner, Dottie. A rebel."
[Windows 7] [OO.o 3.3] [HSQL 1.8]

The Lazer Cat
Back to top
View user's profile Send private message Visit poster's website
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Mon Oct 17, 2011 11:44 pm    Post subject: Re: Question on Parent/Child, SuperType/SubType tables Reply with quote

Caementum wrote:
regarding duplicity of roles and super classes...I am looking to create is several Forms...The Students, Tutors, Volunteers and Members forms would display (read only or editable, I don't care) the information from the Contacts table.

What is the best practice for this in Open Office Base?

Your table structure looks sound and can certainly be implemented through Base with the built-in HSQLDB engine using one-to-one relationships, just like you would approach "subclass" tables with any other RDBMS. CONTACT_ID should be setup as the Primary Key in each table mentioned.

As for the Forms:
(1) the Contacts Form is straight forward and will allow you to read/write the CONTACTS table. You'll probably want to make the Primary Key (CONTACT_ID) an Auto-Value field for simplicity (but only in the CONTACTS table).

(2) the other Forms can be based on a 'Query' or 'SQL command' that combines the CONTACTS table with a respective STUDENTS, TUTORS, VOLUNTEERS or MEMBERS table as applicable. This approach will render a read-only result-set in these Forms.

(3) you can generate read/write Forms which combine the CONTACTS table with a respective subclass table using a hidden SubForm structure. For instance, you can base the MainForm on the CONTACTS table while basing the SubForm on the STUDENTS table. You can then link the SubForm to the MainForm using the CONTACT_ID field. You may also want to set the SubForm navigation toolbar to "parent form" to eliminate Form navigation confusion when the cursor is placed inside a SubForm field.

(4) and since you asked about 'best practices' with Base, you should be aware of the data loss issue and remedies outlined in my signature link below.
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
Caementum
Newbie
Newbie


Joined: 17 Oct 2011
Posts: 2

PostPosted: Tue Oct 18, 2011 2:02 pm    Post subject: Thanks Reply with quote

Thanks so much to both of you.

sainttomn:

This is all going to be accessed and maintained by the single employee of the volunteer orginization. (One employee, many volunteers.)

There will be quite a few other tables to track other things. Tutor-Student pairs, qualifications, attendence at meetings. But the only thing I could not figure out was how to deal with the people(contact) records.

dacm:

Your numbers 1 and 3 were exactly what I was looking for.

I will make sure to look at those two articles before I go much further.
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