[Ldsoss] Scout Tracking

[Oscar Schultz]

On Monday 10 July 2006 10:04 am, Tom Welch wrote:
> I've used Dia in the past for Linux.  I've not used Umbrello but it
> looks like it should do the trick just fine.  Also, to model the data,
> have you given MySQL Workbench
> <http://dev.mysql.com/downloads/workbench/1.0.html> a try?

thanks - I will try dia.
>gety
>
> Here are my comments on your data table.  Take them for what they are
> worth...about a nickel and two pennies.
>
>     * As a general note:  If you want to allow for synchronization
>       between a local system and a remote system then all of your
>       primary keys should be in the form of a guid (I use varchar(32)).
>       Most DB's have an auto-increment type for ints and these are used
>       as primary keys but you can't synchronize as easily .. especially
>       if you are allowing for a multi-user system because two keys could
>       get the same value.  If your is not to allow for synchronization
>       then I'd let the DB handle the auto-assigning of the primary key.

my plan is to use a varchar field as a membership number - good catch - I 
completely forgot a variable I can use as a common number between dbs.
I have been planning to use the record_id as only a local number and to use 
the auto-increment feature for all the record_ids . The system will be 
multi-user.
>     * On the "people_table", why do you have "birthdate" and also
>       "birthmonth", "birthyear" and "date".  It seems that "birthdate"
>       would handle it and any programming language you use could extract
>       the month/year easily.
the date field in each field is to doc when a record was created - that 
permits versions . I am not sure why I felt the need to doc creation dates. I 
think the date field will be used to document changes, track other info to 
prevent complains about data errors.
>     * On the "address_table", what is the "date" field for?  Also do we
>       think that we will have multiple addresses for people and so need
>       to keep a separate table for them?  I agree if this is to be
>       expected then it would be more efficient to have the addresses in
>       a separate table.
Multiple addresses - sure . we have youth living in multiple homes (divorce, 
boarding schools, etc ), unlisted addresses (shipping address, po box) and 
other situations. 
>     * What is the date field on the "phone_table" used for?  In fact,
>       all tables have a "date" field.  I'm not sure their purpose.
I just want to know how old a phone number is - people change phone number 
every day AND without telling me. If I know how old the number is and what 
type it is (cell, landline) I can guess the probability of finding a good 
number when someone has multiple phone numbers 
>     * On the "Emergency Contact" section, I can see that you intend to
>       have a small link table linking "boys" to their parents or other
>       contacts.  My comment here is that it may be a bit of "overkill"
>       to force the user to enter in all parents and other people that
>       act as emergency contact information into the DB.  What I mean is
>       that if I enter in a boy's information (address, phone number,
>       etc) I then have to enter in the same information for his parents
>       so that I can link the boy to the parent.  What you end up with is
>       two records with basically the same information.  It may be more
>       efficient to create a "family" table which would consist of the
>       last name of the family and then the address, phone numbers, etc.
>       Then have a "family members" table which you enter in family
>       members (first name, birthdates, etc).  This way you are not
>       double or triple entering in the address.

emergency contacts are not always the parents . I also need to add an 
emergency release passphrase to permit someone to gain emergency guardianship 
of a youth. 
>     * On the "authorization_table", why do you have two password
>       fields?  You will need to ask for the user to enter in the
>       password twice but that is to just verify that it is entered in
>       correctly.

I have the 2 fields to allow the use of a 2 stage password. the 2 stage is not 
required - it is just available
>     * On the "authorization_table", I recommend that you lose the
>       "userid" and use the email address to verify and allow the user to
>       login.

emails change and can be extracted from mailing lists. Using a userid is just 
a way to obsure part of the info needed to login.

>     * On the Image and Picture tables, would it make more sense to store
>       the data in a blob in the DB instead of having a link to a file?
>       Links are easily broken.

I prefer to store images as files - I still do not trust databases as much as 
I trust filesystems. Also keeping the images as non-blobs keeps the database 
smaller and allows access to the images with multiple tools (outside of the 
database). Broken links can be recovered if the pathname/filename naming 
convention has enough info.
>
> Good start Oscar.

It is a start. I'll add the changes, modify into sql and repost.

thanks
oscar
>
> Tom
>
> Oscar Schultz wrote:
> > Hopefully someone on the list has reviewed my set of tables and fields.
> > no responses about the structure yet.
> >
> > I need a list of the needed basic functions to produce a tracker with a
> > minimum set of functions . A set of functions to manage (add,delete,
> > modify) people, events, awards, requirements, and produce a set of
> > reports is what I think are needed . Additionally a set of functions to
> > manage access to the program data.
> >
> > Any linux fans out there have a favorite tool to design the application.
> > I'm looking for a good tool to flow-chart with and to do the initial
> > design work of the app. I have looked at using umbrello to doc/flowchart
> > the app . Suggestions are appreciated .
> >
> >
> > I also plan to use sane and subversion, to extend what I can write to
> > include scanning and image/picture version tracking .
> >
> > thanks
> > oscar
> >
> > On Friday 07 July 2006 10:44 pm, Oscar Schultz wrote:
> >> I have spent some time attempting to setup a sql database and tables as
> >> the basis for my version of the tracking software.
> >> Hopefully those database admins out there can review my table outline
> >> for missing fields and/or crazy table relationships and make some
> >> helpful comments.
> >>
> >> enjoy and thanks
> >> oscar
> >>
> >>
> >> #the table to record personal information
> >> people_table
> >> record_id
> >> firstname
> >> middlename
> >> lastname
> >> preferredname
> >> gender
> >> birthdate
> >> birthmonth
> >> birthyear
> >> date
> >>
> >> # the table to record personal address information
> >> # 1 people record to many address record relationship
> >> address_table
> >> record_id
> >> people_record_id
> >> address1
> >> address2
> >> city
> >> county
> >> state
> >> zipcode
> >> type (primary, secondary, mailbox, residence, shipping, other, unknown)
> >> date
> >>
> >> # the table to record personal phone information
> >> # 1 people record to many phone record relationship
> >> phone_table
> >> record_id
> >> people_record_id
> >> type (personal cell, home, business, home2, business cell)
> >> number
> >> date
> >>
> >> # a table to record emergency contacts
> >> # 1 personal to many personal relationship
> >> emergency_contact_table
> >> record_id
> >> people_record_id (participate)
> >> people_record_id (emergency contact)
> >> relationship
> >> date
> >>
> >> # the authorization table to control access via the
> >> # web interface
> >> authorization_table
> >> record_id
> >> first_name
> >> middle_name
> >> last_name
> >> userid
> >> password
> >> password2
> >> auth_level
> >> email
> >> password_start_date
> >> password_status
> >> date
> >>
> >>
> >>
> >> # a table to record the high level in the hierarchy
> >> # of the award requirement, subrequirement chain
> >> award_table
> >> record_id
> >> name
> >> type (LDS,BSA,other)
> >> level (youth(deacon,teacher,priest,scout,cub,varsity,venture,adult)
> >> max_age
> >> min_age
> >> date
> >>
> >> # a table to record each completed award
> >> # many awards to 1 people relationship
> >> completed_awards_table
> >> record_id
> >> people_record_id
> >> award_record_id
> >> date_completed
> >>
> >> # a table to record which image file relates to which person
> >> # each file is a scanned image of the medical form
> >> medical_form_table
> >> record_id
> >> people_record_id
> >> image_record_id
> >> date
> >>
> >> # a table to record which image file related to which person
> >> # and event Each image is a scan of the completed doc
> >> # many permissions to 1 person relationship
> >> permission_form_table
> >> record_id
> >> people_record_id
> >> event_record_id
> >> image_record_id
> >> date
> >>
> >> # a table to relate people to pictures
> >> # many to many relationship
> >> picture_people_table
> >> record_id
> >> people_record_id
> >> picture_record_id
> >>
> >> # a table to record each picture and metadata about the picture
> >> picture_table
> >> record_id
> >> date_of_picture
> >> location
> >> description
> >> filename
> >>
> >>
> >> # a table to record each scanned image/doc
> >> # 1 people to many images relationship
> >> image_table
> >> record_id
> >> filename
> >> image_date
> >> description
> >> image_owner_people_record_id
> >>
> >> # a table to record event information for calandaring
> >> event_table
> >> record_id
> >> event_start_date
> >> event_end_date
> >> description
> >> group (miamaids, bears, scout, priest etc)
> >>
> >>
> >> _______________________________________________
> >> Ldsoss mailing list
> >> Ldsoss at lists.ldsoss.org
> >> http://lists.ldsoss.org/mailman/listinfo/ldsoss
> >
> > _______________________________________________
> > Ldsoss mailing list
> > Ldsoss at lists.ldsoss.org
> > http://lists.ldsoss.org/mailman/listinfo/ldsoss