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 |
#71
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
On Dec 11, 5:31 am, "Tony Toews [MVP]" wrote:
How does clustering apply to Access databases? If by "Access databases" you mean Jet then it has already been mentioned several times in this thread alone. Here I even got accused by the OP of providing a "helpful answer": http://groups.google.com/group/micro...add6aaab8ef045 Jamie. -- |
#72
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
On Dec 8, 7:43 pm, "David W. Fenton"
wrote: Jamie Collins wrote : I broadly agree with everything you've said here. I do still think that many (perhaps most) users of Autonumber columns use it for their one and only unique constraint per table; for the record, I think such people are ill informed rather than stupid. Some should now be better informed after reading your post One point: in a lot of situations, forcing someone to use the natural key can lead to all sorts of other problems, like default values to avoid Nulls, and then you have to program around those (e.g., you won't be able to use this to concatenate the name fields: Mid(("12" + [LastName]) & (", " + [FirstName]),3), because with default values, none of your "empty" fields will be Null). I don't design my schemas to fit your ready-rolled formulas g!. I have no doubt that if I define my middle_name column as NOT NULL with a DEFAULT of '{{NONE}}' (plus CHECK constraints to disallow the empty string, spaces, etc) then you would be able to come up with a similar formula to derive a full name. My create/update stored procs handle the NULL value and the empty string so it shouldn't be too onerous on the 'front end guy' to replace '{{NONE}}', '{{NK}}', etc, with whatever is appropriate in context. That said, person's name rarely makes a good key, so is not IMO a good example of a natural key. My favourite is the ISBN: industry standard (ask at your local public library or look at the URLs on Amazon), trusted source (will resolve rare cases of duplication), familiar and verifiable in reality (take a look at the bar code on the back cover of your favourite book), stable (sure, they have recently changed from 10 to 13 characters but this has been managed by the trusted source to provide an upgrade path), fixed length with a check digit (easy to validate at time of keying in), a so-called 'intelligent' key (it comprises detail of region, publisher, etc) and so on. Jamie. -- |
#73
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
I think the answer you've gotten is that none of us can think of a good
reason to include an autonumber in a table if it is NOT the primary key. "Jamie Collins" wrote in message ... On Dec 10, 3:32 pm, "Pat Hartman" please no wrote: If you want to conduct an intellectual discussion you really need to be able to handle it when people disagree with you and stop taking cheap shots. If I really was conducting this discussion I would have answers to my direct questions (ping Dale Fye: "given that someone has used Autonumber as a so-called surrogate, what reasons would they have for not making the Autonumber the PK?") And if I couldn't handle people around here disagreeing with me I'd have left years ago! FYI: The Access Web: Netiquette http://www.mvps.org/access/netiquette.htm Be thoughtful of bandwidth and other folks' thought processes: [quote] Look for Smileys :-), grins g, and other "Emoticons". When you see one, the preceding statement was not meant to be really serious, don't take it as such. [Unquote] I don't think the poster was being serious either (hence my short dismissal with a grin). Designing database for one's own convenience rather than your client's doesn't sound like a credible proposition to me. Jamie. -- |
#74
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
On Dec 11, 4:01 pm, "Pat Hartman" please no wrote:
I think the answer you've gotten is that none of us can think of a good reason to include an autonumber in a table if it is NOT the primary key. I'm assuming that by 'primary key' you mean the SQL keyword PRIMARY KEY rather than the logical meaning of 'primary key', on the basis that an Autonumber only comes into existence in the physical implementation and has no place in the logical model. I'm also assuming that by 'us' you aren't alienating me g. 1) Clustering: as an extreme example, imagine a table with a ten- column compound key and you wanted to cluster on just one of those columns being a DATETIME consisting of non-unique values. In SQL Server, if you created a clustered index solely on a column without a unique constraint then the system would create an auto-increment column under the hood to 'uniqueify' the values. In Jet, the only way of influencing clustering is to use the PRIMARY KEY designation and Jet will not allow you to create a PK solely on a column containing non-unique values. You can do the same trick as SQL Server but you have to explicitly create the 'uniqueifier' yourself and an Autonumber column is a very convenient way of doing this. You'd then create a compound PK on (datetime_col, unique_col) _in that order_ of course (BTW how does one do this using the Access interface rather than code?) In this scenario, the autonumber in the table is not the primary key, though it is _part of_ the PRIMARY KEY. 2) PRIMARY KEY is arbitrary: you can put it on *any* columns (bar those of type OLEOBJECT) in the table (unless existing values are NULL and/or non-unique) or you can opt to omit a PRIMARY KEY completely: if you have a unique constraint (e.g. unique index and NOT NULL designation) on a candidate key I don't think anyone could fault you (though they could point out that you were potentially missing out on an opportunity for optimization). Therefore, a table with an Autonumber column but without a PRIMARY KEY is perfectly acceptable. I'm sure there are other reasons which smarter people than me could come up with; perhaps a little thinking outside the box is required? Jamie. -- |
Thread Tools | |
Display Modes | |
|
|