A couple of minor corrections here, but worth noting because I think it helps people understand the subject better.
When designing an entity (roughly the logical equivalent of a physical database table) and specifying its attributes (roughly the logical equivalent of a physical database table column), you try to discover any naturally occuring keys among those attributes. Keys can always be either simple (single-column) or compound (multi-column). If a naturally occuring key cannot be discovered then you will either have to artificially introduce a surrogate key (an attribute that doesn't describe the entity, but instead exists solely to identify instances of that entity) or wait until a second discovery pass after *identifying* relationships are defined in related parent entities (which will contribute a foreign key to the child entity that also becomes part of the child entity's primary key). Most relationships are non-identifying. An example of an identifying relationship is that between a sales order and its order items, where each primary key of each order item is the combination of its part number and the sales order number that was contributed by the relationship. Because the foreign key that was contributed by the relationship with the parent entity is also used to uniquely "identify" each instance of an order item entity, the relationship is known as an "identifying relationship." All possible keys are considered "candidate keys" at this point -- candidates for becoming a primary key. It's like a beauty pageant, and they all have to answer questions like, "If you were selected as Primary Key, how would you solve your entity's identification problems?" Well, not really, but kinda. Typically, the candidate key that serves the database best at mechanically solving identification problems (not mandatory but good to be small in size and unchanging in data) will be chosen as the primary key for an entity. Once a primary key is chosen for an entity, all remaining candidate keys are considered "alternate keys" because they are also keys that can uniquely identify each instance of an entity but they are not the primary key. So "candidate keys" cease to be called that after the primary keys have been chosen. Alternate keys are critical to good application design -- even at the interface level, though many claim that the database should be completely divorced from the layers above it (they must have read that somewhere and it sounded good to them at a theoretical level). For example, if you have a select menu that enables you to choose a related parent company on an employee form, the value being displayed to the user must be defined in the database as a key as well as the actual "value" attribute that will be submitted by the form when the user clicks the Submit button, because the user must be able to tell exactly which company is being chosen. This typically necessitates requiring a uniquifier to be added to the company name (like a branch name or sometime like that). If not, the user can't tell which "IBM" he's choosing. You can't choose keys trivially -- you must be rigorous about your claims that attributes or groups of attributes are definable as keys. For example, the example that Dan gave about ContactName and ContactPhone would not be defined as a key because such information is not used to identify an specific instance of an entity. It may happen that the combination of these two attributes in reality may be unique throughout the database, but that in itself doesn't qualify it as a key. Defining each key in a data model has significant impact -- both positive and negative -- in the physical database that will eventually be built from it. I hope this helps. Respectfully, Adam Phillip Churvis Member of Team Macromedia http://www.ProductivityEnhancement.com Download Plum and other cool development tools, and get advanced intensive Master-level training: * C# & ASP.NET for ColdFusion Developers * ColdFusion MX Master Class * Advanced Development with CFMX and SQL Server 2000 ----- Original Message ----- From: "Dan" <[EMAIL PROTECTED]> To: <[email protected]> Sent: Sunday, May 01, 2005 5:22 PM Subject: RE: [plum] NumberOfBrainCellsDroppingQuicklyPleaseHelp A Primary Key IS designated in your _db Table. An Alternate Key is also referred to as a Candidate key; this is a key that uniquely identifies rows in a table. It could potentially serve as the primary key. There can be more than one candidate key and a candidate key may be composed of more than one column. For example, if the Contact_table contained columns: ContactID, ContactName, EmailAddress, etc. You would designate in the table ContactID as the PrimaryKey. Now, a key, any key, must uniquely identify a row, so ContactName would be a bad choice as an Alternate Key because you could have two, or more, contacts name "Mark", but pretty much you would never have two identical email addresses, so choosing EmailAddress as an Alternate Key would work. Alternate Keys can also be two or more columns. You could designate ContactName + ContactPhone as an Alternate Key. You might have two records for Mark, but not for Mark + 800-770-1234. By The Way (BTW) your best friend is Google. As the incredible Dave Watts (at Fig Leaf Software) once said: "It's not how smart you are, it's how fast can you Google." Hope this helps, Dan Kaufman -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mark Fuqua Sent: Sunday, May 01, 2005 4:59 PM To: [email protected] Subject: [plum] NumberOfBrainCellsDroppingQuicklyPleaseHelp Hey guys, I'm losing my mind. Considering how small it is, this is not good. Please help. What is an alternate key. I have done searches everywhere. Can not find a good concise answer. I am trying to do a simple filtered select. The documentation says that the display column must be a key, most likely an alternate key. "displayColumn is the name of the column to display in the filtered select menu. Must be a key column; most likely this will be an alternate key column. Defaults to the value of primaryKey." What do they mean by alternate key and how can I set one in Access2000? Thanks a lot, Mark Fuqua ********************************************************************** You can subscribe to and unsubscribe from lists, and you can change your subscriptions between normal and digest modes here: http://www.productivityenhancement.com/support/DiscussionListsForm.cfm **********************************************************************
