IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New In Access, Identity is an AutoNumber
The reference to the identity is to the SQL statement I gave abovem wherein I set aside a critterid column as an integer (seperate from the name itself). Laying out the SQL, it would be something like:
\nCREATE TABLE critter(\n   critterid    INT           NOT NULL IDENTITY(1,1) PRIMARY KEY,\n   name         VARCHAR(80)   NOT NULL,\n   description  VARCHAR(255)  NOT NULL)\n

The identity would make critterid an auto-incrementing number that starts at the value of 1 and gets incremented by one everytime an row insert is done.

In terms of what you have, you've used the name as the primary key, so you don't have to worry about using an AutoNumber as the key. For simple databases, using a character field will probably be ok, but there are a number of things you have to worry about as you scale an application. First off, there is the question of efficiency. For most processors, a number is an atomic value for the cpu. Running through a table trying to find an integer value is far faster than doing lengthy string searches and comparisons.

The second problem is one of maintenance. Let's say that you at some point need to change the name. Every table that keys off the critter table uses the name as the lookup key. If you change the name in the critter table, you run into the problem that you have to change the lookup key in every table that references the critter table. Worse still, if you are enforcing referential integrity on the foreign keys, you have a chicken-egg situation - you can't change the name in the critter table because it is referenced in other tables - and you can't change the names in the other tables if that name is not already in the critter table.

Finally, there is the problem of sometimes needing duplicate names - though this is probably not a concern with you design. But let's say we were talking about the names of people. What you'll find is that there are a bunch of John Smiths. If you were to use the name as a primary key, you could not have more than one John Smith in your database. Typical response is to change to something like Social Security Numbers, which can also be a mistake since SSN's get reused.

To get around these type of issues, many database tables just use a autonumber as the primary key in the table. The downside, of course, is that the key to the data becomes just some number that has no meaning outside of the database context (though some assigned numbers do take on a life of their own).
New Re: In Access, Identity is an AutoNumber
The identity would make critterid an auto-incrementing number that starts at the value of 1 and gets incremented by one everytime an row insert is done.


Ah, ok. We have that number that increments by one every time we add a new critter, but it isn't the key. It simply keeps count for us of how many critters we own.

In terms of what you have, you've used the name as the primary key, so you don't have to worry about using an AutoNumber as the key. For simple databases, using a character field will probably be ok, but there are a number of things you have to worry about as you scale an application. First off, there is the question of efficiency. For most processors, a number is an atomic value for the cpu. Running through a table trying to find an integer value is far faster than doing lengthy string searches and comparisons.


Hmmm, all I do is sort by critter type, or critter name to match whether a name exists... is that a string search or comparison? (Need to look those up, I think).

The second problem is one of maintenance. Let's say that you at some point need to change the name. Every table that keys off the critter table uses the name as the lookup key. If you change the name in the critter table, you run into the problem that you have to change the lookup key in every table that references the critter table. Worse still, if you are enforcing referential integrity on the foreign keys, you have a chicken-egg situation - you can't change the name in the critter table because it is referenced in other tables - and you can't change the names in the other tables if that name is not already in the critter table.


Ah ok, well since there are no tables to this table, (it's all one table), I see why I don't run into the problem. I have changed names before, but it's simply a change name in the field and then it takes it and all is well.

But I see how that would get complex in the cases of databases that required duplicate names.

To get around these type of issues, many database tables just use a autonumber as the primary key in the table. The downside, of course, is that the key to the data becomes just some number that has no meaning outside of the database context (though some assigned numbers do take on a life of their own).


That's why John said my table was so smart, wow. Because every data bit in the row is dependent upon, or related to the name. Neat.

Thanks for explaining that.

Nightowl >8#
"I learned to be the door, instead of the mat!" "illegitimi nil carborundum"

Comment by Nightowl
New AutoNumbers not always continuous
Ah, ok. We have that number that increments by one every time we add a new critter, but it isn't the key. It simply keeps count for us of how many critters we own.
Identity columns using the AutoNumber technique have a tendency to introduce periodic gaps in the numbering. What happens is that someone will start to type in values in a new row (which will cause the sequence to increment), and then hit the Escape key to abort the insert (in database terms, a rollback is performed on the transaction). In both Access and SQLServer, the autonumbers are not reset to the previous value (meaning they are autonomous and not part of the transaction process). That number in the sequencer is basically skipped as far as the table is concerned. The only way to get it back would be to do a database repair prior to the next row insert.

Bottom line is that the maximum autonumber will not necessarily be a true indication of the count of the number of rows in the critter table.

Hmmm, all I do is sort by critter type, or critter name to match whether a name exists... is that a string search or comparison? (Need to look those up, I think).
In a larger application, you'd typically not want to use a string column as the primary key. An integer key will make the table joins much faster.

But to make the string searches faster, you might want to build an index on a string field. In Access, there is an Indexed attribute for each column you define in the Design View. Primary Keys are one form of an index. Any column (or group of columns) can participate in Indexes, in various combinations. Indexes are very important when the amount of data becomes large - I've seen queries go from not being finished after waiting an hour, to taking just a few seconds with the proper index. At any rate, indexes are a subject on to themselves.

The other problem you have, of course, is that you want to place a constraint on the name column to not allow duplicates. Primary keys have the quality that they are by definition a UNIQUE INDEX. Databases allow a unique constraint to be placed on any index of your choosing - in the Design View for Indexed you'll see the choice of "Yes (No Duplicates)".

That's why John said my table was so smart, wow. Because every data bit in the row is dependent upon, or related to the name. Neat.
Third Normal Form is sometimes expressed as:

The key; The whole key; And nothing but the key. So help me Codd. :-)
New ICLRPD (new thread)
Created as new thread #119763 titled [link|/forums/render/content/show?contentid=119763|ICLRPD]
"good ideas and bad code build communities, the other three combinations do not"
- [link|http://archives.real-time.com/pipermail/cocoon-devel/2000-October/003023.html|Stefano Mazzocchi]
     Is there some sort of tutorial disk for Access? - (Nightowl) - (31)
         Okay, first question... :) - (Nightowl) - (17)
             Table is multiple columns together as a unit - (FuManChu) - (5)
                 Re: Table is multiple columns together as a unit - (Nightowl) - (4)
                     Third normal form - (ChrisR) - (3)
                         Re: Third normal form - (Nightowl) - (2)
                             1st Normal Form - (ChrisR) - (1)
                                 Re: 1st Normal Form - (Nightowl)
             Tuples - (ChrisR) - (10)
                 Re: Tuples - (Nightowl) - (1)
                     Could be - (ChrisR)
                 How come critterid isn't IDENTITY? -NT - (mmoffitt) - (7)
                     I'm of two minds on identity - (ChrisR) - (6)
                         Re: I'm of two minds on identity - (Nightowl) - (5)
                             In Access, Identity is an AutoNumber - (ChrisR) - (3)
                                 Re: In Access, Identity is an AutoNumber - (Nightowl) - (2)
                                     AutoNumbers not always continuous - (ChrisR) - (1)
                                         ICLRPD (new thread) - (ben_tilly)
                             Identity being the key - (orion)
         Don't recall if there's a tutorial - (ChrisR) - (4)
             Re: Don't recall if there's a tutorial - (Nightowl) - (3)
                 Part of Office Pro - (ChrisR) - (2)
                     Re: Part of Office Pro - (Nightowl) - (1)
                         Word to Access - (orion)
         Access tutorials - (orion) - (4)
             Re: Access tutorials - (Nightowl) - (1)
                 Re: Access tutorials - (orion)
             It worked, Norman!! ( was Re: Access tutorials) - (Nightowl) - (1)
                 Glad to hear it - (orion)
         the first thing to do - (cforde) - (2)
             Better yet - (orion) - (1)
                 Not that I recommend it... - (ChrisR)

Ha, he spelled "pusillanimous" wrong!
92 ms