X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
3 Pages V < 1 2 3 >  (Go to first unread post)
   Reply to this topicStart new topic
> Composite Indexes Vs Composite Primary Keys, Wiki Talk    
post Oct 13 2013, 03:01 PM

Posts: 2,018
Joined: 2-June 09
From: Bogotá - Colombia

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.
Go to the top of the page
post Oct 14 2013, 05:54 AM

Utterly Banned
Posts: 136
Joined: 11-January 12

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.
Go to the top of the page
post Oct 14 2013, 06:09 AM

Posts: 14,079
Joined: 26-September 02
From: Sudbury, Ontario, Canada

As Ace and Cybercow have already asked, please point out some specific database learning tools/paths.
Go to the top of the page
post Oct 14 2013, 06:42 AM

Posts: 2,018
Joined: 2-June 09
From: Bogotá - Colombia

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.
Ohighly recommend you (and all other people) to read this book... it is just beautiful. Ways of Worldmaking
Go to the top of the page
post Oct 14 2013, 07:44 AM

Utterly Banned
Posts: 136
Joined: 11-January 12

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.
Go to the top of the page
post Oct 14 2013, 08:34 AM

Dungeon Cleaner
Posts: 1,520
Joined: 16-June 07
From: Banana Republic

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 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.
Go to the top of the page
post Oct 14 2013, 08:47 AM

Posts: 18,324
Joined: 29-March 05
From: Wisconsin

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.
Go to the top of the page
post Oct 14 2013, 09:36 AM

Dungeon Cleaner
Posts: 1,520
Joined: 16-June 07
From: Banana Republic

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.
Go to the top of the page
post Oct 14 2013, 09:46 AM

Utterly Banned
Posts: 136
Joined: 11-January 12

That's good news. Thanks.
Go to the top of the page
post Oct 14 2013, 12:01 PM

UtterAccess VIP
Posts: 5,446
Joined: 26-November 05
From: Upstate NY, USA

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.
Go to the top of the page
post Oct 14 2013, 01:09 PM

UtterAccess VIP
Posts: 4,073
Joined: 19-October 10

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.
Go to the top of the page
post Oct 14 2013, 05:13 PM

Posts: 2,018
Joined: 2-June 09
From: Bogotá - Colombia

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...
Go to the top of the page
post Jan 10 2014, 01:38 AM

Utterly Banned
Posts: 136
Joined: 11-January 12

I have updated the article with some fuller explanations. Hopefully an improvement.
Go to the top of the page
post Aug 20 2014, 10:17 AM

Posts: 122
Joined: 14-June 11

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.
Go to the top of the page
post Aug 20 2014, 11:11 AM

Posts: 7,115
Joined: 22-December 10
From: England

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?
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.
Go to the top of the page
post Aug 20 2014, 01:14 PM

UtterAccess Editor
Posts: 18,007
Joined: 4-December 03
From: Northern Virginia, USA

>> 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.
Go to the top of the page
post Aug 20 2014, 02:17 PM

UtterAccess VIP
Posts: 5,446
Joined: 26-November 05
From: Upstate NY, USA

A key is theoretical.
An index is physical.
Go to the top of the page
post Aug 20 2014, 03:14 PM

UA Admin
Posts: 36,778
Joined: 20-June 02
From: Newcastle, WA

@ 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
Go to the top of the page
post Aug 26 2014, 11:58 PM

UdderAccess Admin + UA Ruler
Posts: 19,557
Joined: 27-April 02
From: Upper MI

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.
Go to the top of the page
post Sep 2 2014, 10:48 PM

Posts: 303
Joined: 4-April 12
From: Bendigo, Australia

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.
Go to the top of the page
3 Pages V < 1 2 3 >

Custom Search

RSSSearch   Top   Lo-Fi    23rd February 2020 - 06:19 PM