|
Review Site User Manual
ADDING COLUMNS TO A TABLE
|
|
« Table of Contents
|
Obtain Review Site »
ADDING COLUMNS TO A TABLE
You may decide that you need to add custom columns to one or more of the tables in Review Site.
If you do, you will find the procedure for modifying the structure of a table
is the same, regardless of the table involved.
On this page we cover in some detail how to add to your tables 6 of the more commonly used column types. You are not restricted to these 6 types but to keep you on track it is STRONGLY RECOMMENDED that you don't
use column types that are not mentioned on this page.
The reason for this is that the administrative back end for Review Site derives from the back end for
Review Foundry which is a much more complex
review application. So there are more options availabe in Review Site for column specification
than there really ought to be. Try to stick to the easily understandable ones so you don't get
confused or end up trying to use an ill-suited column type. If you stick to the column types mentioned
on this page you should be fine.
IMPORTANT: When you go about modifying the structure of a table managed by Review Site, it is important that you use the administrative control panels to carry out the change. The reason for this is that Review Site uses internal representations of the tables to keep track of how the tables relate to each other, and how they are structured. If you carry out a table modification using some other tool, like phpMyAdmin, the internal table definition files will not be in synch with the changes you have made. The only way to correct things in that case is to remove the changes you made with the other application, then reintroduce them the proper way using the Review Site Database control panel. This always turns out to be tricky, so AVOID using other MySQL clients to alter the Review Site tables!!
All of the tables in Review Site have a PRIMARY KEY (which is the INTEGER id column), and each of these tables
has been made available for inspection, and editing, in the Database menu of the Database control panel.
To add a column to a table you first select the "Columns" option of the Database menu (found on the left side of the page), as well as the name of the table to which the column will be added. Clicking on the submit button for this menu will bring up the Column Properties frame for the table, summarizing the existing columns.
At the bottom of this frame you will find a link labeled "Add Column". When you
click on that you will be presented with a form something like the following
(here we'll assume we are dealing with the Review table). The same javascript
constraints that are normally present are embedded in this page too--at least
for the following set of form elements, though not for others later in the
page--so you can try different combinations of properties to see if they are
accepted (although the constraints aren't so exhaustive that you won't be able to
come up with inputs that wouldn't be accepted by Review Site in practice):
For the full definition of each of the form inputs shown above, see
COLUMN PROPERTIES. On the current page will be
provided some examples on how to fill out this form in order to add one column type
or another. Not all possibilities will be covered, just the ones that you are
more likely to use with Review Site. These are the column types
VARCHAR,
TEXT,
INT,
ENUM,
DATE,
FLOAT.
VARCHAR Column
The VARCHAR
column is used when you want to collect a short string. This might be a URL or
a product name, or file name, or something of that nature where you know that the
data can never have more than 255 characters (which is the maximum length of a
VARCHAR field).
As an example, in the Review table we have a Review.product_name column
which is a VARCHAR(128) column. In this case we are enforcing a hard limit of 128
characters to the maximum length of the product name. Note that if you need to collect
text that is likely to run longer than 255 characters, then you will need to use a
TEXT column (see the next column type).
For illustrative purposes, let's assume we want to add the name of a PDF file to the
Review table. This will be a short string like BestWoodenWidgets.pdf
We'll assume that not every Review record is going to have an associated PDF file,
so we'll need to make this an optional record value. We'll insert the new column after
the existing Review.product_description column. We will also assume a maximum
file name length of 64 characters.
Below are the filled
out form elements that might be used to specify our new Review.product_pdf_file column
(note: this form is non-functional and has had excess options removed from it):
Notice that our Not Null value is No because we anticipate
that a PDF file name won't always be available. Also the Form Type we've
chosen is a TEXT form element with a length of 64 characters.
The form elements that don't apply to the specification of a
VARCHAR column are the
Column Values, Default, Search Weight and Form Values.
TEXT Column
The TEXT
column is used when you want to collect a sizeable block of text as one of
your record elements (up to 64 kB). For the Review table we already have a
Review.product_description column. But you might want to add more such columns for
any number of different reasons. At the very least, if you break textual input into
a number of different columns you can position these independently of one another
when it comes to displaying the record. Everything we say here about specifying
a TEXT column also applies to the
TINYTEXT, MEDIUMTEXT, and LONGTEXT
column types too (these are are string column formats but differ in the
maximum input length of the string and the amount of memory required to store the data).
Let's pretend we want to add a Design Specification field which the manufacturer of
an product used to blueprint the product before the manfacturing stage. However, let's
also assume that the Design Specification might not always be known or available,
so we want to allow for the possibility that the spec will not be available when
we come to creating a new Review record (which contains product specifying columns).
We'll insert the new column after the existing
Review.product_description column
Note: If we wanted this column to be searchable, using the FULLTEXT search engine that
comes with Review Site, we would need to add the new column to the search index. This is
covered in more detail on the page SEARCH ENGINE.
Below are the filled
out form elements that might be used to specify our new Review.product_design_specs column
(note: this form is non-functional and has had excess options removed from it):
Notice that our Not Null value is No because we anticipate
missing spec data. Also the Form Type we've chosen is a TEXTAREA form element
with 70 columns and 5 rows (the number of columns and rows are specified as a pair
of comma-delimited integers). If we wanted our Design Specification field to be treated
in a cAse sENsitIVE way we would set the Attributes value to BINARY, rather
than leave it blank (in practice the only time you might specify BINARY strings is
for short strings where case might be important.
The form elements that don't apply to the specification of a
TEXT column are the
Column Size/Precision, Column Values, Default,, Search Weight,
Form Values, and Form Regex.
INT Column
If you need to supply a count of some aspect of your record you would use one of the
integer columns, TINYINT,
INT, SMALLINT, MEDIUMINT, or BIGINT.
We'll assume here that the
INT column type is most suitable
for the new column we're considering, say, because we are recording the number of
units sold, and this could get fairly big. The name of our column will be
Review.product_units_sold. To add our column just after the existing Review.product_thumbnail
column we would fill out the form to look something like the following (note: this
form is non-functional and has had excess options removed from it):
Things to note here are that we've selected
UNSIGNED for the
Attributes element
because we know the number of units sold cannot be a negative integer, so we double
the range of positive integers that can be recorded. By selecting Yes for
the Not Null element we are saying that we will always have a value to go
into the column, even if it is zero.
For collecting the integer value we have specified a
TEXT Form Type with
a size of 10. We've also specified a regular expression to be applied to the column
value at the time the record is created (or modified). This is the ^\d+$
expression in the Form Regex which tests for the presence of one or more
digits only in the column value. If the regular expression test fails, the record
will not be reated (or modified).
The form elements that do not apply when creating an integer column type are:
Column Values, Search Weight, and Form Values.
ENUM Column
Suppose you wish to add a field to your Review table which can take any one of a finite
number of values, and you want the person inputting the record to have to choose the
value from a select menu. The ideal column choice in this case is the
ENUM column
type. Let's suppose the field will represent the size of the product, and that
the product size value for any Review record MUST be one of the 3 possible values small,
medium, or large. To add a Review.product_size column just after, say, the Review.product_color column we would fill out
the form to look something like the following (note: this form is non-functional
and has had excess options removed from it):
Note that the Column Values are separated by linebreaks, and that corresponding
values appear in the Form Values textarea box. These latter values are what the
user will see in the drop-down select menu, and represent the human-readable
values (the Column Values, after all, could have been supplied as numeric quantities,
though in practice sticking to human-readable values there as well is almost always a good
idea if you can manage it). The number of values supplied for the Column Values
and the Form Values should, of course, match. Note also that we have specified
a value for the Default, so that this value will be automatically selected when
the select menu is constructed. As an alternative to the select menu,
we could also have chosen RADIO for the Form Type, instead of SELECT. This
would force the user to click on a radio element when specifying the product size instead
of selecting from a menu.
Note also that we have specified that the column should be NON NULL--every Review record should
have an associated product size. As for the Column Size/Precision, Attributes,
Search Weight, and Form Regex inputs, these are meaningless in the
context of an
ENUM column. Leave them blank.
The Form Size/Length also should not be specified for either a select
menu or radio form element.
Finally, you should ignore the last two form element, which are the Hide on Add/Modify Forms radio elements. These are not used in Review Site.
DATE Column
The DATE,
DATETIME,
TIME,
YEAR, and
TIMESTAMP
columns are similar in behavior. The form elements required to specify one of these
column types are few. Here we suppose we wish to add a Product Date Available field to the
Review table (column Review.product_date_available to go after the supposedly existing
Review.product_color column). We assume the column value might not always be known, so
we allow it to assume the NULL value. The date-like columns have a well-defined
input format which can be translated into a menu format for collecting year,
month, day, hour, minute, and second values by choosing DATE_SELECTABLE for the
Form Type (the range of selectable years can, if necessary, be adjusted
by editing the db_date_field_input.ttml template, found in
/_lib/templates/cpman of your CGI bin area). Otherwise, you can simply select
TEXT for the Form Type to force the user (you) to enter the date in the correct
format, and then check this with a suitable Form Regex condition. For example,
^\d{8}$ might be used to force collection of a date with a 4-digit year
like YYYYMMDD.
Thus we would fill out the form for a DATE column to look something like the
following (note: this form is non-functional and has had excess options removed from it):
While it is possible to add a Default date, you are not likely to want to do
that. The other form elements that have been left blank are not required when specifying
one of the date-like column types.
FLOAT Column
Our final example column addition involves the
FLOAT column type, which is
used to store decimal numbers. There are sibling column types like DOUBLE and DECIMAL,
so check which is best suited to your data before plunging ahead. For a money column
you might want to use DECIMAL, but here we are considering how to add a Product Weight column
to the Review table, where our weight is measured to the nearest 1/100 of a kilogram.
We specify the form elements for our Review.product_weight column as follows (note: this
form is non-functional and has had excess options removed from it):
Notice that we have specified for the Column Size/Precision element the pair of
integers 8,2. This means 8 character is total to display the value, with 2 of these
used for the part appearing after the decimal point. This is also reflected in the
regular expression devised to catch input values that do not conform to the expected
format.
Other Column Types
To reiterate a point made at the top of this page, we have covered in some detail how
to add to your tables 6 of the more commonly used column types. You are not restricted
to these 6 types but to keep you on track it is STRONGLY RECOMMENDED that you don't
use other column types.
The administrative back end for Review Site derives from the back end for
Review Foundry which is a much more complex
review application. So there are more options availabe in Review Site for column specification
than there really ought to be. Try to stick to the understandable ones so you don't get
confused or end up trying to use an ill-suited column type.
To understand all of the
options available to you when adding or modifying columns, study the information
presented in COLUMN PROPERTIES.
Next Section: COLUMN PROPERTIES
« Table of Contents
|
Obtain Review Site »
Copyright © 2004 Random Mouse Software. All Rights Reserved.
|