Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Q & A - Access Wiki _ Composite Indexes Vs Composite Primary Keys, Wiki Talk

Posted by: ButtonMoon Oct 11 2013, 05:38 AM

I can't reply in http://www.UtterAccess.com/forum/Composite-Indexes-Compos-t2010223.html&p=2406066#entry2406066, it appears to be locked, so I'm posting here.
e: http://www.UtterAccess.com/wiki/index.php?title=Composite_Indexes_vs_Composite_Primary_Keys
The weasel words of the first sentence aren't "debatable" content, they are just wrong content. Composite keys are never a violation of 1NF and except in this one case I haven't heard anyone claim that they are.

Posted by: genoma111 Oct 11 2013, 05:42 AM

OT: I think posts in that thread are moderated meaning you'll have to wait until a moderator approves your comment before it becomes visible to the rest of us.

Posted by: ButtonMoon Oct 11 2013, 05:52 AM

I can't even comment. When I click reply I get "Sorry, this topic has been locked".

Posted by: argeedblu Oct 11 2013, 06:48 AM

Someone will be taking a look at what's going wrong. Thanks,
Glenn

Posted by: CyberCow Oct 11 2013, 08:53 AM

We're looking into it and have verified the problem. Thank you for pointing it out. We hope to have it fixed by tomorrow. (many of us admins have regular jobs to attend and those of us who have the engine room responsibilities are now aware of the issue and have it at the top of our "fix list".)
or one of the other Engine Room attendees will post back when the issue is dealt with. hat_tip.gif

Posted by: datAdrenaline Oct 12 2013, 11:30 AM

"The weasel words of the first sentence aren't "debatable" content, they are just wrong content."
Agreed. I reworded briefly. But the whole article needs help --- I can't give it at the moment
Ideas:
- Explain what a "key" is. Also the claim of a "Composite Index" is misleading ... its an index or not, the key of the index is the composite part. With that its a Composite Key Index, or an Index with a Composite Key.
- Then go into the differences between how a Composite Key Primary Index and a Single Field Key Primary Index can effect your development processes.

Posted by: dmhzx Oct 12 2013, 12:30 PM

When it is addressed, I wonder if the following could be included please.
Is there a practical difference between an 'index' and a 'key'.
There may only be one Primary Key, but you can have a load of unique indexes. So why is a unique index called Primary Key at all important: - Apart from the convention of the terms PK and FK, and entity modelling, and schema design.
When building 'indexes' in Access, you can designate one of the to be the 'Primary Key': So does it then stop being an 'index', or is it just a special case of 'index'
When working with VBA, you can order by any 'index', including the one called "Primary Key".
So is it that you may have a maximum of one 'key' per table. That one key IS the primary key, but you can call it whatever you want?
And when explaining this, could you point out exactly how Access helps us to understand.
I'm asking because I can't see much other than semantics separating a primary key from any other unique index, and I feel I MUST be missing something..

Posted by: ace Oct 12 2013, 01:08 PM

While we're at it, a wiki article really should be an actual article as opposed to a few sentences introducing
a couple of files to download.

Posted by: genoma111 Oct 12 2013, 02:56 PM

That's not even an abstract evilgrin.gif

Posted by: CyberCow Oct 12 2013, 04:16 PM

The beauty of our Wiki is that ANY member can add/edit the content. These "Talk" threads are merely for further/deeper discussion about the actual topic before/after such content is added/edited. hat_tip.gif

Posted by: ButtonMoon Oct 12 2013, 05:53 PM

In relational database terms a table must have at least one key and it may have more than one. A key is a minimal superkey, which means a minimal set of attributes (zero, one or more attributes) which collectively are required to be non-null and unique at all times. The word key is also used for key constraints, i.e. the implementation of a logical rule in a database that a certain set of attributes must be unique. For example the NOT NULL/UNIQUE and PRIMARY KEY keywords in SQL are syntaxes that are used to define key constraints. By convention when a table has more than one key then one key of that table is deemed to be a "primary" one, meaning it is the "preferred" identifier in that table or has some other special significance. The other non-primary keys of the table are then called secondary keys or alternate keys. The distinction of a primary key is only a convention however and is only as important as you want it to be. A primary key doesn't have to be different in form or function from any other key unless you choose to make it so. The relational model itself makes no theoretical or practical distinction between primary and alternate keys. All keys are equal under the relational model.
While keys are logical features of a database, indexes are physical. An index is a performance and storage optimization feature. Key attributes may or may not be indexed. Usually they are indexed because keys are usually very good candidates for performance optimization. Some DBMSs actually require keys to be indexed and even provide a "unique index" feature as an alternative way of defining keys. Allowing an index feature to define a key is an unfortunate compromise of the principle of Physical Database Independence but it can also be seen as mere syntactical sugar: the fact that a key is defined as a "side effect" of index creation doesn't alter the fact that it is a key.
Access won't help you understand. There is no accounting for the sloppy and confusing way in which keys, constraints, indexes and other database features are presented in the Access UI. Any student wanting to learn about database principles would be well advised to stay away from Access.

Posted by: genoma111 Oct 12 2013, 06:43 PM

QUOTE (ButtonMoon)
Any student wanting to learn about database principles would be well advised to stay away from Access.

That's like saying that anyone who wants to learn music should stay away from jazz...

Posted by: CyberCow Oct 13 2013, 08:58 AM

ButtonMoon - I agree with Diego. Making such a statement on a forum that primarily supports Access is counter-productive.
I knew nothing of database principles until I encountered Access. Before Access, I worked with DB4 and Clarion - both were horrible ways to learn database concepts. (IMO)
Some sort developing environment is required to learn db principles and IMO Access is the fastest path to that end.
Access helps us to understand db principles by providing enough controls to shoot one-self in the foot or render a beautifully designed data model and everything in between. LEARNING database principles is a process - no matter what environment is selected/used to engage in that learning. While Access is not the only environment to learn db principles, I challenge anyone to find a faster tool.
Any member wishing to participate here at UtterAccess would be well advised to support the very thing for which this forum was designed, is maintained and exists. :ha

Posted by: ace Oct 13 2013, 10:18 AM

One could sit down in front of Access with a good book on relational database design and learn about relations, tuples, keys, constraints and
what have you by using nothing but the SQL view of the query builder.
laming a tool for someone not taking the time to learn the basics of relational database design is really kind of silly.

Posted by: argeedblu Oct 13 2013, 10:23 AM

Well said, Mark and Ace.
That's the old saying, "A good workman doesn't blame his tools."
Glenn

Posted by: ButtonMoon Oct 13 2013, 10:34 AM

That's true but then why bother with Access at all if you aren't using its UI.
I'm not blaming the tool for people not trying to learn, I'm simply saying that Access's irrelevant and misleading terminology and UI features tend to hinder rather than contribute to the learning process. I think there's plenty of evidence in these forums and elsewhere to confirm that impression. That isn't a criticism of Access for what it was intended for: as a development tool it isn't a problem. But there are far better ways to learn about databases.

Posted by: ace Oct 13 2013, 10:36 AM

Can you make any suggestions?

Posted by: CyberCow Oct 13 2013, 11:06 AM

I believe the venue and end-target of what a student is intending to learn is key to the starting point. Access was never intended for totally secure, mission critical db development tool (IMO); I've always seen it as the best and fastest pilot-application development to ever hit the market.
Oalso believe Microsoft is now targeting Access to provide better web-based tools for those who have little to zero programming experience/education.
And as Ace suggests, please point out other suggestions.

Posted by: GroverParkGeorge Oct 13 2013, 12:10 PM

When I was studying linguistics back in the 70's, we identified a specific type of writing primarily associated with academics. Hence, we referred to it as the academic style, although that term can include a lot of other things in addition to the one that is relevant here. What we meant by "academic writing" is that many journal articles are written NOT to illuminate or elucidate a particular topic, but rather to establish the writers' superior intellect and understanding. The way that is done is to employ highly technical language, complex theory and an obscure style to convey the notion that, "I know more than you; therefore it's your fault if you can't understand what I'm saying." The genius of some well known science writers like Carl Sagan and Richard Feynman is, IMO, that they realized "academic pontification" was not going to help anyone understand anything. I think Stephen Hawking's books show that he shares that same understanding. There are others. Unfortunately, there are also far too many academics who try to establish their superiority through journal articles whose hallmark is obscurity rather than elucidation.
The point I'm trying to make is that we offer the most help to our members by avoiding the urge to show off our superior knowledge and just help them try to understand the nature of the problem they are facing, along with some practical suggestions about solving it. Again, IMO, the typical member here at UA is not at all interested in mastering relational design, no more than Sagan's legion of readers intended to master biology, nuclear physics or astronomy. When our members pick up some sound principles along the way, so much the better.

Posted by: CyberCow Oct 13 2013, 01:27 PM

George - very well said! order="0" alt="thumbup.gif" />

Posted by: genoma111 Oct 13 2013, 03:01 PM

QUOTE (ButtonMoon)
I'm simply saying that Access's irrelevant and misleading terminology and UI features tend to hinder rather than contribute to the learning process

Following with the music analogy, you can learn music by using partitures, tablatures, letter notation or any other system designed for that purpose, or even by creating your own system from scratch by "feeling" the color and tension of the notes.
In the end what is important is how your "tune" sounds and how it's perceived by the audience.
Sure, you'll find very silly people that understand nothing about music feeling "superior" because the are very well dressed and go to the Royal Opera House to listen music that they believe is intended for an elite.
And sadly enough you'll find Music Schools around the world that teach to their students that music composed for popular instruments like guitar represent a low level art and should be treated as inferior in quality.
So I don't blame you for your preconceptions against Access, mostly if your learned them from a dumb teacher.
But I encourage you to open your eyes, expand your mind and become aware that much of the criticism against Access is in reality False.
Regards,
Diego

Posted by: ButtonMoon Oct 14 2013, 05:54 AM

Hi George,
You may be right. Many people using Access wonít necessarily be designing and building databases. Some will be though and the topic in this thread is a database design matter. Dmhzx asked some relevant questions and I tried to answer with as full an explanation as I could manage in a short time. Hopefully Iíve avoided any highly technical language and complex theory in my reply!
Ohavenít noticed much of what you call "academic style" in peer-reviewed computer science literature. Itís of course very common among people who pedal IT products and ideas and use pseudo-science to sell them.
More worrying to me is the tendency of some in the IT profession to treat foundation knowledge with suspicion or even outright hostility. I mean the tendency of some to denigrate education and science as "mere theory" and to value cook-book style solutions and dubious lessons learnt by "experience" more than fundamental knowledge. The people who think that way are probably a small minority but Iím not sure if the tendency has been increasing or decreasing over the last few years. Lack of rigor and failure to apply proven principles is still too common in the IT Industry despite the existence of an enormous body of science that IT professionals ought to be drawing on. Evidence of the resulting expensive technology failures and their costs to industry and society are all around us.
I donít suppose the same disrespect for foundation knowledge exists in other comparable professions - medicine or engineering for example. Doctors and engineers of course are not just expected to know the science of their professions - foundation knowledge is an essential requirement. The fact that such knowledge isnít really mandatory in the IT profession makes it all the more important to take the trouble to learn it and to promote it.

Posted by: argeedblu Oct 14 2013, 06:09 AM

@ButtonMoon
As Ace and Cybercow have already asked, please point out some specific database learning tools/paths.
Glenn

Posted by: genoma111 Oct 14 2013, 06:42 AM

QUOTE (ButtonMoon)
More worrying to me is the tendency of some in the IT profession to treat foundation knowledge with suspicion or even outright hostility. I mean the tendency of some to denigrate education and science as "mere theory" and to value cook-book style solutions and dubious lessons learnt by "experience" more than fundamental knowledge.

Science is by definition based upon experience. Knowledge is built from experience not from theory. Theory is a practice that help us to observe our world from perspectives accumulated by different people during several years and even centuries. Both theory and experience are equally valuable and any discussion that tries to make one superior to the other is useless.
Cook-book style solutions are valuable too, mostly for technicians, but also for hardcore scientists since they help to get the job done without having to go through all the paths other people had walked.
Concepts, classifications and ontologies are always evolving (i.e. changing) even the most "fundamental" ones.
In genetics for example we can't really tell what a gene is nowadays. Can you imagine that?
In medicine, we deal with the so-called "complex-traits" which is an elegant way of saying that we don't know what the [censored] is producing an observed character/disease.
And believe it or not, complex-traits are the rule, not the exception: Diabetes, Hypertension, Cancer, Cleft Lip and Palate are complex traits.
If you want, you can see Access as a mutant that is evolving a little bit different than mainstream database systems.
That's as natural as speciation and if you think for a moment, is speciation in database systems resulting from the action of several evolutionary forces. <
HAs I said, open your mind so you can appreciate all the good things Access has to offer.
Regards,
Diego
Edit
Ohighly recommend you (and all other people) to read this book... it is just beautiful. http://www.amazon.com/Ways-Worldmaking-Nelson-Goodman/dp/0915144514

Posted by: ButtonMoon Oct 14 2013, 07:44 AM

Access is a fine application developer's tool. My criticism of Access specifically as a platform for learning about data management is simply that the development features of Access get in the way of a proper understanding of database issues. As evidenced by this thread, some Access users at least are confused about what keys and indexes are because Access doesn't use the same terminology used in database design theory and practice. Access's QBE query designer doesn't support even some basic types of query that database beginners ought to master. Jet/ACE doesn't support some important features of SQL (all implementations of SQL are different of course; Jet/ACE is more different than others and so is not the place to begin learning SQL). The so-called "relationships" UI uses a pictorial style that is so unconventional, lacking in features and so contrary to good data modelling practice that it demonstrates more bad things than good. I could go on. I repeat, Access is a fine developer's tool; it's a poor environment to learn about databases in.
When beginning to learn database design, first take a course or study some good database theory books by a respected author like Chris Date. Then move on to books that deal with modelling and design issues - my old favourites are still Terry Halpin's "Information Modelling" book and Fabian Pascal's "Practical Issues in Database Management" but there are other good books around too. Avoid books or courses that emphasise any one piece of software. To gain experience in SQL, learn how to write queries or try out database designs try practicing first on one of the major SQL DBMSs, e.g. SQL Server, Oracle, DB2, MySQL, all of which are free to download and use and can be installed in minutes (much cheaper and quicker than Access). These are the products that dominate and define the database landscape and the ones that database professionals are probably most likely to encounter. Once you're comfortable with the basics of data management, that might be the time to try using Access on a database project, but if you use it beforehand and rely only on what you find in Access to teach you about databases then you'll probably learn much that is misleading, incomplete or just wrong.

Posted by: BananaRepublic Oct 14 2013, 08:34 AM

While it would be nice that everyone learned everything the right way the first time go, that's not how it works out in real world. Most of people who come to use Access are not database designer by trade; it's merely incidental to their main job. I don't really see them going to a bookstore and picking up a book about lofty concepts, especially when they only have a Friday afternoon to get something going for them.
It's only after their initial efforts has succeeded beyond what they originally intended, do they realize the necessity and may either endeavor to learn more or hire a professional.
Furthermore, I don't think it's realistic to expect that they'd download some free edition of RDBMS --- they might not be even able, and they likely started with Access only because it was already installed. That's what I've always considered Access' selling point - its ubiquity and accessibility. There's been plenty of moaning over "features", but the first goal is to solve a business problem, not to learn a relational theory.
I think one goal of UtterAccess is to get everyone started on the right theory, but it has to be accessible. Everyone is more likely to respond to "a place for each thing, and everything in their own place" than to "must not violate third normal form." Also, because as acknowledged, Access has its own set of terminology that they'd still have to learn anyway had they started out "right" in order to apply the "right thing." For those who don't/won't be a database professional, it's far more practical to show them how to do it right in Access than telling them about a theory then having to show it anyway in Access.
BTW, this debate is no mean restricted to relational theory. For example, see http://en.wikipedia.org/wiki/Worse_is_better, contrasted with "The Right Thing." I actually find it interesting that Access would probably fall into "Worse is better" camp and is part of why it's more successful.

Posted by: doctor9 Oct 14 2013, 08:47 AM

ButtonMoon,
Sorry, I have to disagree here. That's like teaching a person about performing music by handing them a really good book on music theory. No matter how well-written and insightful the book is, it can't compare to handing the person an actual instrument. Even a kid who starts out by playing a cheap little electronic keyboard will have the actual experience of creating music. The same goes for databases - nothing compares to actually building the tables, queries, forms, and reports, even if they are dealing with a specific product's syntax and quirks.
We learn by doing.
Dennis

Posted by: BananaRepublic Oct 14 2013, 09:36 AM

FYI:
Regarding the side topic about inability to post to topics created by wiki talk -- that has been fixed now and anybody should be able to use the talk topic created by the wiki now.

Posted by: ButtonMoon Oct 14 2013, 09:46 AM

That's good news. Thanks.

Posted by: ace Oct 14 2013, 12:01 PM

I have to agree that if your goal is to learn relational database theory then a book about Access is not the place to start.
Although I have to say the first book I selected on relational database design and theory did use Access to implement
the physical model.

Posted by: JonSmith Oct 14 2013, 01:09 PM

I am going to jump in and defend ButtonMoon abit. He is 100% right about things like the QBE, as I recently discovered (with some help from him) the QBE is awful with complex queries going so far as breaking perfectly good queries by trying to autocorrect them. It doesnt fostered a good environment for learning alot of the meaty database concepts. The enterprise solutions listed are undoubtedly superior.
That being said, Access is the stepping stone.
To continue the popular music analogy, you dont learn the piano by trying to play Moztarts 5th Symphonia. You play twinkle twinkle, build up from there. The more fundamental principles you can learn the better and if you can be taught the more complex aspects earlier on then all the better, a la this wiki.
Just to quickly touch on the snooty at the opera, see the video on the 'homeless' man playing violin in the subway go totally ignored. The man being one of the greatest violin players in the world in disgise. Perception is everything.

Posted by: genoma111 Oct 14 2013, 05:13 PM

QUOTE (JonSmith)
The enterprise solutions listed are undoubtedly superior.

Odoubt about the accuracy of that sentence.
For me, a superior system is one that simplifies my life not one that overcomplicates it.
If I have to earn a PhD for being able to use a tool, then that tool can be powerful but never superior.
...
QUOTE (ButtonMoon)
My criticism of Access specifically as a platform for learning about data management

No relational database system is good enough for learning about data management.
For a database professional data stored in 3N form can be regarded as "very clean"; in contrast for data miners and analysts the same body of data may mean aspirins, coffee and lack of sleep, before it can be regarded as "clean enough".
Contrary to your perspective, I consider Access a perfect place for learning about data management.
With Access I can perform all CRISP-DM data preparation steps for data coming from a wide variety of sources in many different formats including custom compressed formats.
So, I can live without some of the SQL specific features as well as I can live without fully supported inheritance in VBA.
QUOTE (ButtonMoon)
When beginning to learn database design, first take a course or study some good database theory books by a respected author like Chris Date.

That's unrealistic. As BR pointed out, I started using Access because it was already installed in my lab, and because I realised that several programs we used on daily basis were built on an Access mdb file: Progeny, EndNote, etc.
The query builder is one of the most amazing features of Access in terms of thought process: tables and relationships are not any more "ideas" but concrete "objects" you can see, manipulate and understand in few minutes.
With that "image" in your mind you can go to any database system and easily learn its specific rules. That's called abstraction.
aying that Access is not a good place for learning about database design is the same as saying you cannot learn how to play heavy metal music using an acoustic guitar...
Regards,
Diego

Posted by: ButtonMoon Jan 10 2014, 01:38 AM

I have updated the article with some fuller explanations. Hopefully an improvement.

Posted by: iliyanuhu Aug 20 2014, 10:17 AM

MicroSoft developed MS access and MS SQL Server (presumably bad and good) and continously support them, why intentionally support two different DBMS? think about that.

Posted by: dmhzx Aug 20 2014, 11:11 AM

I like to think of myself as a pragmatist, and a 'practicalist'.
If a tool is fit for the purpose you want to use it for, then it is fit for purpose.
The QBE for example is IMO the best such thing I've ever seen. It enables pretty complex queries to be put together with supreme ease. - Sure it doesn't do everything, but it's good for neatly everything I ever need.
HAs for the actual SQL it builds: In most cases I really don't care. I have far better things to do with my time, than pore over what Access has done: - I just save it and run it.
Access can be used by people from 'beginner' to fairly well advanced, and for the vast majority of everyday applications.
It has for the most part good links to other Office application by one route or another,
It has a bonus of VBA, which allows you to do things with 'Access' that have nothing to do with relational (or any other kind of ) database.
For the most part if you do make a design error, it's not too hard to fix. -- Just look at the rigmarole you have to go through with some "serious" products to add a field to a table, whose aficionados will sneer at you
"If you need to add a field , then your design was wrong in the first place".
Do I want to learn about database design and normalization? - Only if it helps with today's business problem. Do I care about storing calculated fields? - As long as I know the pros and cons. I can apply whatever weighting I want and make
my own decision.
The last time I worked on a 'proper' dbms, it didn't have even the concept of a memo field.
Nor did it allow tables in a query NOT to be joined
And after all that design and development work, do you think anyone cares about your third normal form, or super efficient queries? Do they care that your complex looking code shaves a few nano seconds
off a simpler looking easier to read code?
No.
If the application works properly, reliably, and quickly enough, the users will in general be pleased.
Back to the music analogy: You won't find many string quartet aficionados at a Status Quo concert: (or vice versa)- But who's to say which kind of music is 'better'.
But back to my original question, is there a practical difference between an 'index' and a 'key'? - Which were both in the title of the original posting.
It still seems to me that there isn't (at least not in Access). A 'Primary Key' just happens to the one unique index that you've decided is the most important (however you've defined 'important)
And for practical purposes (pragmatism) the two words are synonymous.
I have very much enjoyed reading all the comments.
UA can often be relied on to bring out 'varying' views on things.

Posted by: datAdrenaline Aug 20 2014, 01:14 PM

>> But back to my original question, is there a practical difference between an 'index' and a 'key'? <<
Yes ... a Key is a field or group of fields.
An index uses a key to create a 'map' of the data.
---
A key is used in many circumstances --- a relationship; an index; a WHERE clause.

Posted by: ace Aug 20 2014, 02:17 PM

A key is theoretical.
An index is physical.

Posted by: GroverParkGeorge Aug 20 2014, 03:14 PM

@ iliyanuhu. Either your question is based on a false premise, or you are simply trying to raise an argument. Either way, the statement that "... MicroSoft developed MS access and MS SQL Server (presumably bad and good)... " simply is not accurate. Neither SQL Server nor Access is "bad", nor is either "good". That sort of value judgment simply falls outside the boundaries of a relevant discussion here.
Please read dmhzx's exposition carefully for realistic insight into the relevant factors at work.
It is, on the other hand, quite clear that Access and SQL Server have their own strengths and that those strengths only partially overlap.
It would be a sad day, indeed, if somehow the development community were restricted to one implementation of each type of tool. One RDBMS, one coding platform, one Operating System, etc. It has long puzzled me that in some quarters, it is not enough to express a preference for one tool of many. There seems to be an impulse towards extending that to a wish for all other competitors to simply vanish. I will probably never crack the covers on a Postgres installation, for example, but I can't see that leading to a campaign to discourage its

Posted by: CyberCow Aug 26 2014, 11:58 PM

iliyanuhu - I consider such 'presumptions' as based in the narrower point of view that excludes the concept that Access was created as a "Rapid Development Tool" as opposed to SQL Server, which can be considered a target\goal for Access based pilot applications.
SQL Server is far more robust in several ways when considering the security, data modeling, relationships and myriad of side-chained table affects. Yet, SQL Server lacks any real user interface.
From my perspective, Access is the tool to use to 'prove out' a database concept from start to finish; allowing a developer to create not only a sound data model construct, but also a user interface - without the expense or time consumption of SQL Server.
Access is also great for small chunks of business model development data sets, providing a means to also create a GUI for continued testing of such data gathering\manipulation - before investing in the expense of an SQL Server class business tool. Then to top it off, MS also created Access to be able act as the front end for such large scale db's like SQL Server.
In my estimation, that is why MS intentionally created and supports two different DBMS's. One is inexpensive and can be developed more quickly and the other has far greater capacity, features and security for when the pilot application has proven its worth and is approved for graduation to the larger scale and scope of SQL Server.

Posted by: haresfur Sep 2 2014, 10:48 PM

Ok, so if I understand this properly, a key uniquely identifies a record and is nearly always indexed - in Access you make a key by defining an index where the key-field or combination of fields are required to be unique. It is good practice (but not required in Access) to have at least one key and for at least one key to be a non-composite (is there a term for a single-field key?) surrogate key defined as the primary key. It is possible in Access to designate an composite key as the primary key but that complicates the relationship between tables.
I'm not sure I understand the need for the primary key to be a surrogate key if you can guarantee that the natural key will not change or can cascade updates to the related tables.
I'm unclear on the statement
Say I have a composite natural key of (location, date_time). I could define a unique,non-null index for (location, date_time, value) but the quote from the wiki implies this would not represent a 'key' because it is not 'minimal'. Is that correct? I don't think there is anything in Access that prevents me from defining that as the primary key, though.

Posted by: argeedblu Sep 3 2014, 02:40 AM

I hope this reply will clear the water for you and not add to your confusion. If you feel your confusion rising as you read, please feel free to stop and ignore me.
It is good practice but not required in Access that each table have a primary key. At its heart this principle does not specify whether the primary key is a natural or a surrogate key. A natural key consists of one or more fields (i.e. composite) that taken together uniquely identify the record. If you choose to use a natural key, you do not need to also have a surrogate key. In my opinion, it would be unusual to have both.
I assume that you are referring to http://www.UtterAccess.com/wiki/index.php/Composite_Indexes_vs_Composite_Primary_Keys. That is not easy terminology to wrap your head around. However, if the combination of location and date_time that you have suggested can be guaranteed to uniquely identify the record, that composite would indeed be minimal. Minimal in this context simply means the smallest number of fields that taken together would never result in duplicate data.
The whole debate between natural and surrogate keys is a long-standing discussion (dare I say, 'dispute') among relational theorists. It is a definite 'hot button' issue for some with each side staunchly defending why their side is right and the other side is wrong. I have seen arguments where a proponent of natural keys characterizes the use of surrogate keys as plain and simple laziness. Personally, I prefer surrogates (call me lazy if you will) but have resorted to natural keys to solve some specific data management issues.
Glenn

Posted by: ace Sep 3 2014, 05:26 AM

The Minimal suggestion refers to when a key is constructed from actual attributes of the entity being modeled. The typical surrogate key
that is used is a value the database engine pulls out of it's butt, guaranteed to be unique for that "table". It has nothing to do with the
entity being modeled.

Posted by: haresfur Sep 3 2014, 11:21 PM

Thanks Glenn and Ace,
This is helpful and I believe I understand.
My impression from following UA, is that most people recommend using surrogate keys (usually hidden from the end user), and in particular avoid composite keys. On one hand I understand that, because you don't need to deal with potential changes to the key, you can design a systematic naming convention for key fields, and you don't have to mess with joins on multiple fields. However, I'm a bit surprised that you say it would be unusual to have both a surrogate key and a natural key. I've been thinking about this for my databases and it seems to be very important to understand what makes each record unique outside of any surrogate keys. I think it is possible to define a natural key for all my tables. If I depend only on the surrogate key, I could load duplicate records and the database would happily accept them but this would mess up a lot of things. Worse yet someone could potentially load different data for the same 'natural key' or edit a record to create a duplicate. So even if I use a surrogate key for convenience, I try to define a natural key so the logic behind the database can't be broken (am I the only one who has ever done that?-). If nothing else, it helps me be sure I really understand the data and business processes I'm working with. I work with a fair bit of imported data and a natural key seems to me to be the only way to find any external changes to data that already are found in my database.
Reading this thread, I tried to think of cases where a table would not have a logical natural key. I guess that could be the case if you have 2 people with the same name and don't have access to their DNA profile or finger prints to tell them apart. Then your surrogate key would be used to enforce their 'natural' uniqueness, right? But then presumably you might need some process to ensure you don't end up with clones in the database.
Thanks again. It really helps me think through all this.

Posted by: argeedblu Sep 4 2014, 02:25 AM

Hi Evan,
I'm glad that Ace and I were able to shed some light for you.
I think we may be dealing with a matter of semantics here. You can, and I do quite often, define composite indexes to ensure that duplicate records do not get created in the circumstances you describe. I use that approach whenever I create a junction table, for example. I don't think of a composite index as a key so my comment came from that lack of definition and thinking strictly in terms of primary keys. However, I think technically, a composite index is indeed properly referred to as a key.
My personal preference is to use a surrogate as the primary key is based on the difficulty of defining a natural key with the absolute certainty that it will guarantee uniqueness. Certainly the probability of uniqueness increases as the number of fields in the key increases but given a large enough population there is always at least the possibility that the uniqueness of the key will break down.
Glenn

Posted by: ace Sep 4 2014, 05:53 AM

You have to at least identify a natural key in order to normalize a table. There is nothing else to base normalization on.
Creating a index on the chosen natural key is the only way for the database engine itself to enforce your theoretical decisions.
You really do want the database to enforce your theoretical decisions.

Posted by: haresfur Sep 7 2014, 07:30 PM

Thanks again, this is most helpful.
I was thinking about how natural keys relate to normalization and you have confirmed my thoughts.
In terms of semantics, I guess I think of a composite index as a key if it applies uniqueness constraints (it doesn't have to). It seems to be a bit of an unfortunate side effect of Access imposing constraints through defining an index. In any case I need to look at my databases and be sure I enforce my natural keys on all tables through indexes. I think I will name them NaturalKey to document that is what the index is for.

Posted by: ace Sep 7 2014, 07:57 PM

How else would it be done? When you define a "Unique Constraint" in SQL Server the database creates an unique index to enforce it.
Good idea. I simply use the name "Unique"

Posted by: CyberCow Sep 7 2014, 09:07 PM

Regina Whip has a fresh blog post out on http://regina-whipp.com/blog/?p=704 that has several excellent examples - for even more clarity.

Posted by: datAdrenaline Sep 7 2014, 09:35 PM

Some terms to note ...
Key: a field (attribute) or group of fields (attributes). Nothing more, nothing less.
Natural Key: a key that is composed of attributes that describe the entity (no "system" fields, like an autonumber field).
Surrogate Key: a key that has at least one "system" field, like an autonumber field.
Composite Key: a key with multiple fields.
Candidate Key: a key the uniquely identifies a row of data.
Primary Key: in the context of a Table, it is the candidate key defined as the "primary" one. In the context of a relationship, the candidate key used on the "primary" table (aka: parent table) that is referenced by the foreign table. The key of the foreign table (aka: child table) of the relationship is called the Foreign Key.
----
- All tables should have a Primary Key. Many DBMS do not require a primary key to design/create a base table.
- ACE/Jet is fine database management system, So is SQL Server, Oracle, and many others -- they each have some characteristics that allow a programmer to blatantly ignore normalization rules -- heck its not that hard to design a non-normalized database in ANY dbms. Remember, the relational data model is a way to design schema, normalization guidelines help you adhere to the relational data model. A database designed to 3NF (3rd normal form) is a typical goal.