Re: [Ldsoss] Scout Tracking

[Tom Welch]

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 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@lists.ldsoss.org http://lists.ldsoss.org/mailman/listinfo/ldsoss _______________________________________________ Ldsoss mailing list Ldsoss@lists.ldsoss.org http://lists.ldsoss.org/mailman/listinfo/ldsoss -- Tom Welch [EMAIL PROTECTED] (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. ------------------------------------------------------------------------------

_______________________________________________
Ldsoss mailing list
Ldsoss@lists.ldsoss.org
http://lists.ldsoss.org/mailman/listinfo/ldsoss