Discussion: What are the advantages/disadvantages to combination k

I'm in the fortunate position of not having any time pressure on this
project so I can take the time to do it 'right'. But then on the other
hand,
I have to fit this into free time between work and home - so development
is
very slow and sometimes I have to 're-learn' how to do things.

A project with no "time pressure"? Is that really a project? g

I must have a different book than you (or maybe I got the title wrong?) -
I
thought that this one's by Michael Hernandez.

Yes, Michael wrote the one you are referring to. I'm reading Rebecca
Riordan's Designing Relational Database Systems right now and had it on my
brain. Sorry about that. Apologies to Michael and Rebecca too, for confusing
them g.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"rpw" wrote in message
...


"Lynn Trapp" wrote:

You may find that a Google search
on "surrogate keys" would return you some interesting, and lively,
discussions of the subject.

I'll do that later on today...

You should probably reconsider the advice of the local user group. By
allowing the employees to enter duplicates, you not only violate one of
the
cardinal principles of database design, but make more work for you
payroll
department, when they have to track down the employee who entered the
duplicate. I assume after they talk to this employee that they then have
to
go out and delete the duplicate record. Wouldn't it be better to find a
way
to preven its entry in the first place?

I'll keep working on that. In the meantime I looked at my db again and
the
'duplicates' are not actually duplicate records, but rather multiple
entries
on the same day by the same employee. This could have happened for any
number of reasons. As I study this issue deeper, I'll consider the
indexing
of date, EmpID, (maybe start and end times too) vs. combination key.

I'm in the fortunate position of not having any time pressure on this
project so I can take the time to do it 'right'. But then on the other
hand,
I have to fit this into free time between work and home - so development
is
very slow and sometimes I have to 're-learn' how to do things.




btw, on Saturday I got delivery of "Database Design for Mere Mortals",
so
maybe by the time I finish reading it, I'll be a little better
oriented on
these 'Natural key, combo key, surrogate key issues. But for now, I
still
like the convenience of using just a single PK field.

That's excellent. Rebecca has an excellent discussion of "candidate
keys" in
a real world context and in easy to understand language. She seems to
lean
in favor of surrogate keys but not without a proper understanding of
data
duplication.

I must have a different book than you (or maybe I got the title wrong?) -
I
thought that this one's by Michael Hernandez.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

"Lynn Trapp" wrote:
A project with no "time pressure"? Is that really a project? g

I'd be happy to send you version one and version two if you like. by the
way, I assigned myself this 'project' cause the things I saw in the books
didn't have any relevance to me and this did. The project will be useful to
my employer, but the company doesn't know I'm working on it - hence no time
pressure.

I'll be sure to keep Ms. Riordan's book in mind when I'm hungry for more db
design knowledge.

I must have a different book than you (or maybe I got the title wrong?) -
I
thought that this one's by Michael Hernandez.

Yes, Michael wrote the one you are referring to. I'm reading Rebecca
Riordan's Designing Relational Database Systems right now and had it on my
brain. Sorry about that. Apologies to Michael and Rebecca too, for confusing
them g.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"rpw" wrote in message
...


"Lynn Trapp" wrote:

You may find that a Google search
on "surrogate keys" would return you some interesting, and lively,
discussions of the subject.

I'll do that later on today...

You should probably reconsider the advice of the local user group. By
allowing the employees to enter duplicates, you not only violate one of
the
cardinal principles of database design, but make more work for you
payroll
department, when they have to track down the employee who entered the
duplicate. I assume after they talk to this employee that they then have
to
go out and delete the duplicate record. Wouldn't it be better to find a
way
to preven its entry in the first place?

I'll keep working on that. In the meantime I looked at my db again and
the
'duplicates' are not actually duplicate records, but rather multiple
entries
on the same day by the same employee. This could have happened for any
number of reasons. As I study this issue deeper, I'll consider the
indexing
of date, EmpID, (maybe start and end times too) vs. combination key.

I'm in the fortunate position of not having any time pressure on this
project so I can take the time to do it 'right'. But then on the other
hand,
I have to fit this into free time between work and home - so development
is
very slow and sometimes I have to 're-learn' how to do things.




btw, on Saturday I got delivery of "Database Design for Mere Mortals",
so
maybe by the time I finish reading it, I'll be a little better
oriented on
these 'Natural key, combo key, surrogate key issues. But for now, I
still
like the convenience of using just a single PK field.

That's excellent. Rebecca has an excellent discussion of "candidate
keys" in
a real world context and in easy to understand language. She seems to
lean
in favor of surrogate keys but not without a proper understanding of
data
duplication.

I must have a different book than you (or maybe I got the title wrong?) -
I
thought that this one's by Michael Hernandez.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm