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 Tuples
A table is a logical collection of records, with the stipulation that all records within the table are identical in structure. From a SQL standpoint, your table might be defined as:
\nCREATE TABLE critter(\n   critterid    INT           NOT NULL PRIMARY KEY(CLUSTERED),\n   name         VARCHAR(80)   NOT NULL,\n   description  VARCHAR(255)  NOT NULL)\n

In this case, the table would be the critter entity, and the id/name/description would be items within the record (columns if you like). The table represents a collection of information concerning Critters.

New Re: Tuples
In this case, the table would be the critter entity, and the id/name/description would be items within the record (columns if you like). The table represents a collection of information concerning Critters.


Okay, so let me ask this then. When I sort the table, (i.e. select a column and put it in order of name, date bought, etc), is that generating a report, or is there more to it?

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

Comment by Nightowl
New Could be
Okay, so let me ask this then. When I sort the table, (i.e. select a column and put it in order of name, date bought, etc), is that generating a report, or is there more to it?
There are different forms of reports. In access, you have DataSheet views available on the Tables & Queries. You have Forms which allow you to view the data through the windows user interface. And lastly you have reports which are designed to be printed. Normally when Access users think of reports, they think of the kind that come in ready-to-print form.

New How come critterid isn't IDENTITY?
bcnu,
Mikem

The soul and substance of what customarily ranks as patriotism is moral cowardice and always has been...We have thrown away the most valuable asset we had-- the individual's right to oppose both flag and country when he (just he, by himself) believed them to be in the wrong. We have thrown it away; and with it all that was really respectable about that grotesque and laughable word, Patriotism.

- Mark Twain, "Monarchical and Republican Patriotism"
New I'm of two minds on identity
On the one hand, identity keys are nice if you just need an auto-incrementing sequence. On the other, I find it makes imports & exports a bit more difficult. Still haven't made up my mind whether I like them or not. And given that we're moving to Oracle, I'll be stuck with using the Sequence generater that's external to the table.
New Re: I'm of two minds on identity
Hmmm is identity the primary key? In my critter database, the name of the stuffed animal is the critter ID, and that becomes the primary key. This also prevents us from ever naming two stuffed animals the same name.

Is that what you mean by identity being the key?

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

Comment by Nightowl
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]
New Identity being the key
CritterID is a data field that not only is a primary key, but also a number which automatically increments itself for each new record. Each CritterID will be unique and always counting forwards.

There may be situations where you delete a Critter, so CritterID may not always be one number after the other. So a CritterID of 13 may not be the 13th record in the database if Critters before it got deleted. It just holds a value of 13, which is unique to that Critter record. No other Criter may have that CritterID of 13, and CritterID cannot be blank or nothing, it has to be a valid number. These are characteristics of a Primary Key with an AutoNumber property. Some databases call the AutoNumber an Identity, it is basically the same thing.

You can later create a CritterShelf table having a ShelfID, and a CritterID field that goes back to the Critter table and ShelfID going back to Shelf. Matching the CritterID in CritterShelf to a CritterID in Critters will tell you which Shelf the Critter is on. This is called a Foreign Key, also may be called an External Key. When you place a Critter on a shelf, you have the database copy the CritterID from Critter and put it into CritterShelf using the correct ShelfID and the CritterID of the Critter you are moving. Critter and CritterShelf tables are linked by CritterID in both tables. CritterID is an Autonumber in Critter, but not in CritterShelf. This is because CritterShelf holds a copy of CritterID and doesn't need the AutoNumber property. It has to match a CritterID in Critter. It is Indexed, but not a Primary Key in CritterShelf, because it is a Foreign Key pointing to Critter and CritterID in that table.

If you enforce Referencial Integrity between Critter and CritterShelf, then when you delete a Critter, the matching record in CritterShelf gets deleted too for that CritterID you are deleting. Because if you get rid of a critter it no longer is on a shelf.



"Lady I only speak two languages, English and Bad English!" - Corbin Dallas "The Fifth Element"

     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)

Three cheers for literacy... I feel sick.
156 ms