Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1617181920

Lookup Table Madness Expand / Collapse
Author
Message
Posted Monday, March 26, 2007 8:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 3, 2014 11:55 AM
Points: 24, Visits: 194

I expect articles to be somewhat subjective and enjoyed the author's opinions. I prefer not to mix lookup codes strictly from the FK's perspective.

The author mentioned a dislike of the term "lookup" and other similar terms. I am curious what the author's opinion and other reader's opinions are of the technically correct term, as well as examples of good "lookup" table names.

For example, if I have a Cars table and the ColorCode column looks up to another table with the code and description, what would you name the table? I typically go with the much disparaged table qualifier, e.g. ddColor, where dd means "DataDictionary" so that all my lookup tables are grouped together.

I always experience inner torment when coming up with good table names and was just wondering what others do ...

Post #353850
Posted Monday, March 26, 2007 8:27 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:32 PM
Points: 15,517, Visits: 27,895

Allow me to say right up front, I don't like this standard. I inherited it. I fought against it. I lost.

We name our simple lookup tables 'C_WhatEver' and the complicated ones 'R_Whatever.' 'C' stands for Code. 'R' stands for Rule. A simple lookup table, or code table, is defined as an ID, Code, Description set. No other columns such as relationships to other code tables, etc. Rules tables are just code tables that have all the other stuff in them, self-referential constraints, hierarchy relationships with other tables, etc.

Stupid? Yes. A pain the butt? Yes. Not my choice? Absolutely.

Hey we also have to use abbreviations from a set list including, my personal favoriate: Deductible = ddltbl. How do we shorten a word and a letter that isn't there or implied? I don't know, but that's how I'm expected to type deductible in table & column names.



----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #353854
Posted Monday, March 26, 2007 8:39 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:16 AM
Points: 1,035, Visits: 410

I have yet to hear a truly useful definition for Domain\Lookup\DataDictionary\etc... tables.  Why is it deemed necessary to treat them differently than any other tables?  And by extension, why is it deemed necessary to name them any differently?

I would call that table PaintColor or maybe CarColor, or even just Color just depending on what best conveys the meaning of the data that is actually held there.




/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek



*****************/
Post #353865
Posted Monday, March 26, 2007 9:09 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:32 PM
Points: 15,517, Visits: 27,895

The only answer I have for you is the one that I'm dealing with at this company. They want the "code" tables to be the same across all applications and all environments. So we have 10 or 15 different applications, not counting the EWH & Datamarts, in at least four environments, Dev, QA, Staging & Production(not counting Performance Test or Training environments of the various others), and these tables, unlike all the other tables in the system, are the same everywhere. So any place you go, CoverageId = 52 gets you the same coverage. You may get a different value for Policy on any given system because the data was generated by test plans or by actual users or by developers, but those "code" tables are managed across the enterprise.

I could still do that calling it Coverage rather than C_Coverage, but I was told I had to designate it that way. There was a fight. I lost.



----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #353883
Posted Monday, March 26, 2007 9:42 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:16 AM
Points: 1,035, Visits: 410

It makes perfect sense to have a single set of data available across the enterprise and things like Color, or EmployeeStatus etc... are very likely candidates for this, but in many cases, highly transactional data like PurchaseOrder would be managed on an enterprise wide manner too. 

In Data Warehousing circles they like to call these tables Dimensions.  And again, I don't like the idea of naming tables like dim_Customer and f_Sales for dimension and fact tables respectively.

It all depends on the business and the design of the system(s) involved.  But I haven't been able to define, nor have I ever heard anyone else define, a useful generalized definition for "lookup tables". 




/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek



*****************/
Post #353895
Posted Monday, March 26, 2007 10:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:01 PM
Points: 266, Visits: 2,567
I have experimented with distinguishing 'code'/lookup tables from other tables in two different ways: 1) adding text at the beginning of the table name and 2) adding text at the end. The system I liked best was to putting the designation at the end. I inherited a system where lookup tables were 'type' tables and had the text 'Typ' appended to the end. In the example above, it would be CarColorTyp. I've continued that system, though I might have used some other text like 'dd' if I were starting from scratch now.

The nice thing about a suffix as opposed to prefix text is that related tables tend to be next to each other. For example, if I have tables like Car and CarColorTyp and CarEngineTyp, etc, all my 'car' tables would be next to each other in a list. I don't need all my type tables grouped together.

But this begs the question of why make the distinction at all. I could just have Car, CarColor and CarEngine as table names. Here's why I add the suffix: I find the suffix text useful from a human perspective when trying to get the big picture of understanding a database. A distinguished table name very quickly helps me to identify what is going on with that table. I quickly get the function at a glance. It doesn't bother me in the least that there is a subjective/human element to deciding when a table is a 'type'/code/lookup table and when it is a 'main' table. Having the distinction is helpful to me.

Since all my 'typ' tables (with additions when needed) have the exact same columns: code, name, abbreviation and sort order (ex for a PhoneTyp table: phnCd, phnNm, phnAbbr, phnSortOrd), one could easily argue that the 'Typ' suffix is simply not needed. I could look at the structure of the table to understand it. But again, from a human perspective, I get a much quicker understanding of the table by being able to deduce it's purpose from just it's name. I'm looking at a list of table names more often than I'm looking at a data model.

I will say one nice thing about having 'Typ' as the suffix/text rather than something like 'dd' for data dictionary or 'lu' for lookup. The 'typ' text often seems to create meaningful names in an of itself. Take my Phone table example. I can have a Phone table and a PhoneTyp table. I instantly know two things about the PhoneTyp table without having to know anything at all about it's columns (I won't dare call them fields after reading some other comments in other articles ). I know that PhoneTyp is a table that gives me phone types. I don't have to name it say 'PhoneTypLU'. And I know that this table is nothing more than a simple list of phone types. When I need that simple list, I know right where to go. When I need to get to main data, I know I can skip that table.

I think of another reason to have a suffix or prefix, but I can't think of a great specific example right now. Here's the idea anyway: if the lookup tables all have the same text, then it would be much easier to make mass changes to those tables using code. Here is an example of what I am trying to say. Suppose you have a WHOLE bunch of code/type tables configured as I explained above with the same basic 4 columns (with some additional columns when needed). Then your business rules change and you need to add a new column to all your code tables. For example, maybe you need to add a 'Stop Date' column. If all the tables have a unique beginning or ending text, then you can quickly write some code to loop through the tables and add the new column to just the tables you care about. It could save you a lot of work. Admittedly I don't know how often this type of need would arise. I haven't needed it yet--especially since I use ERwin to create my databases. So, changing something like field size for all the 'abbreviation' columns would be easy.
Post #353900
Posted Monday, March 26, 2007 10:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:01 PM
Points: 266, Visits: 2,567
I want to reply to the note posted above:
"But I haven't been able to define, nor have I ever heard anyone else define, a useful generalized definition for "lookup tables"."

For me it is like pornography: You may not be able to define it, but everyone knows what it is. And since I find the distinction of a lookup table, like the distinction of 'pornography' (vs art?), to be helpful, I don't mind that I don't have a definition that everyone likes. It just works for me/makes me happy, makes me more productive, and in my opinion, makes my database designs more user-friendly for anyone else who might come in some day and have to understand and manipulate that database.

Not that I don't understand the above point too. I'm just offering a response.
Post #353916
Posted Tuesday, March 27, 2007 8:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 3, 2014 11:55 AM
Points: 24, Visits: 194

I appreciate everyone's input. I find myself involved in more and more data exchanges, and translating another entity's "number to string" relationships between tables is always one of the first items addressed. It seems some "Technically Correct" term of the day is in order to aid in discussions when categorizing those tables that hold nothing more than support data for the tables that contain the meat of the system. Thanks again, enjoyed the different perspectives.

Post #354194
Posted Thursday, October 18, 2007 10:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, December 7, 2013 9:38 AM
Points: 2, Visits: 25
Completely Agree, there is a time and place for all designs and no one way to create an architecture. Arguments can be made about triggers, constraints, defaults, keys and lookup data sets. Nothing irritates me more than a close minded developers who believe school taught him everything he will ever need to know and experience is outdated and so the only way to create is the way their education taught them. I agree the article made good points on design consideration but was far to one sided to be valid.
Post #412304
Posted Friday, November 9, 2007 10:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, May 4, 2008 6:20 AM
Points: 10, Visits: 22
At the risk of firing up this age old debate. I would like to propose a different perspective on these arguements. I suggest this because there are some like myself who listen to both sides and can get stuck in gridlock. My perpective is as both a programmer and business owner.
I don't care who gets stuck with the extra work. My DBA, my designer, or my programmer. The work needs to get done. When stuck with two competing concepts, I think the answer lies in cost.

Tell me, which approach can I afford to implement?



Kindest Regards,

Frederick Goodrum
Post #420619
« Prev Topic | Next Topic »

Add to briefcase «««1617181920

Permissions Expand / Collapse