Database Tables!

After looking into options for database management interfaces, I found phpMyAdmin and was able to successfully download and integrate it with my Apache server. Using phpMyAdmin, I constructed a new database ("salonbook") and built all of the tables that I designed. The process was tedious but fairly straightforward.

I ran into some issues trying to figure out what exactly the different "indexing" options are. The different "index" options for a given field are: none, primary, unique, index, fulltext. I realize that for tables with unique id's, these are primary indexes into that table. However, the 'index' option is unclear to me. I'm hypothesizing based on research that it is appropriate for storing id's from other tables in a field, in order to link the data together. For now I used 'unique' for fields that should never have repeats appear in the table. One example would be email addresses for users; two users should never be able to have the same email address. I also looked into what 'fulltext' means, and I discerned that it allows you to search and sort data based on string matching. These indexing issues are important, and I plan on teasing out the details with Professor Nimeroff when I discuss this with him.

Another issue was whether to store "notes" as text types or as varchar types. I looked into the definitions and limitations on the two types, and they both have pros and cons. Varchars will truncate padding on the end of input, whereas text types will not do that. Text types have limited size, whereas varchars don't. Still unsure which I'm going to use though.

Date/Time types are also a mystery to me right now. Not sure which type to use between: date, time, datetime, timestamp. For now I used type 'datetime' for appointment times.

In order to avoid having sets or enums within tables, I established tables that feature many-to-one and many-to-many relationships. Examples are salon_stylist_h_service and salon_b_service. This way their may be many table entries with the same stylist and/or salon and/or service, but never a repeat of any combination of these three values. This relationship allows for much more flexibility in terms of the use cases that can be handled. With this design, for example, I can represent the circumstance where a stylist works at various salons, and that he offers a different set of services at each salon.

I'm not sure if I am or am not going to store hair appointment information. Google Calendars can provide me with equivalent back-end functionality. However, I built the tables for now, if nothing else to maintain customer history information (which may prove valuable to salons down the line).

Here are some screenshots of my database and data tables I built:

salonbook database and its tables:

"user" table and its fields:

"state" table (with all 50 states entered into the table):

"neighborhood" table (with a few familiar neighborhoods entered):

*Because I store the 'city id' in the neighborhood table, it has indirect knowledge of the city to which the neighborhood belongs, and the city knows its state. So because of this structure, a neighborhood knows what city and state its in without explicitly storing city or state name in the "neighborhood" table.

The next thing I need to look into and work on is setting up and learning how to integrate ADO DB. It'll play an integral link in passing information between my database and my interface.

The New Plan

After research, thought, and discussion with Professor Nimeroff, I've formulated a plan that shifts the focus of my work. 

The mere scheduling aspect of the site is not novel, and the management of scheduling data in my own database would be extremely tedious and time consuming. So, I decided to look into the offerings of various calendar toolkits already available on the web. It turns out Google Calendars has all of the functionality that I would need for the scheduling aspect of the site. Google Calendars supports creation of new calendars, adding and deleting events (including recurring events), and viewing the schedule. The API for this toolkit is openly available online and will prove to be very helpful in harnessing this technology towards my project.

http://code.google.com/apis/calendar/docs/2.0/developers_guide_protocol.html#AuthAuthSub

While this calendar application will be very useful, my project will extend far beyond the simplicity of a generic calendar application. SalonBook is more than just a scheduler, it is an online salon management tool and salon community. There will be three distinct interfaces customized for three distinct groups of users. Salons, stylists, and clients will all create accounts and experience SalonBook is a different way. Below are the "use cases" that my site will set out to handle, organized by user type:

After thinking through all of the use cases, I had to revisit my data tables in order to capture all of the data I would need. The use cases dictate what data you will need access to at what points in a user experience, and so as stepped through the data tables as dictated by the use cases, I found many deficiencies in my initial tables. In order to allow for more flexible relationships between salons, services, and stylists, I created tables with "many-to-one" relationships that could represent a wide array of cases without redundancies. I also decided to split "services" into two separate categories: hair services and beauty services. The two aspects are distinctly different when it comes to salon management and schedule organization. It became very messy to try to treat them as different instances of the same "service" object. Below is a screenshot of my new set of data tables as I've designed them so far:

Its clear from this that the scope and functionality of my website will go far beyond a generic scheduler. Google Calendars will assist in back-end management of only a small portion of the data being stored, managed, and queried. Building the three distinct user interfaces and linking all of these pieces and data together will be a substantial undertaking. Thus, Professor Nimeroff suggested Amazon Web Services as a potential way of offloading the grudge work of managing my own database. It turns out Amazon has a service called SimpleDB that is perfect for the needs of this project. SimpleDB allows developers to create and store data tables, then access and manipulate that data with simple function calls from my program.

http://aws.amazon.com/simpledb/

Using SimpleDB will allow me to focus my efforts on the interfaces and pull together a rich, cohesive, and novel application. To do this, I'm going to employ OpenLaszlo. It's syntax is relatively straightforward and is capable of deploying applications in a number of different formats, abstracting away gritty compatibility issues.

So, this is the new plan. The next steps from here are to finalize the data tables and use cases, and to create a features matrix with all the features I want for my application against all the various tools that already exist and are relevant to this project.