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 [email protected] http://lists.ldsoss.org/mailman/listinfo/ldsoss
