[AccessD] Access and SQL Server

Shamil Salakhetdinov shamil at smsconsulting.spb.ru
Wed Mar 2 11:00:22 CST 2011


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.)
>
>





More information about the AccessD mailing list