Jim Dettman
jimdettman at verizon.net
Tue Aug 12 21:11:37 CDT 2014
I think it would depend on the string. Gains in the difference in register operations could be far out weighed by the gain in index operations. As a simple example, a long is four bytes, which means any string key shorter than that would pack more key entries per index page. Search operations would be faster as a result And that's besides the fact that I'd have one less index on the table to maintain. One place I think it's silly to have a AN as a primary key is a many to many linking table (assuming it has no additional attributes other than expressing the relationship between the tables). You need a compound index on the two foreign keys no matter what, so why throw an AN on top of that? Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan Sent: Tuesday, August 12, 2014 05:48 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Field linked to different tables Because indexing on a Long is more efficient than indexing on a character string? (It's computationally much faster and uses far less storage). -- Stuart On 12 Aug 2014 at 13:14, Jim Dettman wrote: > <<why give a list of countries an ANPK when there already exists a > well-defined ISO list of countries, each having a unique char code>> > > Someone on my side of the fence for a change<g> > > Jim. > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur > Fuller Sent: Tuesday, August 12, 2014 11:39 AM To: Access Developers > discussion and problem solving Subject: Re: [AccessD] Field linked to > different tables > > David, > > I've had to skin a cat like this a few times in the past, and agree > with Gustav's suggestion. In fact, the experience has caused me, in > several situations, to re-think the whole knee-jerk AutoNumberPK thins > that I once depended on to the exclusion of logic and common sense. > (For example, why give a list of countries an ANPK when there already > exists a well-defined ISO list of countries, each having a unique char > code?) > > Arthur > > > On Mon, Aug 11, 2014 at 8:24 PM, David Emerson <newsgrps at dalyn.co.nz> > wrote: > > > Thanks for the reply Stuart. This will still have the problem in > > the combobox that it may not show the correct selected record if the > > ID's from the various tables in the first column double up. > > > > Gustav suggested making a composite key that combines the > > "WhoAboutType" and source table ID's so that there will not be any > > replications. This will solve the problem. > > > > Regards > > > > David Emerson > > Dalyn Software Ltd > > Wellington, New Zealand > > > > > > > > -----Original Message----- > > From: accessd-bounces at databaseadvisors.com > > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart > > McLachlan Sent: Tuesday, 12 August 2014 12:06 p.m. To: Access > > Developers discussion and problem solving Subject: Re: [AccessD] > > Field linked to different tables > > > > How about another field in the Notes table "WhoAboutType" (1 for > > Client, > 2 > > for Staff, 3 for Company or whatever) > > > > Then > > Select 1 as WAType, ClientPK as PK, CLientName as EntityName frm > tblClients > > UNION Select 2 as WAType, StaffPK as PK, StaffName as EntityName > > from tblStaff UNION Select 3 as WAType, CompanyPK as PK, CoName as > > EntityName from tblCompany. > > > > > > Then Join on (WAType = WhoAboutType and PK = LinkRecordID) > > > > > > -- > > Stuart > > > > On 11 Aug 2014 at 15:23, David Emerson wrote: > > > > > Hi Listers, > > > > > > > > > > > > I am looking for some data structure advice. I will use a simple > > > example but the real world application will be more complicated. > > > > > > > > > > > > I have a Notes table. The Notes table fields include WhoAbout, > > > LinkRecordID. > > > > > > > > > > > > WhoAbout could be Client, Staff member, or Company. > > > > > > LinkRecordID is the ID from the Client, Staff member, or Company > > > table depending on what was selected in the WhoAbout field. > > > > > > > > > > > > The records from the Notes table will be displayed in a continuous > > > form showing WhoAbout and the name from the corresponding table > > > for the record selected in LinkRecordID. > > > > > > > > > > > > The Linkrecord field on the form is a combobox with a union of the > > > records from the three tables. Data from all three tables are > > > needed because it is a continuous form and I don't want to change > > > the recordsource for each current row as this would show incorrect > > > data in other rows. > > > > > > > > > > > > Immediate problem: Even though I could put code in place to ensure > > > that the record selected is of the same type as the WhoAbout field > > > the correct row may not show if it has the same ID as another > > > record from one of the other tables. > > > > > > > > > > > > Question: Has anyone else come across this problem and what might > > > be the solution? > > > > > > > > > > > > Regards > > > > > > David Emerson > > > Dalyn Software Ltd > > > Wellington, New Zealand > > > > > > > > > > > > > > > > > > -- > > > AccessD mailing list > > > AccessD at databaseadvisors.com > > > http://databaseadvisors.com/mailman/listinfo/accessd > > > Website: http://www.databaseadvisors.com > > > > > > > > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > > > -- > Arthur > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com