Francisco Tapia
fhtapia at gmail.com
Tue Mar 11 07:15:25 CDT 2014
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