Dan Knight
- 2002.05.13
The Deal of the Day database will need several fields:
- ID
- URL
- description
- short description
- extra
- date
The ID will be a unique integer that automatically increments
itself. The next four fields will contain text and be of type
char, which allows them to be up to 256 bytes long. The
date field is the date that the deal should be displayed and will
be of type date.
Dates in MySQL
There are many ways to enter dates. Here in the States, most
people would write today's date at 5/13/02. In most other
English-speaking regions, it would be written 12/5/02. Since the
Web is international, that gets confusing in a real hurry.
To address this, Low End Mac began by dating articles like this:
13 May 2002. No confusion, but a mess if you want to sort dates. We
later settled on the format YYYY.MM.DD, since it simplifies
sorting. It's also an international standard.
While Unix uses a timestamp, that number needs to be converted
to years, months, days, hours, minutes, and seconds so we humans
can make sense of it. MySQL and PHP also support the YYYY-MM-DD
format for dates. Since we're not worried about date stamps and
want to make it easy to schedule our Deal of the Day for a specific
date, that's the format we'll use.
Creating Six Fields
We've got six fields. After analyzing field lengths from our
specials of the day over the past months, we came up with the
following:
- ID, just a simply integer that automatically increments
- URL, the URL for the day's special, up to 128 characters
long
- text, our long description of the day's special, up to 64
characters
- short, a shorter description for the navigation bar, up to 32
characterss
- extra, used for tracking code required by some affiliate
programs, up to 128 characters
- date, the date the special is scheduled to appear, this is also
the index field for our database
That gives us a template to pour our data into - except that in
this case there isn't a text file or database to import. We'll be
adding our Deal of the Day one at a time, so the next project is
designing a Web-based front end for editing our database and adding
new records.
