[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