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