[dba-SQLServer] PK/ANPK Debate

Mark Breen marklbreen at gmail.com
Tue Mar 11 11:17:27 CDT 2014


Hi Francisco

thanks for the reply.

Just on question, not sure what you mean when you said

>As for anpk, why should coding up a lookup save me time as a programmer in
my queries?

Hope you are well

Mark



On 11 March 2014 12:15, Francisco Tapia <fhtapia at gmail.com> wrote:

> Well since this has started off so well I'll throw my 2 cents..
>
>
>
> I've seen this design within SAP.  It makes for finding related lookups
> pretty easy but they do seperate their lookups so you don't have unrelated
> data together.  Case in point they have a table simply for damage codes but
> the if maybe for damage codes in a service call or it might be from
> manufacturing, medical etc...
>
>
> As for anpk, why should coding up a lookup save me time as a programmer in
> my queries?
>
>
>
>
> My idea has always been optimize as much for data efficiency and speed! I
> know today's computers can run circles around yesterday's technology, but
> that's no reason for programmers to get sloppy with coding.  If the
> argument really is, why complicate my query since machines today are so
> fast? Then why normalize at all?  Just build it flat no joins no fuss
> problem solved no wierd ids in your table...
>
>
>
>
> It's hard to justify...
> --
> Sent from Mailbox for iPhone
>
> On Tue, Mar 11, 2014 at 2:50 AM, Mark Breen <marklbreen at gmail.com> wrote:
>
> > Hello Arthur,
> > About three years ago, I started to use a special table named Lookups.
> > in that table I store all my system lookups in one table.
> > I know it is not true referential integrity but it makes it extremely
> easy
> > to add a new set of lookup values.  My table structure is
> > Id, ItemName (nvarchar(255), ItemValue Nvarchar(max)
> > In there I might store a list of Colours, a list of status codes and a
> list
> > of priorities, I may sometimes have ten or twenty different sets of
> lookup
> > values.
> > I name the foreign keys as follows
> > Item name: Grades
> > Foreign Key GradeLVId
> > Item name: Colours
> > Foreign ColourLVId
> > When I app needs a new lookup table to store Shapes, I simply insert a
> few
> > more records in the lookup table as follows
> > Shapes, Square
> > Shapes, Triangle,
> > Shapes, heptakaidecagon
> > I should add that I also have only have one screen to maintain all these
> > different lookup items.
> > *Summary*
> > Since I switched to this structure for lookup values, I truly estimate
> that
> > I have saved a lot of times and added flexibility to my applications.
> > On 10 March 2014 12:43, Arthur Fuller <fuller.artful at gmail.com> wrote:
> >> To start off the week, I thought that I would re-open the PK/ANPK
> debate,
> >> limiting the discussion to a specific group of tables: lookup tables.
> >> Consider this example:
> >>
> >> SCENARIO 1:
> >> Table: Volunteers
> >> PK: VolunteerID (autonumber)
> >> Columns: Name, Address, etc.
> >>
> >> Table: Languages
> >> PK: LanguageID (autonumber)
> >> Columns: Language(varchar(20)
> >>
> >> Associative Table: VolunteerLanguages
> >> PK: VolunteerID, LanguageID
> >> Columns: VolunteerID, LanguageID, and perhaps Profiency
> >>
> >> SCENARIO 2:
> >> Table: Volunteers
> >> PK: VolunteerID (autonumber)
> >> Columns: Name, Address, etc.
> >>
> >> Table: Languages
> >> PK: Language
> >> Columns: Language(varchar(20)
> >>
> >> Associative Table: VolunteerLanguages
> >> PK: VolunteerID, Language
> >> Columns: VolunteerID(int), Language(varchar(20), and perhaps Profiency
> >>
> >> Both scenarios exist to allow the recording of multi-lingual Volunteers.
> >> The knee-jerk choice is to go with the first scenario, but I am calling
> >> this into question, on the grounds that it complicates queries,
> comboBoxes
> >> etc. unnecessarily. The name of the language is sufficient as a PK in
> >> itself. No one wants duplicate occurrences of any language name in this
> >> table.
> >>
> >> In the associative table, the second Scenario takes up a little more
> space.
> >> But surely in this age when laptops have multi-TB hard disks, this is
> of no
> >> real concern. So why complicate queries, combos, listboxes etc. with a
> >> pointless lookup and additional Join?
> >>
> >> The same argument could be applied to many lookup tables, for example
> >> Countries? Why bother with an ANPK when the official ISO abbreviations
> >> exist, are easily obtained, and are universally recognized? Perhaps the
> >> argument is even stronger here, since the ISO codes are char(3)?
> >>
> >> In case it isn't clear, I am not arguing for this design in the common
> >> Customer+Orders+OrderItems situation, but only in the subset of tables
> >> where there are a small number of rows whose text values must be
> unique. In
> >> the past, I have designed this subset according to the first scenario,
> >> Scenario 1 demands either a unique index on the text column or logic in
> the
> >> front end to achieve the same thing. In short, I no longer see the
> point.
> >>
> >> --
> >> Arthur
> >> _______________________________________________
> >> dba-SQLServer mailing list
> >> dba-SQLServer at databaseadvisors.com
> >> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> >> http://www.databaseadvisors.com
> >>
> >>
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


More information about the dba-SQLServer mailing list