The Tables for MadamCurry.com

Some Background. For some background, the tables for MadamCurry.com turn out to really be quite simple. When developing for the web, the most difficult work was in developing the interfaces for handling the sales and email list development. Developing the slide shows was also somewhat involved, though not anywhere near as involved as for developing the interfaces for sales and email. I won't go into more detail about this until after we've developed the client database, tables, forms and some queries and reports.

The entities we will focus on in this example are

customers for the email list

purchasers

These correspond to the tables in our relational database. The attributes are also called fields. A data dictionary lists all of the attributes and their data types for an entity. In other words, it lists all the fields and their data types for a table in a relational database.

First we will delineate the fields we want for the e-mail list.

Notice that I have used text fields to contain things like zip codes, phone numbers and so on. Number formats tend to be somewhat less reliable in a number of different settings and uses. So it really is common practice to treat a fairly large variety of things we consider to be numbers as text within a database.

I have established id_email_list as the key. It is a unique identifier for each row of the table. As is fairly common practice, it is configured as an autonumber field so that each new entry will get a number one higher than the previous highest number. In this sort of situation there is no reason to use something more uniquely associated with a user such as their social security number, which the customer shouldn't give to you anyway!

I have also included a field fk_id_purchaser. This field will link back to the id_purchaser of purchasers that choose to be included on the email list in the purchasers table.

If this was a web database we would also need to include a password so that the user could access their own information.

You should also notice that I have configured that several "numbers" should be stored as text. This makes the entries much more stable. If you use numbers for things like phones or zipcodes you can get some undesired and inadvertent computations happening.

You should also notice that I have included quite a few fields you may not really feel are needed. This is also fairly common practice. It is much more difficult to collect more information at a later time and it is also more difficult to restructure a database and/or table after it has started being populated. You can configure the programming code associated with your input forms to allow some fields to be left blank while others are required.

The data for the book purchasers will be largely similar, but we need to differentiate between shipping and billing information.

	id_purchaser	autonumber
	bill_first_name	text
	bill_middle_name	text
	bill_last_name	text
	bill_street_address	text
	bill_city	text
	bill_state	text
	bill_zipcode	text
	bill_phone_home	text
	bill_phone_business	text
	bill_phone_cell	text
	bill_email_address	text
	include_in_email_list	yes/no
	bill_same_ship	yes/no
	ship_first_name	text
	ship_middle_name	text
	ship_last_name	text
	ship_street_address	text
	ship_city	text
	ship_state	text
	ship_zipcode	text
	ship_phone_home	text
	ship_phone_business	text
	ship_phone_cell	text
	ship_email_address	text
	date_of_purchase	date/time
	amount	currency

I have also included fields

include_in_email_list to denote whether this purchaser wants to be included in the email list
bill_same_ship to denote whether the billing information is the same as the shipping information

While we won't get into this until quite a bit later, you should think about how one might automatically include a purchaser in the email_list table if they check this box. You should also think about how one can automatically fill in the shipping information to match the billing information if the purchaser checks that the billing and shipping info are the same.

Again, I have included quite a few fields that probably should be considered overkill. What fields are actually required from the purchaser can be controlled by the programming controlling the form.

	id_email_list	autonumber
	fk_id_purchaser	number
	first_name	text
	middle_name	text
	last_name	text
	street_address	text
	city	text
	state	text
	zipcode	text
	phone_home	text
	phone_business	text
	phone_cell	text
	email_address	text