Stuart McLachlan
stuart at lexacorp.com.pg
Tue Aug 12 21:56:41 CDT 2014
True, a Long is four bytes. But the minimum length of a non-Null string is also four bytes. An initial two bytes to storelength of the string, followed by the characters stored as 2 byte WCHARs. So a four character string actually requires 10 bytes of storage. Strings take a lot more storage than longs. As for searching, It only requires one operation to compare two 32 bit values (i.e. longs). String comparisons take orders of magnitude more time to do. I agree with you totally about link tables. They are a different matter entirely. If you consider it from an Entity/Relationship perspective, unlike a normal data table - they do not contain entity data, only relationship information. By their nature, they do not require a separate key. -- Stuart On 12 Aug 2014 at 22:11, Jim Dettman wrote: > > 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 > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >