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