tjameswhite.com

In my first installment I related the thought process I went through to arrive at a first-pass database structure. Patrick help further refine the structure, and in general he helped me better understand how relational databases work.

For example, I was only thinking of the database in terms of storage, not how data would get in and out. It’s not that I didn’t think of that part, my thoughts were merely “OK, data gets stored in the database and I’ll access it via the Web with a script.” What I didn’t do was examine the steps that needed to be taken to achieve that. Once Patrick got me to conceptualize, even on a broad scale, that there would be an input form and a search/sort form the relations between the different tables began to make more sense.

See, I understood the separate tables to store redundant data, but I had only a faint idea of what a bridge table was for and didn’t really think that I’d still need to access each table individually. Seeing how input and output forms would work made all of that click. I realized that my script would need to access thing 1 from here and thing 2 from there and put them together somewhere else.

Out of our meeting came a requirements document and a (slightly) modified table set:

Wish List

Purpose

• Share wish lists for family members with other via the Web

Requirements

• Ability to sort on name
• Ability to sort on category
• Ability for family members to update lists via simple web form
  1. Enter item
  2. Select details
  3. Submit
• Admin screen for db management

Functionality

• PHP form read/write to MySQL database
• Item <input> writes to table
  1. Check if exists; if not add
  2. If exists, alert/update
• Category & people <select> fields filled in via table providing authority control

Database: Wish List (4 tables)

[Item]: Item_ID (pk), Item_Desc, Item_Name, Item_URI, Item_Bought, Item_Cat_ID (fk)
[Category]: Cat_ID (pk), Cat_Name
[People]: People_ID (pk), People_Name
[Wish (bridge table)]: Wish_ID (pk), Wish_People_ID (fk), Wish_Item_ID (fk)

Now all I need to do is a) figure out how to build the database, and b) figure out how to read/write to it. Time to read the next chapter.