Results 1 to 7 of 7
  1. #1
    Yup, Sure ... now let me check ... Cagles Mill's Avatar
    Join Date
    January 18th, 2005
    Location
    The Midwest
    Posts
    646
    Seeking advice on building a database for the local library
    Although I have been doing affiliate marketing full-time for over 3 1/2 years now, I recently took on a part-time job in the Local History and Genealogy Dept. at the local library. I'm putting in about 17 hours a week there. My wife has been wanting me to do something to get out of the house more (spelled getting out of her hair) and I felt as if I needed to do something to get myself back into circulation with other people more. Anyway, I have been into genealogy for years and I have always enjoyed history. So although I am treating the position seriously as a job, I actually think of it more as a hobby.

    Since the library knows I build websites, they want me to try and build a website for the Local History and Genealogy Dept. This would be totally independent of the library's own site. I have pretty much already put the site together, but now I'm ready to get down to the brass tacks of constructing a database for them, or rather several databases.

    So, here's the problem. What would you pros consider the best way to handle this?

    One of the department's collections and the one they want to put on line first, consists of a file cabinet filled with about 300 folders (called the Boatright files). Each folder is labeled with one or more family last names. These folders contain any number of pages consisting of any and all information the Boatrights were able to gather on these local families. The library wants to scan all these pages and put them on line as images. Maybe someday they may want to actually try typing it all in, but that seems rather unlikely.

    Anyway, the Boatrights built a 6-drawer index card file for all these folders. Each index card is titled with a last name and then cross-references all the folders in which that last name appears. There are many names which do not have their own folders, but only show up in the folders of other families.

    So what would be the best way to try and organize such a system? PHP with MySQL seems the most logical way to go, yet there would seem to be problems with it. A given index card may point to just one folder or 60 folders. Trying to build a table with 1 to 60 cross-references within each record seems rather clumsy and inefficient. Not to mention it is hard to get into my head exactly how to structure such a table. A PHP search script (or a Perl script) without a table at all would seem a lot easier.

    Last, but not least, this is a 2-tier problem. I need to design the whole system in a way that would make it easy for other people to update the system as needed. This would seem to mean I need to build an interface program on top of the database program.

    Any suggestions?
    Rick M.
    I would rather have a bottle in front of me, than have a frontal lobotomy!
    Does your bubblegum lose its flavor on the bedpost overnight?

  2. #2
    ABW Ambassador meadowmufn's Avatar
    Join Date
    January 18th, 2005
    Location
    Seattle
    Posts
    2,587
    Quote Originally Posted by Cagles Mill
    Anyway, the Boatrights built a 6-drawer index card file for all these folders. Each index card is titled with a last name and then cross-references all the folders in which that last name appears. There are many names which do not have their own folders, but only show up in the folders of other families.

    So what would be the best way to try and organize such a system? PHP with MySQL seems the most logical way to go, yet there would seem to be problems with it. A given index card may point to just one folder or 60 folders. Trying to build a table with 1 to 60 cross-references within each record seems rather clumsy and inefficient. Not to mention it is hard to get into my head exactly how to structure such a table. A PHP search script (or a Perl script) without a table at all would seem a lot easier.
    Assuming I understand the setup correctly, I would have a table for the last names, a table for the folders, and then a table which would map the names to the folders.

    For example, both the last name table and the folder table have a numerical primary key that's automatically incremented each time a new record is added. The mapping table would look something like this:

    map_id as integer (primary key for the mapping table)
    lname_id as integer
    folder_id as integer

    Then, to find out which folders the last name appears in, simply select all records from the mapping table for the primary key (lname_id) for the last name you're searching on.

    Hope that helps!
    -Don't criticize anyone til you've walked a mile in their shoes. Then when you do criticize them, you'll be a mile away and have their shoes.
    - Silence is golden. Duct Tape is silver.

  3. #3
    Yup, Sure ... now let me check ... Cagles Mill's Avatar
    Join Date
    January 18th, 2005
    Location
    The Midwest
    Posts
    646
    Thanks for the suggestion, meadowmufn. I had not given any thought to using more than one table and certainly had not thought about using 3 tables. As it turns out, just trying to describe the problem on the board here helped me to sort the idea out in my head a bit.

    The search output would look something like this:

    Folder assigned that Name (if a folder exists for that name)
    Another folder containing that name
    Yet another folder containing that name
    etc.

    If a name does not have its own folder, but shows up at least one time in another folder, then that other folder were appear in the search results.

    I'm probably being dense, but although the 3 table idea sounds interesting, I'm not sure how that would work exactly. If a name points to 20 other folders, where would those 20 pointers be placed?
    Rick M.
    I would rather have a bottle in front of me, than have a frontal lobotomy!
    Does your bubblegum lose its flavor on the bedpost overnight?

  4. #4
    ABW Ambassador meadowmufn's Avatar
    Join Date
    January 18th, 2005
    Location
    Seattle
    Posts
    2,587
    Those 20 pointers would be put in the mapping table. Here's an example.

    LName_Table (primary key, last name, default folder)
    1 Smith Folder_QRS (you could also use just the folder primary key here)
    2 Jones Folder_ABC
    3 Brown

    Folder_Table (primary key, folder name)
    1 Folder_ABC
    2 Folder_QRS
    3 Folder_XYZ

    Mapping_Table (primary key, lname key, folder key)
    1 1 3 (Last name Smith is found in Folder_XYZ)
    2 1 1 (Last name Smith is found in Folder_ABC)
    3 2 2 (Last name Jones is found in Folder_QRS)
    4 3 1 (Last name Brown is found in Folder_ABC)

    If you want to add a folder for Smith, say Folder_LMN... add Folder_LMN to the folder table. It will have a primary key of 4. Then, in the mapping table add an entry for that mapping... it will look like:
    5 (mapping table primary key) 1(primary key for Smith) 4(primary key for Folder_LMN)

    You can add as many folders as you like to the folder table and as many last name mappings to them in the mapping table.

    Then, when you look for all the folders for Smith, you would select all entries from the mapping table where the lname key is 1. So, you'd get a record set that would tell you that the folders where Smith exists are folders with the primary keys 3, 1, and 4. That corresponds to Folder_XYZ, Folder_ABC, and Folder_LMN.

    Let me know if that makes sense to you or not.
    -Don't criticize anyone til you've walked a mile in their shoes. Then when you do criticize them, you'll be a mile away and have their shoes.
    - Silence is golden. Duct Tape is silver.

  5. #5
    Yup, Sure ... now let me check ... Cagles Mill's Avatar
    Join Date
    January 18th, 2005
    Location
    The Midwest
    Posts
    646
    Thanks for the clarification, meadowmufn. I think I understand what you're saying. I may need to try building three tables with just a few sample records each in order to play around with the idea and get it to really sink into my mind.

    As for understanding the problem, you seem to have a good grasp of it. Heck, you may even understand it better than I do.
    Rick M.
    I would rather have a bottle in front of me, than have a frontal lobotomy!
    Does your bubblegum lose its flavor on the bedpost overnight?

  6. #6
    ABW Ambassador meadowmufn's Avatar
    Join Date
    January 18th, 2005
    Location
    Seattle
    Posts
    2,587
    Yes, definitely play around with it using actual tables. I tackled the same problem for one of my affiliate marketing sites. How do you categorize a product when it fits into more than one of your established categories? What I showed you is the solution I came up with.
    -Don't criticize anyone til you've walked a mile in their shoes. Then when you do criticize them, you'll be a mile away and have their shoes.
    - Silence is golden. Duct Tape is silver.

  7. #7
    ABW Ambassador PatrickAllmond's Avatar
    Join Date
    September 20th, 2005
    Location
    OKC
    Posts
    1,219
    What you guys are actually referring to is something called an 'intersect table'. It is needed when you have to build a many to many relationship.

    Meadow has you on the right track. The only thing I would add is:

    - The intersect table probably does not need a seperate primary key. It's key can consist of the two other columns s/he mentioned

    - Look into using foreign keys (which map back to primary keys on the other two tables) on the intersect table. This will *always* insure that you only use values that are in lname table and the folder table

    - If you really want to make your system organized (and learn a bunch in the process) look into using PHP objects. That will get you started into the world of object oriented programming. Pretty advanced, but very worth it.
    ---
    This response was masterly crafted via the fingers of Patrick Allmond who believe you should StopDoingNothing starting today.
    ---
    Focus Consulting is where I roll | Follow @patrickallmond on Twitter
    Search Engine Marketing | Search Engine Optimization | Social Media | Online Video

  8. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. New and Desperately Seeking Advice
    By PleasantSolutions in forum Midnight Cafe'
    Replies: 8
    Last Post: March 15th, 2012, 04:59 PM
  2. Seeking Advice
    By Shoe-Shopping-Spree in forum Starting an Affiliate Program & Merchant Q&A
    Replies: 1
    Last Post: June 6th, 2011, 12:40 AM
  3. New Merchant Seeking Advice
    By ericak in forum Introduce Yourself
    Replies: 3
    Last Post: June 15th, 2007, 04:00 PM
  4. Hi I am New - seeking advice
    By cashuser in forum Introduce Yourself
    Replies: 8
    Last Post: June 12th, 2006, 12:50 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •