Jim Dettman
jimdettman at verizon.net
Thu Jul 26 06:33:07 CDT 2007
John, <<>Have you ever created a one table relational database? (OK, smart alecs - that remained so.) Of course not, it would not be a relational db if there was not a child table.>> That is not correct. A "relation" does not refer to the relationship between two tables, it refers to the tables themselves. When designing a relational system, before you can start the normalization process with 1NF, you have to form the relations using the following rules: The relation 1. Describes one entity. 2. Has no duplicate rows; hence there is always a primary key. 3. Columns are unordered. 4. Rows are unordered. Most definitely yes a single table is relational. Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Wednesday, July 25, 2007 8:32 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Primary Key Best Practices >Have you ever created a one table relational database? (OK, smart alecs - that remained so.) Of course not, it would not be a relational db if there was not a child table. >Have you ever created a table that didn't have a PK? (when you were done creating it). Yes, but not often. I almost always add the ANPK whether or not I need one. But any table with no children does not require a PK in order to exist and contain data. In order to select it for editing / deletion is another story but it still does not have to contain a field, marked by the database as being a PK, nor does it have to have a unique index on it. ANY field(s) with unique data (candidate key) can be used in a where clause to pull a specific record or set of records for editing or deletion, and it does NOT have to be marked as the PK to do so. We all know what candidate keys are. They are a field or set of fields which taken together CURRENTLY contains data that is unique within that set of fields in that table. We all know that we can take any candidate key and use it as a FK in a child table. It is not marked in the parent as the PK, but the fact that (the data in) it is unique allows us to use that as the FK in the child table. So in fact by this example we can have a table FULL OF candidate keys DOZENS OF THEM if we can discover that many candidate keys. We can intentionally not designate any one of them as the PK, yet in fact we can use any valid candidate key as the FK (pointer back to the parent) in a child table. You can demonstrate that for yourself simply by doing a join on two such tables, or even by building a where clause on such candidate key. The FUNCTIONALITY of the PK is obtained even though no PK is specified in the parent table. That is why it is important to not get hung up on the model! A PK is an artificial construct from the get go. It is a useful construct but is not required to do many of the things that some people here on the list believe it is required for. Given that it is an artificial construct, and given that a surrogate ANPK can be substituted at the drop of a hat for a natural PK, and given that the ANPK solves many problems that are created by a natural PK, I choose in almost every case to use one. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Bartow Sent: Wednesday, July 25, 2007 6:27 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Primary Key Best Practices OK now I think YOU'RE talking theoretically :o) What purpose would a one table database serve? And if there is a purpose would it even be considered relational? Sure I created spreadsheets that could be called databases but not by me, because I always mentally preface database with relational. I personally refer to a spreadsheet database as a list to avoid confusion. But all that aside I think we mostly agree on things here - it mostly nitpicky terminology/theory that some of us don't agree on. Yes or No: Have you ever created a one table relational database? (OK, smart alecs - that remained so.) Have you ever created a table that didn't have a PK? (when you were done creating it) -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Wednesday, July 25, 2007 4:47 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Primary Key Best Practices Any database made up of a single table would not require a PK. -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com