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.