[MS_AccessPros] Re: Disadvantages of using String as Primary Key

 

Olusesan

Oy! That is a mess waiting to blow up. What I'd do is put a real primary key in the table and just do the concatenation in forms and reports so if one of the fields changes it won't have to be updated in the fake primary key. Just changing it one time in the

Whoever decided that the concatenated key was a good thing does not understand the difference between storing data and presenting it.

Bill

--- In MS_Access_Professionals@yahoogroups.com, Olusesan Makinde <sesmak@...> wrote:
>
> Hi Duane and Bill,
>
> Thanks for the comments.
>
> The strings are a concatenation of several other fields that are actually made of numbers but with a leading zero in some cases. That is why the field has to be stored as a string. The concatenation is from a code for states, local government areas, the type of a facility and a unique number per local government. It has about 10 characters. One of my fears is that the way the numbers are generated, once a facility is upgraded, a new ID will have to be issued to the facility which I do not think is a good practice. The records right now is over 35, 000 and may grow slightly subsequently. We anticipate linking this database with others and was wondering if the strings ID will affect performance. From the comments, it may not affect the performance significantly. 
>
> If the performance will not be affected down the line, then I will stop the argument. However, I have to tackle changing the unique ID of a facility when upgraded.
>
> Regards'
>
> Olusesan. 
>
>
> ________________________________
> From: Bill Mosca <wrmosca@...>
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Friday, 2 August 2013, 15:33
> Subject: [MS_AccessPros] Re: Disadvantages of using String as Primary Key
>
>
>
>  
> Olusesan
>
> Strings are slower to index than numbers and are subject to human error more so than an AutoNumber which is not touched by users.
>
> Are these strings internally generated? What is the length? Will that length give you an adequate number of unique identifiers or will you run out in a few years?
>
> According to an article by FMS ( a highly regarded software company):
> (quote)
> Primary Key Should be One Field and Numeric
> The primary key should only be one field and ideally numeric and meaningless. Primary keys define the uniqueness of each record, which can be accomplished efficiently with a single number. The easiest method is to use an AutoNumber field in Access or an Identity column in SQL Server. The primary key is also used in each secondary index for that table, so the smaller the better. Multi-field primary keys and non-numeric text fields are less desirable.
> That said, some tables should use text fields as primary keys because they don't change much and the tables are relatively small. For example, a list of countries or states is a good candidate because there is no need to create a separate number for each country or state.
> Having a meaningless primary key means that the index is stable even when data changes. Otherwise, changes in the primary key have a ripple effect through each secondary index and any other tables bound by referential integrity.
> --(end quote)
>
> Regards,
> Bill Mosca, Founder - MS_Access_Professionals
> http://www.thatlldoit.com
> Microsoft Office Access MVP
> https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E
> My nothing-to-do-with-Access blog
> http://wrmosca.wordpress.com
>
> --- In MS_Access_Professionals@yahoogroups.com, Olusesan Makinde <sesmak@> wrote:
> >
> > Greetings Colleagues,
> >
> > I am working on an evolving project that can grow to encompass several integrated information systems in a country. The unique identifiers have been developed using String/ Text characters and I am trying to convince the project champion to recreate new identifiers which will be numbers. This will come at a cost but it is still quite early in the project and I believe that we can achieve this.
> >
> > I will appreciate if you can feed me on points to convince him for this move or otherwise advise me if this suggestion is unnecessary. .
> >
> > Thanks for the help. 
> >
> > Olusesan Makinde
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
> [Non-text portions of this message have been removed]
>

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (6)

Recent Activity:

• New Members 5

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use • Send us Feedback

.

__,_._,___