The Tables for MadamCurry.com
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
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
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.
|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
I have also included fields
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.