If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Rick Brandt wrote: Let me qualify that statement by saying that I would do this if I were designing a database for the entity that "owns" the creation of ISBN numbers. If I were designing a database for a library (an entity that merely consumes ISBNs) then I would put it into a single field. I'm getting a little confused. Let's assume the 'library' example. Could you relate you last comment (quoted above) back to you earlier comments about 'how the data is stored [and] how it is consumed' because the library would do both. Thanks again, Jamie. -- |
#12
|
|||
|
|||
Jamie Collins wrote:
Rick Brandt wrote: Let me qualify that statement by saying that I would do this if I were designing a database for the entity that "owns" the creation of ISBN numbers. If I were designing a database for a library (an entity that merely consumes ISBNs) then I would put it into a single field. I'm getting a little confused. Let's assume the 'library' example. Could you relate you last comment (quoted above) back to you earlier comments about 'how the data is stored [and] how it is consumed' because the library would do both. Thanks again, Jamie. Does the library issue ISBNs? I think not. It is a number that they use that has been determined by an outside authority. As a consumer of that number it might well be fine to store as a single atribute in a single field because they are primarily using it to uniquely identify books. However; the entity that is issuing ISBN numbers might more likely have a field for Country code, a field for Publisher, a field for Title, and a field for the Check Digit. On output interfaces this would be combined and dislayed as the single attribute [ISBN Number], but that is not how it would be stored in the database (if they did it correctly). If a library frequently has a need to do searches by the individual pieces of information contained within an ISBN number then they would be better off splitting it into separate fields as well. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
#13
|
|||
|
|||
Jamie
What are your thoughts on the subject? You have a lot of comments and questions, but I don't have a sense of where you stand. You state that you "know that the ISBN is an excellent example of a key" -- on what do you base this assertion? The "one fact, one field" statement is a paraphrase of the notion of atomicity, which seems to be at the heart of 1NF. Perhaps your Google preferences need resetting -- I just found a slew of references to ways of stating ("one fact, one field", and "one field, one fact"). Regards Jeff Boyce Access MVP "Jamie Collins" wrote in message oups.com... Jeff Boyce wrote: Periodically the subject of "intelligent keys" shows up here in the tablesdbdesign newsgroup. The consensus has seemed to me to argue against combining multiple facts into a single field Is this the same consensus that recommends autonumber primary keys, by any chance g? The underlying notion is "one fact, one field". I've not seen this soundbite before. My google search: http://groups.google.com/groups?q=%2....*&qt_s=Search turned up nothing. Could you clarify your point, please? That an ISBN "contains many facts" doesn't, in itself, qualify it as a good example of a key, does it? In fact, I don't know if an ISBN is a single field (then, by 1NF standards, it would not be a good design), or is itself a concatenation of multiple, single-fact fields... I'm not sure what you are getting at here. I know that the ISBN is an excellent example of a key and that it contains multiple facts. Are you saying the ISBN is a violation of 1NF? This is the first time I've heard this suggested but this could be interesting. Could you expand your thoughts, please? Jamie. -- |
#14
|
|||
|
|||
Jamie
I'm replying in a separate thread to your "aside" comment autonumber primary keys. I believe this to be an aside because the topic was use of intelligent keys. I have seen considerable debate about using autonumber primary keys. One school appears to subscribe to the notion that a natural key can (and perhaps even 'must') be found for every row/record. Another school subscribes to the notion that an autonumber primary key simply provides a unique row/record identifier. What works for you? I usually refrain from joining one camp or the other, and use what seems practical, given the entity. Regards Jeff Boyce Access MVP "Jamie Collins" wrote in message oups.com... Jeff Boyce wrote: Periodically the subject of "intelligent keys" shows up here in the tablesdbdesign newsgroup. The consensus has seemed to me to argue against combining multiple facts into a single field Is this the same consensus that recommends autonumber primary keys, by any chance g? The underlying notion is "one fact, one field". I've not seen this soundbite before. My google search: http://groups.google.com/groups?q=%2....*&qt_s=Search turned up nothing. Could you clarify your point, please? That an ISBN "contains many facts" doesn't, in itself, qualify it as a good example of a key, does it? In fact, I don't know if an ISBN is a single field (then, by 1NF standards, it would not be a good design), or is itself a concatenation of multiple, single-fact fields... I'm not sure what you are getting at here. I know that the ISBN is an excellent example of a key and that it contains multiple facts. Are you saying the ISBN is a violation of 1NF? This is the first time I've heard this suggested but this could be interesting. Could you expand your thoughts, please? Jamie. -- |
#15
|
|||
|
|||
Jeff Boyce wrote: I have seen considerable debate about using autonumber primary keys. One school appears to subscribe to the notion that a natural key can (and perhaps even 'must') be found for every row/record. Good timing: I put an autonumber (incrementing INTEGER) into a database just one hour ago. I needed to know the relative order in which rows were being added to the table and autonumber is convenient. The table already had a natural key being a composite of the entity's attributes, in the Dr Dodd tradition; I'd have been in a whole lot of trouble if I didn't. I've looked after databases where a natural key exists and an autonumber is used as an 'alternate' key. I haven't sought to change this because there is some justification for this approach e.g. a single INTEGER column foreign key is more convenient to code than a multi- text column key. There may be some storage and performance benefits but I haven't investigated this myself, a correct data model being my primary consideration. Another school subscribes to the notion that an autonumber primary key simply provides a unique row/record identifier. Using an autonumber as a key where no natural key exists is asking for trouble. It is uniqueness merely for the sake of it (I'd rather have a 'heap' i.e. without a key and technically not a table at all). You must expose the autonumber for it to be useful and almost everyone agrees you should never expose an autonumber in this way. We need to be clear about the meaning of PRIMARY KEY in Access/Jet. Here is a pertinent newsgroup post from Joe Celko: "Dr. Codd started with the concept of a PRIMARY KEY in his first writing; it was the old sort key from sequential file processing and magnetic tape storage in disguise. It was awhile before Dr. Codd changed his mind and said that all keys are equally keys, and we don't need a special one in a relational database, like you did in a tape system. Unfortunately, the first SQL systems were build on existing file systems and the idea of the PRIMARY KEY had become part of the language... The UNIQUE constraint lets you have multiple keys on a table." So in relational terms, there is no difference between a key defined as UNQIUE and a key defined as PRIMARY KEY. In the Access/Jet implementation, PRIMARY KEY has special meaning but it has nothing to do with relational keys. Allow me to quote myself g: "PRIMARY KEY has special meaning for Jet [Access]. In relational terms, a key is a key and PRIMARY KEY has no special meaning. However, PRIMARY KEY has traditionally taken on a meaning particular to each SQL product and Jet is no exception. For Jet, the special meaning is that PRIMARY KEY provides the only means of specifying the clustered index for a table. The clustered index is non-maintained, meaning the index is only 'physically' rebuilt when the file is compacted; it is, however, still maintained 'logically' between compacts. Some people have a problem with the term 'clustered index' as regards Jet (don't make the mistake on thinking 'not supported' means 'does not exist') so let me clarify: PRIMARY KEY determines the physical ordering on disk. For best performance, a table should be physically ordered on the columns of its 'primary' use i.e. those used in GROUP BY and BETWEEN clauses. Think of a paper copy telephone directory, which can obviously have only one physical order: if I'm using it primarily for search such as 'get listings for people whose last name begins with the letter C' then I'd want it ordered on last name because it would take me no time at all to rip out the required pages. Similarly, it pays to choose your PRIMARY KEY carefully and in accordance with its implications for physical ordering on disk. If you are using PRIMARY KEY to mean a UNIQUE constraint/index only then your database may be taking a performance every time a table with a poorly chosen PRIMARY KEY is queried (pay no attention to the message that tells you, 'A table must have a primary key for you to define a relationship between this table and other tables in the database,' it lies, it lies). And this is why an autonumber is usually a poor choice for PRIMARY KEY: how useful would you find a paper copy telephone directory physically ordered on telephone number?" Given that PRIMARY KEY affects physical ordering and nothing else, I can see no justification for an autonumber *solely* being PRIMARY KEY. There may, however, be some justification for including an autonumber in a *composite* PRIMARY KEY assuming it was the last column specified in the PK's definition e.g. PRIMARY KEY (key_col1, key_col2, ..., autonumber_col). Jamie. -- |
#16
|
|||
|
|||
Jeff Boyce wrote: You have a lot of comments and questions, but I don't have a sense of where you stand. I'm still learning. I'm particularly poor when it comes to normal forms. I took an online test on the subject recently (e.g. differences between BCNF - any relation g? - and 5NF etc) and scored appallingly. And it would seem I'm not the only one here. I did a search of postings on the subject in the Access groups and here are the ten most recent usages in the last 24 hours: is a breech of normalization. violates data normalization rules if you had your database normalized a less normalized table isn't properly normalized Its not 100% normalized properly normalized the normalization rules of relational databases say one field violates data normalization rules It is against relational database normalization rules Nine of those I'd have extreme trouble defending to Fabian Pascal g. In contrast, explicit references to 3NF (or Third Normal Form) and 1NF (or First Normal Form) are seen fairly infrequently in these groups. Me, I'm a casual bystander on matters of normal forms. You state that you "know that the ISBN is an excellent example of a key" -- on what do you base this assertion? In a nutshell, because Joe Celko tells me so g. The ISBN is maintained by trusted source: useful when the encoding system changes because the source authority manages the change on behalf of everyone. It is unique (situations where duplicates arise are rectified by the trusted source). It is an *international* encoding system. It can be verified in the real world: get the book off the shelf and look at the barcode, go to Amazon.com, etc). It is verifiable within itself: can be tested for character length and syntax, has a check digit. I'm also told that a key must come into existence by the time the entity has come into existence and that a book may be allocated an ISBN before the publisher has even seen the first draft. The "one fact, one field" statement is a paraphrase of the notion of atomicity, which seems to be at the heart of 1NF. I would have though, 'one row, one fact' would be more like it. My normal forms knowledge may not be up to much but I *can* google vbg. Did you notice the group and author of these posts? If I search using "one field one fact" OR "one fact one field" -group:microsoft.public.access.* I get no results i.e. no one outside the Access ngs are using these phrases. If I search using just "one field one fact" OR "one fact one field" I get 21 results. Then I can reduce the 19 by excluding a certain g author: "one field one fact" OR "one fact one field" -"Jeff Boyce" I get just 2 results. Jamie. -- |
#17
|
|||
|
|||
Jamie Collins wrote:
You state that you "know that the ISBN is an excellent example of a key" -- on what do you base this assertion? In a nutshell, because Joe Celko tells me so g. But Joe would also remind you that "a key" does not mean "a single field". A key can be (and often is) made up of multiple fields. In fact a natural key purist like Mr. Celko would likely have multiple column keys the vast majority of the time because few entities have a single attribute that is unique. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
#18
|
|||
|
|||
Rick Brandt wrote: But Joe would also remind you that "a key" does not mean "a single field". A key can be (and often is) made up of multiple fields. In fact a natural key purist like Mr. Celko would likely have multiple column keys the vast majority of the time because few entities have a single attribute that is unique. Indeed. Joe could also tell you a thing or two about 'attribute splitting' i.e. when you take what should have been one table, find an attribute and use the values of the attribute to make extra tables that should not exist. He'd also tell you fields are not columns and at great length too. Jamie. -- |
#19
|
|||
|
|||
Hi there,
I received some good advice relating to having an alphanumeric reference number, which I have been able to create and it works well in a query. Thanks to those who took the time to give me the advice. However, the data that I am able to obtain from the query is not displayed in the underlying table or form. The Table has 3 fields relative to the query; "ClientID Number", a 3 digit autonumber, "ClientID Name", a 3 digit manually created alpha figure, and "ClientRef", which displays a combination of both these data to display the alphanumeric figure that I need. The query draws on the information from the "Corporate Client Details" Table, where all related fields are found. In the underlying table I tried to use the lookup wizard to recall the data from the query for display in the table/form, but when doing so got the message "no valid fields can be found in 'Corporate Client Details Query'. You may have selected a query that uses the table your adding the lookup column to. Please select a new source". I guess the error message is self-explanatory, but I'm wondering how I can get around this problem. Off the top of my head it seemed appropriate to then create another table which holds the alpha and the numeric fields, along with the client name, then only keep the ClientRef, which is a combination of the two fields in the "Corporate Client Details" Table. Upon doing so, I was still unable to recall the data from the query using the lookup wizard. Is there a way around this? The other problem that I have is that I want the alpha figures in the ClientRef to appear in uppercase. I have enabled this to happen in the table, but it does not in the query when I combine the two fields together. Does this matter that it doesn't appear in the query, or is it that once I manage to combine the "ClientID Number" and "ClientID Name" in a single field I can then set it to view in uppercase? Thanks in advance Katharine "Jamie Collins" wrote: Jeff Boyce wrote: I have seen considerable debate about using autonumber primary keys. One school appears to subscribe to the notion that a natural key can (and perhaps even 'must') be found for every row/record. Good timing: I put an autonumber (incrementing INTEGER) into a database just one hour ago. I needed to know the relative order in which rows were being added to the table and autonumber is convenient. The table already had a natural key being a composite of the entity's attributes, in the Dr Dodd tradition; I'd have been in a whole lot of trouble if I didn't. I've looked after databases where a natural key exists and an autonumber is used as an 'alternate' key. I haven't sought to change this because there is some justification for this approach e.g. a single INTEGER column foreign key is more convenient to code than a multi- text column key. There may be some storage and performance benefits but I haven't investigated this myself, a correct data model being my primary consideration. Another school subscribes to the notion that an autonumber primary key simply provides a unique row/record identifier. Using an autonumber as a key where no natural key exists is asking for trouble. It is uniqueness merely for the sake of it (I'd rather have a 'heap' i.e. without a key and technically not a table at all). You must expose the autonumber for it to be useful and almost everyone agrees you should never expose an autonumber in this way. We need to be clear about the meaning of PRIMARY KEY in Access/Jet. Here is a pertinent newsgroup post from Joe Celko: "Dr. Codd started with the concept of a PRIMARY KEY in his first writing; it was the old sort key from sequential file processing and magnetic tape storage in disguise. It was awhile before Dr. Codd changed his mind and said that all keys are equally keys, and we don't need a special one in a relational database, like you did in a tape system. Unfortunately, the first SQL systems were build on existing file systems and the idea of the PRIMARY KEY had become part of the language... The UNIQUE constraint lets you have multiple keys on a table." So in relational terms, there is no difference between a key defined as UNQIUE and a key defined as PRIMARY KEY. In the Access/Jet implementation, PRIMARY KEY has special meaning but it has nothing to do with relational keys. Allow me to quote myself g: "PRIMARY KEY has special meaning for Jet [Access]. In relational terms, a key is a key and PRIMARY KEY has no special meaning. However, PRIMARY KEY has traditionally taken on a meaning particular to each SQL product and Jet is no exception. For Jet, the special meaning is that PRIMARY KEY provides the only means of specifying the clustered index for a table. The clustered index is non-maintained, meaning the index is only 'physically' rebuilt when the file is compacted; it is, however, still maintained 'logically' between compacts. Some people have a problem with the term 'clustered index' as regards Jet (don't make the mistake on thinking 'not supported' means 'does not exist') so let me clarify: PRIMARY KEY determines the physical ordering on disk. For best performance, a table should be physically ordered on the columns of its 'primary' use i.e. those used in GROUP BY and BETWEEN clauses. Think of a paper copy telephone directory, which can obviously have only one physical order: if I'm using it primarily for search such as 'get listings for people whose last name begins with the letter C' then I'd want it ordered on last name because it would take me no time at all to rip out the required pages. Similarly, it pays to choose your PRIMARY KEY carefully and in accordance with its implications for physical ordering on disk. If you are using PRIMARY KEY to mean a UNIQUE constraint/index only then your database may be taking a performance every time a table with a poorly chosen PRIMARY KEY is queried (pay no attention to the message that tells you, 'A table must have a primary key for you to define a relationship between this table and other tables in the database,' it lies, it lies). And this is why an autonumber is usually a poor choice for PRIMARY KEY: how useful would you find a paper copy telephone directory physically ordered on telephone number?" Given that PRIMARY KEY affects physical ordering and nothing else, I can see no justification for an autonumber *solely* being PRIMARY KEY. There may, however, be some justification for including an autonumber in a *composite* PRIMARY KEY assuming it was the last column specified in the PK's definition e.g. PRIMARY KEY (key_col1, key_col2, ..., autonumber_col). Jamie. -- |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
REPOST - I need to make the IDNumber an Autonumber - HOW?? | melwester | General Discussion | 1 | August 4th, 2005 09:17 PM |
Import data into a table with an autonumber field | Martin Watts | Database Design | 2 | March 8th, 2005 08:08 PM |
Access 2000, autonumber fields | Zyberg74 | General Discussion | 3 | November 17th, 2004 05:24 PM |
Problem with AutoNumber | accessmonk | Database Design | 2 | September 30th, 2004 08:57 PM |
Autonumber | Ally H. | General Discussion | 7 | August 27th, 2004 04:51 PM |