Unique Index: Concat Text & Date Fields?

#11

Phil wrote:

Quote:

On 26/07/2012 04:28:59, "David Hare-Scott" wrote:
Bob Quintal wrote:
The Frog <Mr.frog.to.you (AT) googlemail (DOT) com> wrote in
news:almarsoft.5924047734445792180 (AT) news (DOT) aioe.org:

Multi field primary key? Nope. I would do it a little differently
though, but same effect. Use an auto number for the primary, and
unique index on the multi field. Same same, but different. Just
makes it easier if you have external data to relate to it.

An autonumber field in any table is a violatiuon of the first normal
form. They are a workaround to taking the time and effort to
identify the real key.

Only if you are religious. ;-)

D

Surely we all use it most of the time in a join table.
EG
Person table
Hobby table
JnPersonHobby which has a unique key, no duplicates
PersonID
HobbyID

This allows 1 person multiple hobbies and 1 hobby with lots of
participants, but you can't enter a hobby twice for one person

Phil

I don't see the connection between what you are saying and the issue of
natural versus synthetic keys.

D

#12

I would have to say that using a synthetic key provides me better
solutions than natural keys. Phil's approach is simply a
demonstration of that. The classic case where a natural key fails is
two people with the same name at the same location etc... How far do
you take a natural key to create a unique capture of data before it
becomes ridiculous? Down to the DNA?

A natural key is only useful if its practical. A synthetic key allows
you to capture the logic of the problem without unnecessary added
complexity. It's a practical solution to real world problems.

--
Cheers

The Frog

#13

The Frog <Mr.frog.to.you (AT) googlemail (DOT) com> wrote in
news:almarsoft.7658155737436708927 (AT) news (DOT) aioe.org:

Quote:

I would have to say that using a synthetic key provides me better
solutions than natural keys. Phil's approach is simply a
demonstration of that. The classic case where a natural key fails
is two people with the same name at the same location etc... How
far do you take a natural key to create a unique capture of data
before it becomes ridiculous? Down to the DNA?

A natural key is only useful if its practical. A synthetic key
allows you to capture the logic of the problem without unnecessary
added complexity. It's a practical solution to real world
problems.

I have no problem with a synthetic key.

A synthetic key is not necessarily an autonumber. People often default
to using an autonumber instead of generating a simple, as you say
practical, key.

--
Bob Q.
PA is y I've altered my address.

#14

"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote in
news:juqqr0$3cr$1 (AT) speranza (DOT) aioe.org:

Quote:

On 26/07/2012 04:28:59, "David Hare-Scott" wrote:
Bob Quintal wrote:
The Frog <Mr.frog.to.you (AT) googlemail (DOT) com> wrote in
news:almarsoft.5924047734445792180 (AT) news (DOT) aioe.org:

Multi field primary key? Nope. I would do it a little
differently though, but same effect. Use an auto number for the
primary, and unique index on the multi field. Same same, but
different. Just makes it easier if you have external data to
relate to it.

An autonumber field in any table is a violatiuon of the first
normal form. They are a workaround to taking the time and effort
to identify the real key.

Only if you are religious. ;-)

D

Surely we all use it most of the time in a join table.
EG
Person table
Hobby table
JnPersonHobby which has a unique key, no duplicates
PersonID
HobbyID

This allows 1 person multiple hobbies and 1 hobby with lots of
participants, but you can't enter a hobby twice for one person

Phil

Great if your pk for JnPersonHobby is (PersonID,HobbyID) I agree with
that.

What I'm preaching against is
JnPersonHobby which has a unique key, no duplicates
Person_HobbyID (autonum, PK)
PersonID
HobbyID
and a separate index to prevent duplicates.

--
Bob Q.
PA is y I've altered my address.

#15

Must we sit through another repetition of the natural key versus surrogate
key battle?

Aside from the occasional circumstance of information with no
reasonably-determinable natural key, of course, the autonumber is simply an
approach to "abbreviating" the natural key, and, yes, if you are a
relational purist or an academic, that is an unacceptable violation of first
normal form; if you are a pragmatist, it's an efficacious approach so you do
not have to specify multiple (sometimes quite a few) fields every time you
use the key.

Many of us are willing to accept the "violation" because using the
"abbreviation" does not cause us any problems. We can use the natural key to
assure uniqueness, and the surrogate (Autonumber) key for convenience in
joining.

--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

"Bob Quintal" <rquintal (AT) sPAmpatico (DOT) ca> wrote

Quote:

The Frog <Mr.frog.to.you (AT) googlemail (DOT) com> wrote in
news:almarsoft.5924047734445792180 (AT) news (DOT) aioe.org:

Multi field primary key? Nope. I would do it a little differently
though, but same effect. Use an auto number for the primary, and
unique index on the multi field. Same same, but different. Just
makes it easier if you have external data to relate to it.

An autonumber field in any table is a violatiuon of the first normal
form. They are a workaround to taking the time and effort to identify
the real key.

--
Bob Q.
PA is y I've altered my address.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Unique Index: Concat Text & Date Fields?

comp.databases.ms-access comp.databases.ms-access

Re: Unique Index: Concat Text & Date Fields? - 07-26-2012 , 03:13 AM

Re: Unique Index: Concat Text & Date Fields? - 07-26-2012 , 04:00 AM

Re: Unique Index: Concat Text & Date Fields? - 07-26-2012 , 05:29 AM

Re: Unique Index: Concat Text & Date Fields? - 07-26-2012 , 05:38 AM

Re: Unique Index: Concat Text & Date Fields? - 07-26-2012 , 02:41 PM