UtterAccess.com
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    
 
   
argeedblu
post Sep 3 2014, 02:40 AM
Post#41


UA Forum + Wiki Administrator
Posts: 14,040
Joined: 26-September 02
From: Sudbury, Ontario, Canada


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 this article. 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
Go to the top of the page
 
ace
post Sep 3 2014, 05:26 AM
Post#42


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


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.
Go to the top of the page
 
haresfur
post Sep 3 2014, 11:21 PM
Post#43



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


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.
Go to the top of the page
 
argeedblu
post Sep 4 2014, 02:25 AM
Post#44


UA Forum + Wiki Administrator
Posts: 14,040
Joined: 26-September 02
From: Sudbury, Ontario, Canada


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
Go to the top of the page
 
ace
post Sep 4 2014, 05:53 AM
Post#45


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


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.
Go to the top of the page
 
haresfur
post Sep 7 2014, 07:30 PM
Post#46



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


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.
Go to the top of the page
 
ace
post Sep 7 2014, 07:57 PM
Post#47


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


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"
Go to the top of the page
 
CyberCow
post Sep 7 2014, 09:07 PM
Post#48


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


Regina Whip has a fresh blog post out on Sequential Numbering that has several excellent examples - for even more clarity.
Go to the top of the page
 
datAdrenaline
post Sep 7 2014, 09:35 PM
Post#49


UtterAccess Editor
Posts: 17,926
Joined: 4-December 03
From: Northern Virginia, USA


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


Custom Search
RSSSearch   Top   Lo-Fi    23rd October 2017 - 03:12 AM