[dba-SQLServer] PK/ANPK Debate

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
>
>