[Ldsoss] Scout Tracking
Tom Welch
welchtc at ldschurch.org
Mon Jul 10 12:04:28 EDT 2006
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?
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.
* 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.
* 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.
* 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.
* 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.
* 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.
* On the "authorization_table", I recommend that you lose the
"userid" and use the email address to verify and allow the user 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.
Good start 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
>
>
--
Tom Welch
welchtc at ldschurch.org
(801) 240-1609
(858) 829-4614 - Cell
------------------------------------------------------------------------------
NOTICE: This email message is for the sole use of the
intended recipient(s) and may contain confidential and
privileged information. Any unauthorized review, use,
disclosure or distribution is prohibited. If you are not the
intended recipient, please contact the sender by reply email
and destroy all copies of the original message.
------------------------------------------------------------------------------
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.ldsoss.org/pipermail/ldsoss/attachments/20060710/f1ed1d1d/attachment.htm
More information about the Ldsoss
mailing list