|
Tagging lookup tables - how?
Do you do anything to indicate that a table is a lookup table? I've always prefixed them with "lu", but that always seems to raise eyebrows...
Philo
Philo
Saturday, April 10, 2004
Philo, why do you feel the need for distinction in the type of table?
The content of the table is expressed by its name, its role in the database is expressed by its relationships.
Just as meta data in column names is a bad thing, so is overloading the name of ANY object with "useful" information.
Hmmmm ... does this mean you are an ardent fan of "Hungarian" notation?
HeWhoMustBeConfused
Saturday, April 10, 2004
Because lookup tables are an strident exception to "normal" tables - they are generally static, subject to different usage, feed dropdowns and selectors, provide lookup information for tables...
I've had utility code that built stored procedures and lookup code for an entire database. How do you suggest I identify which tables are lookups?
And yes, before VS.Net made everything an object and provided additional identification tools in the IDE, I was a strong supporter of Hungarian notation.
Why is metadata in column names a bad thing? You mean you don't put "ID" in your primary keys?
Philo
Philo
Saturday, April 10, 2004
Philo:
After poking around some, it seems the general consensus regarding lookup tables is to name them in relation to their activity.
i.e.
activity_status and activity_type
I've also seen some people label their lookup tables with the "tlkp" prefix which is even more cryptic. Doesn't seem to be any hard and fast rules though. So long as you're consistent, I don't think it should matter much. And if they're raising eyebrows at your naming conventions, put the heat on them. Ask if they can do better. If they can, great! You just learned a better way. Otherwise, they'll hush up.
Elephant
Saturday, April 10, 2004
No I don't put any prefixes on tables - don't think it is that common.
DJ
Saturday, April 10, 2004
I too never prefix any tables. Just give them meaningful names with regard to what they hold.
Speaking of them being distinct, you never know what the future holds. Even for lookup tables. It may be relatively easy to rename a variable after its role changes, but think about renaming a table in a relatively developed application.
Egor
Saturday, April 10, 2004
I've never felt the need to distinguish lookup tables in this fashion, though I doubt I'd scream if someone wanted to do this.
Naming standards in my experience are a little bit like code-formatting standards. It's important to have clarity and some level of agreement on fundamentals like this, but the specific approach adopted probably isn't as important as having *some* reasonable approach.
John C.
Sunday, April 11, 2004
I use ID in the name of a column only if the natural description of that column is "identifier", or similar word.
Some (contrived) examples:
1. In a table of people identified by Social Security Number, the identifying column is (perhaps) SSN, not ID.
2. In a table of countries identified by ISO-3166 country code, the identifying column is COUNTRY-CODE, or simply CODE.
3. In a table where I choose to use a surrogate key, with no natural assocation with any data type, I would probably use ID as the column name.
I don't really have a problem with your approach to naming tables, it just isn't necessary. It also falls into the common trap of perspective ... the fact that a table is peripheral in your initial usage does not mean that it remains so into the future of the database.
HeWhoMustBeConfused
Sunday, April 11, 2004
Incidentally, Philo, the reference you provided in the topic about variable name spelling some delightful comments about "Hungarian" notation. :)
HeWhoMustBeConfused
Sunday, April 11, 2004
Okay, here's my convention for any table used to contain data for a lookup:
Table name: luStatus
PK: StatusID
Nomenclature: Status
Where "Status" is some invariant predefined list, like "Operational, Out of Commission, Under Repair" etc.
It's a relatively fixed list of values which in the UI is probably populated by a drop-down list or radio buttons. In the data table when you see "StatusID" you know
a) it's a FK
b) it refers to a lookup
c) that lookup is most likely the luStatus table
Yes, that can also be discovered by looking at the reference, but when you see it in code or an SP, it's self-explanatory.
Philo
Philo
Sunday, April 11, 2004
I've used "tbl" to prefix tables and "tlk" to prefix lookup tables. It gets a bit annoying, though, to not be able to quickly jump to an item in a tables list by typing its first letter. Having to type the first four or five definitely undermines the usability of those interfaces.
Sam Livingston-Gray
Sunday, April 11, 2004
I use "tbl" to prefix data tables and "List" to prefix lookup tables. Examples from a medical database I did: tblPatients, tblVisits, ListMedications, ListDeliveryTypes...
Why "List" and not "lst"? I never thought about it much before, but it was probably because "lst" was already taken; it's the Hungarian designation for a ListBox control.
My List tables are generally for filling ComboBoxes and keying to fields in other tables filled from ComboBoxes, and usually have only two fields: primary key and string data. Which, in ListMedications would be named KeyMedication and Medication respectively, while in ListDeliveryTypes they'd be KeyDeliveryType and DeliveryType. And so I don't have to waste much time looking up field and table names while coding.
Kyralessa
Sunday, April 11, 2004
I recently became a big fan of not expressly naming tables or lookup tables. Instead I like the idea of a three letter abbreviation for the table name, which is then used to prefix every variable:
JOS_Joel_On_Software (JOS_uid, JOS_id, JOS_member_name...)
When I'm coding I rarely have to expressly declare my table during a join and the code looks a lot cleaner. Of course a good IDE would help there, but I'm an SQL junkie who prefers to code by hand.
Lou
Sunday, April 11, 2004
Why would you want to do this?
The purpose of naming objects is to distinguish them from other objects of the same name. tblQuestions as opposed to qryQuestions or frmQryquestions (link forms to queries not to tables before anybody asks).
I suppose it might be useful at first glance to be able to see which tables are simple static lists, but I would think that would be offset by the lack of agreement on a standard abbreviation.
Stephen Jones
Sunday, April 11, 2004
I don't use any convention for lookup tables. I just try to give them a name that makes it easy for someone else to understand what their purpose is. I don't follow some strict guideline; I just call them what makes sense.
The problem with home-grown naming conventions is that while they may make perfect sense to the author, other programmers might not have a clue as to what they mean. In that sense, they become an obstacle to someone trying to get up to speed with your code.
Mark Hoffman
Sunday, April 11, 2004
At my current company, we categorize these tables as "reference data," and prefix our tables with REF_. So we have REF_COUNTY, REF_STATE, REF_COUNTRY, etc.
The tricky part is deciding what truly counts as reference data. Generally reference data would include content which is used as read-only during most usage scenarios. But reference data does need to be updated from time to tim. (Hell, there are always new countries popping up in the world, and others disappearing.) As another example, user accounts generally aren't edited very often. Does that make it reference data?
Conclusion: the deliniation is not black and white, but really many shades of gray.
But if that's the case, how do you set the line between which tables are prefixed with "REF" or "LU", and which are not?
Ryan
Sunday, April 11, 2004
I've had lengthy arguments about this topic with coworkers.. They seemed to be in favour of naming something a lookup, while I personally prefer to make each table name reflect it's content, rather than it's purpose.
The way I see it is this: if you have an employee table, you don't care if it's being used for a lookup or as a datafeed or for modifications; you just want the name to describe the sort of information it's going to have. So even if employee IDs function as a lookup, calling it lu_employee_id or something would just mean you're second guessing whoever uses the schema and telling them, look, look.. here be lookup tables :)
Having said that, I do occasionally have hashtables function as lookups in my code and I name the variables as "lookup"_ something. Just not database tables, since I feel they can and should be used anyway you want, not necessarily named according to it's common or intended use.
deja vu
Sunday, April 11, 2004
I'm trying to examine the grey areas here - to me, almost all the time a table is a lookup or it's not. If a field has a fairly limited, established list of values that can go in the field, it's a lookup. Lookups indicate descriptors or "types" of things - priority, color, status, etc. Things where the business organization already has a fairly fixed set of values that are "legal" for the field.
So you normalize those values into a lookup table and put the index or code into the field.
Maybe couching it as a design question:
An application needs a field where you indicate color. There are sixteen available colors. Colors may be added or removed, but not very often.
How do you design that in the database?
Philo
Philo
Sunday, April 11, 2004
Let's take the color thing.
The first question is is color an attribute or an entity?
If you are a paint factory then color is going to be an entity. Equally for most uses in a database, because instead of talking about 'red' you are going to be talking about a particular chemical formula of paint.
But what about those parking tickets from Philadelphia that tell you the color of the car you got a ticket on. Here it doesn't matter too much. Maybe you want to let the attendant describe the color the way he wants (do you really want him agonzing over whether a carmine colored car was red or pink?).
Incidentally there is no reason why look-up tables should only be rarely updated. If you allow the user to add to the list in a combo box, you can allow of frequent updates.
This discussion incidentally has little to do with whether to prefix lookup tables separately from other tables in Hungarian. I can see the advantage of it, since even in small databases maybe 90% of the tables are going to be lookup tables so it would be useful distinguish the others at a glance. But the problem of there being no agreed standard probably pushes the scale the other way.
Stephen Jones
Sunday, April 11, 2004
"But what about those parking tickets from Philadelphia that tell you the color of the car you got a ticket on. Here it doesn't matter too much."
*************
"Hey Harry, what color would you call this car?"
"Beige"
Color: Beche
"Okay Sarge, let's see if we can find our burglar - pull a list of all the brown, tan, and beige cars that got parking tickets on that block on that day."
"Sorry, Fred, nothing showed up"
I suspect this is closer to the truth than either of us would've guessed. My county recently added a mandatory field on auto registration forms - current color, with a list of two-letter color codes (choose the closest).
One of the reasons for providing picklists is to standardize input for reporting purposes...
Philo
Philo
Sunday, April 11, 2004
Your desire to control/validate the data is good. It is your naming convention that is of little value.
I've been thinking about this discussion in terms of "could this benefit me?". So far I can see no practical benefit, for me or any user of my schemas and code, in prefixing a table with some arbitrary "table type".
I can't think of any database design or modelling methodology that supports "types" of table, which is also an indicator that this is of no benefit.
It seems to me that you are taking a short-sighted view of the database design. Perhaps you are used to working with relatively small personal or workgroup applications, from the perspective of a coder rather than a modeller. A data modeller would think about the problem in terms of the data (ie, Colour, or State), not about its usage in one particular application.
HeWhoMustBeConfused
Sunday, April 11, 2004
"A data modeller would think about the problem in terms of the data (ie, Colour, or State), not about its usage in one particular application."
A good point. For example, when the parking ticket application gets hooked into an auto body shop application (where "color" is a line of business data point) then my choice of making "color" a lookup would be a bad one.
Or... we could recognize the likelihood of this happening and realize that for an entire police division "car color" is always going to be a simple explicit list (now codified by the county).
I have always found great utility in tagging lookup tables. My tags have always been met with appreciation by the coders I've handed off to. I've met other coders that do the same thing (actually appending "Code" seems to be the prevailing practice), so the idea must have some merit at some level.
Philo
Philo
Monday, April 12, 2004
 Recent Topics
Fog Creek Home
| 
