[AccessD] Access and SQL Server

Edward Zuris edzedz at comcast.net
Wed Mar 2 11:48:58 CST 2011



 My pleasure. . .  

 Have to run, and get back to my can of worms.


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil
Salakhetdinov
Sent: Wednesday, March 02, 2011 10:00 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Access and SQL Server


Hi Edward,

Thank you for your note.

<<<
 It seems many projects start out 'consistent'
 then become 'inconsistent'.
>>> 
Refactoring is used to keep a project consistent/get a project back into
a consistent state.

See also "Technical Debt" (http://en.wikipedia.org/wiki/Technical_debt)
related books, articles, discussions... and how to handle that
"technical debt" to keep one's project consistent...

In my experience approx. 20% of a project time is needed to keep it
consistent. That "additional" efforts do pay back a lot in long run. And
if one will keep a "technical debt" growing they will get "technically
bankrupt" sooner or later. One (a seasoned developer) should probably
better not inform their customers when and how they keep their project
"technical debt" work performed but for a "good" customer with long
lasting relations such a "secret" should be opened...

Thank you.

--
Shamil
 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Edward Zuris
Sent: 2 ????? 2011 ?. 18:57
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Access and SQL Server


 Go for it. . . .

 It seems many projects start out 'consistent'
 then become 'inconsistent'.

 A great debate about consistent vs inconsistent  might be more
profitable than discussing the  person in the street reacting to the
Banksters  losing all the world's money.  It seems only  a few nations
are immune to such.

 Well I better get back to work straightening  out my once 'consistent'
project. . .



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil
Salakhetdinov
Sent: Wednesday, March 02, 2011 8:13 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Access and SQL Server


Jim --

That our "'consistent' vs. 'inconsistent'" data modeling discussion
promise to become a new "great debate" as "bound vs. unbound" and
similar debates...

Real world is inconsistent or consistent? - no answer? many answers?
Should data modeling practice follow real world inconsistency
(consistency?) or not?

Your and others choice now is to start "consistent vs. inconsistent"
thread to find constructive answers when it's worth following and when
it's not - for sure there will be no definitive one answer as a result
of such a discussion still it could be rather useful...

Start it? Yes/No?

Thank you.

--
Shamil
 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: 2 ????? 2011 ?. 16:04
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Access and SQL Server

Shamil,

  Don't loose sight of the fact that I was specifically talking about a
many to many linking table.

  All the attributes your talking about below would belong in the books
table.  As this is simply a many to many linking table, no other fields
would ever be added to it.

 I have never found a need to point to a record in a many to many table
either, so adding a auto number field just for the reason of "that's
always the way I do it" or "because it's consistent", I think is a
waste.

 If this happened to involve tables with millions of records, the
addition of another index would mean a pretty sizeable performance hit.

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil
Salakhetdinov
Sent: Wednesday, March 02, 2011 03:13 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Access and SQL Server

Hi Jim -- 

<<<
<<If not - be prepared that inconsistent data model design will bring
you a lot of troubles in long run.>>
  Have never had a problem yet...
>>>
Lucky man :)

In your sample case with tblBooksAndAuthors the relation table's
compound index (AuthorID, BookID) could become non-unique, e.g. when
books will get [Edition] field introduced. 
And you'll have to decide where to put [Edition] (+ [CopyrightDate],
...) fields - into [tblBooksAndAuthors] or into [tblBook] table or into
new [tblBookEditions] table. Then you'll have to investigate all your
Queries: Joins, Group Bys, ... and related Forms, Reports, VBA code (if
any) to see how [Edition] field introduction influenced them. 

Yes,  in the case when  [tblBooksAndAuthors] uses dedicated Autonumber
field [LinkId] , introduction of [Edition] field would also require to
decide in what table to put the new [Edition] field and would also
require reinvestigation/refactoring of (some of) Queries, Forms,
Reports, VBA code but all the work in that case would be "routine &
mechanical".

Your customer might also decide (one fine day) that having just one
author for a book isn't a "good idea" and that introduction of several
authors for a book is a "simple change request"...

Thank you.

--
Shamil
 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: 2 ????? 2011 ?. 3:21
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Access and SQL Server

Shamil,

<<If not - be prepared that inconsistent data model design will bring
you a lot of troubles in long run.>>

  Have never had a problem yet...

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil
Salakhetdinov
Sent: Tuesday, March 01, 2011 02:34 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Access and SQL Server

Hi Jim --
 
<<<
 and eliminate an index?  If so, why not?
>>>
In my opinion good data model should be consistent - therefore all and
every table's PK should be an Autonumber/Identity column, even
linking/relation tables. If not - be prepared that inconsistent data
model design will bring you a lot of troubles in long run.

--
Shamil
 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: 1 ????? 2011 ?. 21:40
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Access and SQL Server


 So on a many to many linking table you would do this:

tblBooksAndAuthors
LinkID - Autonumber - PK
AuthorID - Long - FK to tblAuthors - CK-A BookID - Long - FK to tblBooks
- CK-B

And not simply:

tblBooksAndAuthors
AuthorID - Long - FK to tblAuthors - PK-A BookID - Long - FK to tblBooks
- PK-B

 and eliminate an index?  If so, why not?

Jim.
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Tuesday, March 01, 2011 12:47 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Access and SQL Server

When I create a table in any datastore, the first thing I do is create
an autoincrement PK.  I no longer even think about it - if need a table,
I need an autonumber pk!

I then proceed to create the fields.

John W. Colby
www.ColbyConsulting.com

On 3/1/2011 12:17 PM, Jim Lawrence wrote:
> Many years ago I was taking over an Access project as the clients were
> having problems with their invoices. After about two days I discovered

> the problem with the invoice.
>
> It appears that the subform was connected to the main form by grouping
> together 3 fields, creating natural foreign key between the two 
> tables. By some odd set of bad luck certain combinations of this key 
> hash matched another unrelated key value and the sub form data was 
> pulling from
multiple
> invoice details.
>
> The only reliable solution was to move all the tables to auto PKs but
> it cost the client a fair chunk of change. For that reason I have 
> never inflicted natural keys, on a client, no matter how strong the
temptation.
>
> Jim
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart
McLachlan
> Sent: Monday, February 28, 2011 3:00 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Access and SQL Server
>
> I see a lot of sense in it having a separate Autonumber PK.   This is
a
> classic case of why
> you should not use a natural key as your PK.
>
> What happens when the Description changes and the existing Code is no
longer
> an accurate
> short representation of Description?  Do you change it throughout all
> the tables which store it or do you leave your customer with strange 
> Codes which don't match the description
>
> (And please don't tell me that you use Relationships with "Cascade
> Update" turned on.)
>
>


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list