Common Lookup
Week 3: Reading
The following are the reading articles:
Common Lookup Table Basics
Learning Outcomes
- Learn how to design common lookup tables.
- Learn how to create common lookup tables.
Lesson Materials
A lookup table is a generalization that holds lists of values that support end-user selections. The following example uses a combination of the common_lookup_table
and common_lookup_column
columns to identify sets of value for drop down lists. The end-user selects a value from the list to identify a unique row, and returns a common_lookup_id
surrogate key value.
The implementation of a common lookup table requires the use of an HTML SELECT tag and OPTION values drawn from the database. The following illustrates the use of a common lookup in a simple HTML drop down list.
The drop down list actually maintains information not shown in the web page that lets the application find the last value selected and final value selected when you submit the web form.
Applied
This post goes to the next level, and shows how to use foreign keys to preselect values for display in web forms. It also rewrites the prior function so that it supports querying existing data and inserting new data.
Let’s start with data stored in join between two tables – the member
and contact
tables. The internal lookup uses the customers name from the contact
table to find the membership account information in the member
table.
1 2 3 4 5 6 7 8 9 10 11 12 13 | SELECT m.account_number , m.member_type -- A fk to common_lookup table. , m.credit_card_number , m.credit_card_type -- A fk to common_lookup table. , c.first_name , c.middle_name , c.last_name , c.contact_type -- A fk to common_lookup table. FROM member m INNER JOIN contact c ON m.member_id = c.member_id WHERE c.first_name = 'Harry' AND c.middle_name = 'James' AND c.last_name = 'Potter'\G |
It returns the results on the left, while a set of joins against the common_lookup
table returns the results on the right (both use the \G
in SQL Monitor to display the data vertically).
The member_type
, credit_card_type
, and contact_type
columns in the data set on the left hold foreign key values. They’re copies of values found in the primary key column of the common_lookup
table. You have the option of using these values to connect the data through a join or through function calls. A join requires three copies of the common_lookup
table and yields the data displayed on the right above. The query to get the meaningful business information from the common_lookup
table is:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | SELECT m.account_number , cl1.common_lookup_meaning , m.credit_card_number , cl2.common_lookup_meaning , c.first_name , c.middle_name , c.last_name , cl3.common_lookup_meaning FROM member m INNER JOIN contact c ON m.member_id = c.member_id INNER JOIN common_lookup cl1 ON cl1.common_lookup_id = m.member_type INNER JOIN common_lookup cl2 ON cl2.common_lookup_id = m.credit_card_type INNER JOIN common_lookup cl3 ON cl3.common_lookup_id = c.contact_type WHERE c.first_name = 'Harry' AND c.middle_name = 'James' AND c.last_name = 'Potter'\G |
The data returned from any query is symmetrical, which means all columns return the same number of rows. The results of the preceding query are the business results. Although, they’re not what you’d want to display in a web form that presents the ability to change values, like the member, credit card, or contact types. You need to get that information by using the foreign key as a parameter to a function call, and in this case three function calls. One for each of the foreign keys from the original query results. The result is an asymmetric collection of data would look like the following conceptually with Lily Luna Potter as the customer (note the green row is the symmetrical return set from the preceding query):
The f(x)
represents a generic function call where the x
substitutes for the foreign key value as a lookup key to the primary key value of the common_lookup
table. The function in this case is a query that returns a two column list. One column holds the primary key value of the (common_lookup_id
) and the other holds the customer facing description (from the common_lookup_meaning
column).
These steps describe the process:
- Use the natural key (the user name) to find the data in the
contact
table. - Use the
member_id
foreign key column in thecontact
table to link to the same name column primary key in themember
table by joining the two rows. - Use the foreign keys in the new row (combining columns from the
contact
andmember
tables) as call parameters to a PHP function that returns all possible foreign key values and their business descriptions in a web form.
The vertical choices displayed above map to OPTION
tag elements of an HTML SELECT
tag. The blue highlighted value contains an instruction, Select Type, in the display of an HTML OPTION
tag, and it holds a null as the value of the VALUE
attribute for the OPTION
tag. The other displayed rows are the possible values. The green highlighted value is the currently selected value and the yellow highlighted values are alternate possibilities for an end-user to select. The logic for that process is in the PHP get_common_lookup
function below.
Having discussed the structure of the data and problem, we’ll present the result in a dynamically constructed web form below. The display form shows a member account with customer information.
You should note that the primary and foreign keys aren’t displayed because they’re irrelevant to the business process. Primary and foreign keys only serve to support relationships when we use surrogate keys as the primary key of a table. Only the meaningful information from the common_lookup
table are displayed in the preceding form. Behind the web form, the primary and foreign key values are critical to maintaining anomaly free table data.
Each of the drop downs contains the full selection of possibilities from the common_lookup
table, and an end-user could choose a new value by clicking on any of the drop down fields. For example, the following displays the selection of a type of credit card:
The user can click on the drop down, and then navigate from the selection to a new selection. Assuming we don’t change anything, submitting the form should transmit the foreign key column values.