|
I have drafted a rough design of a new database for a job application web site. I have a quintillion lookup tables with no more than a primary key and a string column, e.g. Province, with Id int and Province nvarchar(50). In this case, Province seems a natural and logical name for the description field, but in others the description is more vague, and better named as simple Description.
Now, if I use the name Description for all description columns in the DB I gain the opportunity to use more generalized T-SQL and C# code to deal with lookup tables, but I lose in semantics, especially where e.g. a WorkCategory lookup table grows to represent a business entity and not just a value domain; then Description would become less meaningful among more than just two columns.
I understand there is no black and white, clear cut answer to this question, but I am only asking for suggestions and input from people who may have faced similar dilemmas before.
|
|
|
|
|
Do NOT go down the path of description, do precisely what you are doing, name your fields intelligently and when nothing comes to mind only then use description. It'll save you a crap load of refactoring in the future.
I use a code generator which reduces the cost of refactoring and I still get irritated when I do this.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: I use a code generator which reduces the cost of refactoring
So do I, being the T4 based code generated by the EF Wizard or Power Tools, but I'm interested in what causes the mountains of refactoring you are warning me against. Is it, as I suspect, when you need to rename Description to something more specific?
|
|
|
|
|
Brady Kelly wrote: Is it, as I suspect, when you need to rename Description to something more specific?
You suspect correctly, although I have to admit my problem is caused more by using views and never allowing Select * From ViewName
But I find it immensely irritating to get to the reporting phase of an app only to wish I had used something more descriptive than description. And having to rename them in a query when you have 5 description fields hanging off a Fact table view!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I see. I'm currently on a big renaming exercise, and will try and reduce my Description usage. Luckily that field isn't used in any FK's.
|
|
|
|
|
I mostly agree with Mycroft; the odds definitely favour what he recommends.
But as he and I seem to work on very different types of systems, I generally never display the column headings and in many cases ( Code , Meaning ) will suffice. Yet in many others a broader range of text values is required, so more descriptive names are suitable; one such I have here has ( ID , Tag , Description ) -- the Tag is used for a Dictionary and the Description is used in a ComboBox. There's another example here that pre-dates me wherein four different text values are required ( ID , Name , Description , LongName , ShortName ).
Oh, I just remembered another one that I created a week or two ago ( Code , Meaning , Description ) -- the Description is intended only as documentation for developers looking at the table; this table contains flags to be used in bitmaps.
What matters more is using names like "Name" and "Description" which are or may be reserved words -- you should probably avoid that, and by being in the habit of using more descriptive names you may never need to worry about that.
Consider each table on its own, don't try to enforce a single rule on all of them, but err on the side of caution. As I hope I showed here, habits are hard to break, so please try not to follow my path. 
(Now I'm picturing Jacob Marley...)
|
|
|
|
|
PIEBALDconsult wrote: Description is intended only as documentation for developers looking at the table
That kind of info I am storing in a separate data dictionary, which has not yet moved beyond an Excel doc, but I am seriously considering writing something to use SQL Server's Extended Properties for data dictionary data.
PIEBALDconsult wrote: What matters more is using names like "Name" and "Description" which are or may be reserved words -- you should probably avoid that, and by being in the habit of using more descriptive names you may never need to worry about that.
Yes, and even names that aren't reserved but are prickly, like having a table called ReferenceType with Id and Type columns. I have avoided all of those things.
|
|
|
|
|
Brady Kelly wrote: SQL Server's Extended Properties
Can those be applied to individual rows? Even if so, would a developer look there when perusing the table?
|
|
|
|
|
Duh, no.  I had crossed channels in my mind when I write that. 
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
