ch12.htm

好书《C++ Builder高级编程技术》

<P>Even if you don't yet understand how databases work, for now I would suggest automatically
adding a simple numerical value in a primary index to all your tables. Do so even

if you are not using the field at this time. Believe me, as you come to understand
relational databases, you will see why I recommend doing this in most, though not
all, cases. At this point, however, you will probably be better off creating the
extra 
field and letting it go to waste, even if you don't understand why you are
doing it. After you get a better feeling for relational databases, you will understand
intuitively when the field is needed, and when you are encountering one of those
rare 
occasions when it is going to be useless.</P>
<P>When people first work with relational databases, they can get a little hung up
about the overhead involved in creating all these extra key fields. The point to
remember is that these fields allow the 
database to be treated as nothing more than
sets of simple integers related together in various combinations. Computers fly through
integer math. Adding these extra index fields to your tables makes your data become
computer-friendly. Computers love 
those simple integer fields; your computer will
show its thanks by running faster if you add them to your tables!</P>
<P>Computers don't feel weighed down by the extra field any more than a car feels
weighed down by a steering wheel, people feel 
weighed down by their hands, or a rose
bush feels weighed down by a rose. Relational databases want you to add an extra
integer field as a primary index to your tables!</P>
<P>Remember, people like beautiful paintings, eloquent words, and lovely 
members
of the opposite sex. Computers like logic. They like numbers, they like nice, clean,
easily defined relationships! They like simple, integer-based primary keys in the
first field of a table!
<H4><A NAME="Heading19"></A><FONT 
COLOR="#000077">One-to-Many Relationships: The
Data and the Index</FONT></H4>
<P>One good way to start to understand relational databases is by working with the
<TT>Customer</TT>, <TT>Orders</TT>, <TT>Items</TT>, and <TT>Parts</TT> tables from
the 
<TT>BCDEMOS</TT> database. All four of these tables are related in one-to-many
relationships, each-to-each. That is, the <TT>Customer</TT> table is related to the
<TT>Orders</TT> table, the <TT>Orders</TT> table to the <TT>Items</TT> table, and
the 
<TT>Items</TT> table to the <TT>Parts</TT> table. (The relationship also works
in the opposite direction, but it may be simpler at first to think of it as going
in only one direction.) <BR>
<BR>

<TABLE BORDER="1">
	<TR ALIGN="LEFT" rowspan="1">
		<TD 
WIDTH="86" ALIGN="LEFT"><B>Master</B></TD>
		<TD WIDTH="76" ALIGN="LEFT"><B>Detail</B></TD>
		<TD ALIGN="LEFT"><B>Connector (primary key and foreign key)</B></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD WIDTH="86" 
ALIGN="LEFT"><TT>Customer</TT></TD>
		<TD WIDTH="76" ALIGN="LEFT"><TT>Orders</TT></TD>
		<TD ALIGN="LEFT"><TT>CustNo</TT></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD WIDTH="86" ALIGN="LEFT"><TT>Orders</TT></TD>
		<TD WIDTH="76" 
ALIGN="LEFT"><TT>Items</TT></TD>
		<TD ALIGN="LEFT"><TT>OrderNo</TT></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD WIDTH="86" ALIGN="LEFT"><TT>Items</TT></TD>
		<TD WIDTH="76" ALIGN="LEFT"><TT>Parts</TT></TD>
		<TD ALIGN="LEFT"><TT>PartNo</TT></TD>
	
</TR>
</TABLE>
<BR>
<BR>
Read the preceding table as a series of rows, starting left and moving to the right,
as if they were sentences. The preceding list shows that the <TT>Customer</TT> and
<TT>Orders</TT> tables are related in a one-to-many 
relationship, with <TT>Customer</TT>
being the master table and <TT>Orders</TT> being the detail table. The connector
between them is the <TT>CustNo</TT> field. That is, they both have a <TT>CustNo</TT>
field.</P>
<P>The <TT>CustNo</TT> field is the 
primary key of the <TT>Customer</TT> table and
the foreign key of the <TT>Orders</TT> table. The <TT>OrderNo</TT> field is the primary
key of the <TT>Orders</TT> table and a foreign key of the <TT>Items</TT> table. The
<TT>PartNo</TT> field is the 
primary key of the <TT>Parts</TT> table and a foreign
key of the <TT>Items</TT> table.</P>
<P>The relationship between these tables can be reversed. For instance, the <TT>Parts</TT>
table could become the master table and the <TT>Items</TT> table the 
detail table,
and so on, back down the line. The reason you can reverse the relationship becomes
clear when you think in purely mathematical terms. The <TT>Customer</TT> table has
a series of <TT>CustNo</TT> fields. Say the <TT>CustNo</TT> for the 
first record
is <TT>1000</TT>. To get the <TT>Orders</TT> associated with that customer, you ask
this question: &quot;What are all the rows from the <TT>Orders</TT> table that have
a <TT>CustNo</TT> of <TT>1000</TT>?&quot; That is:</P>
<PRE><FONT 
COLOR="#0066FF">Select * from Orders where CustNo = 1000

</FONT></PRE>
<P>Clearly, you could reverse this question. If you select a particular row from
the <TT>Orders</TT> table, you could find which item from the <TT>Customer</TT> table
it is 
related to by asking for the set of all <TT>Customer</TT> records with a <TT>CustNo</TT>
of <TT>1000</TT>. Because the <TT>CustNo</TT> field for the <TT>Customer</TT> table
is a unique index, you will get only one record back. However, the way you 
relate
the tables is still the same:</P>
<PRE><FONT COLOR="#0066FF">Select * from Customer where CustNo = 1000

</FONT></PRE>
<H4><A NAME="Heading20"></A><FONT COLOR="#000077">Working with Primary Keys</FONT></H4>
<P>The <TT>Parts</TT>, 
<TT>Orders</TT>, <TT>Items</TT>, and <TT>Customer</TT> tables
have various keys. As it happens, these keys are also indexes. An index enables you
to sort tables on a particular field. A key helps you define the relationship between
two tables, or 
otherwise group related bits of information by a set of predefined
and automatically enforced rules.</P>
<P>Unfortunately, sadly, and confusingly, you can still relate tables even without
the presence of any keys or indexes. For instance, if there 
were no <TT>CustNo</TT>
primary and foreign keys in the <TT>Customer</TT> and <TT>Orders</TT> tables, Paradox
would still let you use SQL to relate the tables in a one-to-many relationship. However,
in this scenario, performance would be slow because 
there is no index, and there
would be no constraints on the data you could enter in the two tables because there
would be no primary and foreign keys that define referential integrity. In this scenario
you are back to the rosebush-without-a-rose 
phenomena. Yes, the tables are still
part of a relational database, but they lack the features that make a relational
database appealing. You need both the keys and the indexes to make a relational database
appealing.</P>
<P>I'll draw a distinction 
between only two different kinds of keys. The first kind
I will discuss is called a primary key. The second is called a foreign key.

<UL>
	<LI>A primary key is a unique value used to identify a record in a table. It is usually
	numerical, and it is 
usually indexed. It can be combined with a foreign key to define
	referential integrity. I will talk more about referential integrity later in this
	chapter.
	<P>
	<LI>Because it is indexed, the primary key defines the default sort order for the
	
table. When you first open up a table, it will be automatically sorted on this field.
	If a table does not have a primary index, records will appear in the order in which
	they were added to the table. For all practical purposes, a table without an 
index
	has no defined order in which records will appear.
	<P>
	<LI>With Paradox tables, each entry in the primary index must be unique. That is,
	you can't have two <TT>CustNo</TT>s in the <TT>Customer</TT> table that are the same.
	You can, however, 
have multiple foreign keys that are not unique.
	<P>
	<LI>It is legal to have multiple fields in the primary index of a Paradox table.
	This is called a composite index. These fields must be sequential, starting with
	the first field in the table. You 
can't have the primary index consist of the first,
	third, and fourth fields of a table. A composite index with three fields must consist
	of the first, second, and third fields. If you have a <TT>FirstName</TT> and a <TT>LastName</TT>
	field in your 
database, they can both be part of the primary index. You should, however,
	declare the <TT>LastName</TT> before the <TT>FirstName</TT>, so that your index will
	list people alphabetically by last name: <TT>CustNo</TT>, <TT>LastName</TT>, 
<TT>FirstName</TT>.
	<P>
	<LI>The primary and foreign keys are never composite. They always consist of one
	field.
</UL>

<P>Creating a primary key enables you to have two people with the same name, but
with different addresses. For instance, you can 
list a John Doe on Maple Street who
has a <TT>CustNo</TT> of <TT>25</TT>, and a John Doe on Henry Street who has a <TT>CustNo</TT>
of <TT>2000</TT>. The names may be the same, but the database can distinguish them
by their <TT>CustNo</TT>. Once again, 
this shows why databases love those simple
integer indexes. If the database had to sort on the address fields every time it
tried to distinguish these two John Does, it would take a long time for the sort
to finish.</P>
<P>Computers can easily 
distinguish the number <TT>25</TT> from the number <TT>2000</TT>,
but it takes them longer to do a string compare on <TT>&quot;Maple Street&quot;</TT>
and <TT>&quot;Henry Street&quot;</TT>. Furthermore, just comparing the streets wouldn't
be enough; 
you would also have to compare cities, states, and so on. If two entries
with the same name were both missing addresses, the whole system would be in danger
of falling apart altogether. The same thing would happen if two people named John
Doe lived at 
the same address. Use those integer indexes; they make your life simpler!
<H4><A NAME="Heading21"></A><FONT COLOR="#000077">Working with Secondary Indices
and Foreign Keys</FONT></H4>
<P>It's now time to move on to a consideration of foreign keys. The 
<TT>CustNo</TT>
field of the <TT>Orders</TT> table is a foreign key because it relates the <TT>Orders</TT>
table to the primary key of the <TT>Customer</TT> table. It is also a secondary index
which aids in sorting and searching through data. Indices 
also speed up operations
such as joins and other master-detail relationships.</P>
<P>When writing this section, I have found it difficult to totally divorce the idea
of foreign key and secondary indexes. However, I will try to split them up into two

categories, taking foreign keys first:

<UL>
	<LI>A foreign key provides a means for relating two tables according to a set of
	predefined rules called referential integrity.
	<P>
	<LI>In Paradox, you use the Referential Integrity tools from the 
Database Desktop
	to define foreign keys. There is no such thing as a composite foreign key.
	<P>
	<LI>Using SQL you can relate two tables in a one-to-many relationship even if there
	is no index or key in either table. However, your performance will 
be better if you
	have indexes. There will be no way to enforce referential integrity if you don't
	define foreign and primary keys.
	<P>
	<LI>Using the <TT>TTable</TT> object, it is impossible to relate two tables in a
	one-to-many relationship 
without indexes. (This is one of the points that doesn't
	clearly belong in either the section on keys, or the one on indexes. It relates to
	both subjects.)
</UL>

<P>Here are some facts about secondary indexes:

<UL>
	<LI>A secondary index provides 
an alternative sort order to the one provided by the
	primary key.
	<P>
	<LI>You need to explicitly change the index if you want to switch away from the primary
	index to a secondary index. Remember that the default sort order for a Paradox table
	is 
provided by the primary index. If you want to switch from the primary index to
	a secondary index, you need to change the <TT>IndexName</TT> or <TT>IndexFieldName</TT>
	property of your table. If you want to use the primary index, you don't have to do
	
anything; the table will sort on that field automatically.
	<P>
	<LI>An index that contains more than one field is called a composite index. You can
	create composite secondary indexes, which means the indexes will contain multiple
	fields. In 
practice, fields such as <TT>FirstName</TT> and <TT>LastName</TT> can
	often be part of a secondary index, because your primary index is usually a unique
	numerical value. Sometimes a primary index will consist of three fields, such as
	the 
<TT>CustNo</TT>, <TT>FirstName</TT>, and <TT>LastName</TT> fields.
	<P>
	<LI>In Paradox tables all primary and foreign keys must be indexed. You can't define
	referential integrity without indexes, and in particular, you must have a primary
	key. 
Furthermore, in InterBase tables, the act of defining a primary or foreign key
	will automatically generate an index. (Once again, this is an item that doesn't clearly
	belong in either the discussion of keys or of indexes, but rather it relates to 
both.
	As I said earlier, there are times when the distinction between the two subjects
	becomes blurred.)
</UL>

<P>If you are new to databases, you will undoubtedly be frustrated to discover that
different databases have varying rules for setting up 
indexes, keys, and so on. In
this book, I tend to use Paradox tables as the default, but I also spend considerable
time describing InterBase tables. If you use some other database, such as dBASE,
Oracle, or Sybase, you should be sure to read up on the 
basic rules for using those
tools. For instance, some databases let you set up a foreign key that is not an index.
In the Paradox and InterBase world, however, foreign keys are always accompanied
by an index, so the two words become synonymous, 
particularly in the hands of people
who don't really understand how relational databases work.</P>
<P>The good news is that you will find that overall there are certain basic principles
that define how databases work. The details may vary from 
implementation to implementation,
but the fundamental ideas stay the same.
<H4><A NAME="Heading22"></A><FONT COLOR="#000077">Keys Are the Keys to the Kingdom!</FONT></H4>
<P>Let me take this whole paradigm even one step further. When I first looked at