Striving to be the person my dog thinks I am.

I admit to be fairly ignorant of the Microsoft’s Live strategy, but being preoccupied with other things at the moment, I haven’t spent much time trying to get educated about it.  But in the weekly email regarding new Microsoft downloads, there was specific mention of Windows Live Writer 2008, coupled with a  promise that I could edit WordPress posts with this client-based tool.   Well, so far, so good — this post was composed, edited and published using WLW. 

Subsequent to downloading, a wizard guided me through the installation and configuration for use with WordPress, and within about five to ten minutes, I was oriented in the new environment.  The WLW user interface is similar enough to the WordPress interface to make it easy to find the functionality found in the native web-based user interface.  So first impressions are pretty positive.  A few other random thoughts and observations, so far;

1. When I attempted to publish my first post with WLW, I couldn’t get the new post to appear.  What I subsequently discovered, however, is that the default publication date for posts was way back in 1999.  The post was actually working, except the new post was being inserted before all the other posts, as opposed to after (at the top) as expected.  Once I had set the publication date manually at the bottom of the user interface, everything worked as expect.
2. The main menu, just below the title bar, keeps getting corrupted, with portions of the menu appearing in duplicate.  Minimizing and maximizing the application makes the problem go away, but it re-appears periodically.  I have a sneaking suspicion this is related to the video driver for the laptop that I use — it has exhibited other, similar problems with other applications.
3. It seems as though sometimes you have to click the Publish button a couple of times to actually get the post to appear.  This may be a question of not having saved the draft, or some such thing, but I think it’s also possible this is a bug.  Not a big deal — just keep clicking on Publish until the post appears.
4. I attempted to install WLW on my work PC, and ran into problems that I think are sourced at the corporate firewall.  The installation hangs up about half-way through.  Attempting to install on a second PC — behind the same firewall — provided the same result.  The firewall is about the only thing the two machines have in common.   I have no idea why this would be a problem, but I’m assuming it’s a problem with the installed WLW software attempting to make contact with the Windows Live website, but that’s just a hunch on my part.
5. As noted above, this blog is implemented with WordPress, and while I’m thoroughly impressed with the quality of the WordPress software (not to mention the price), I did find the relatively small scrolling region used to edit posts a little tedious.  One of the prime benefits of WLW is that get a lot more real estate for editing.  Common tasks such as inserting tables, pictures and other items is considerably streamlined.
6. There is a Web Preview mode that integrates the post that is being edited — but isn’t published yet — with the content that is already published.  This helps visualize the new material in the context of the existing material.  Not essential, by any means, but useful in getting things just right before taking the new material public.
7. I started this post with a comment about my admittedly limited knowledge of the Windows Live strategy, but using WLW is providing me with a little insight, I think.  For example, selecting Help from the main menu launches you directly into the Windows Live Help site.  So, I think it’s reasonable to guess that the Live-branded applications — even the ones downloaded and installed on your PC — are going be tightly integrated with the Live web site.  I may well be catching up to the rest of the world’s understanding of this subject, but if you didn’t know either, well, now we both do.

Based on a fairly positive experience so far, I will continue to use WLW, and comment on it in future posts.  Until then, thank you very much for reading, and please let me know what you think. 

When attempting to track down problems in the database, a very useful thing to know is the date and time the data was first created, and when it was most recently modified.  As part of a standardized approach to database design, I have gotten into the habit of adding two columns to every table definition; createUdt and modifyUdt that provide this capability.

What’s In a Name?  If you’ve read the post on column naming standards, you will recognize the Base Name udt.  This means the column reflects UTC time, not local time.  This is a nod toward internationalization, which I think every application should take into consideration, particularly in the early stages of design and implementation.  The prefixes create and modify are self-explanatory, and are required, of course, to differentiate between the two types of dates in a given table definition.  Finally, the two dates have equal status and value, and therefore they both have prefixes.

NULL and DEFAULT  createUdt and modifyUdt should be specified as NOT NULL, because they always need to be populated.  Also, the DEFAULT for both should be specifed as GETUTCDATE, so that they get the right date and time when the record is created. 

Keeping Them Right  If these columns are going to be useful, they should never be directly manipulated by your application, or a tool like SQL Server Management Studio.  You have to have absolute faith that they really do reflect the date and time of creation or modification, and not somebody’s interpretation of these facts.  To ensure this, I use a trigger (shown below) that automatically corrects these values in the event that they are directly changed in some way.  This is slightly different in either case; createUdt gets set back to its original value, whereas modifyUdt, is always set to the current system date and time. 

CREATE TRIGGER DogTrg
ON Dog
FOR UPDATE
AS
BEGIN

	UPDATE
		Dog
	SET
		Dog.modifyUdt = GETUTCDATE()
	FROM
		Dog AS Dog INNER JOIN
		INSERTED AS INSERTED ON INSERTED.uid = Dog.uid

	UPDATE
		Dog
	SET
		Dog.createUdt = DELETED.createUdt
	FROM
		Dog AS Dog INNER JOIN
		DELETED AS DELETED ON DELETED.uid = Dog.uid
	WHERE
		Dog.createUdt != DELETED.createUdt

END

Of course, if you intend to use this trigger in your own application, you will have to change the table name.  Also, a really determined user  can always delete or disable the trigger, but let’s assume that it keeps the honest people honest, 99% of the time, and that you have set up rights in such a way that only person with a need to see the trigger code, can.   With a trigger such as this set up for each table, you can then entirely forget about keeping these columns updated — it’s entirely automatic.

It always surprised me that Microsoft did not have these capability bundled right out of the box — I can remember using databases in the dim and distant that had this data available as a standard feature of every table definition.  Heck, maybe they do, and I just haven’t found it yet. 

Also, In addition to date and time, it is also useful to keep track of who modified the data.  That will be covered off in a future post, where I’ll talk about mapping users to sessions, and then mapping the session to collections of updates.  In the interim, thank you very much for reading, and let me know what you think.

I’ve found Internet Explorer can be used a fairly serviceable RSS feed reader.  It has some limitations but it’s a convenient choice that will get your feet wet with RSS feeds without having to hunt down and install new software.  The following is a simple example of how to set IE up to read an RSS feed.

This example is not intended to be a comprehensive review of all of the RSS-related features of IE, and assumes that you already have some idea of the general nature of RSS.  

With that in mind, the first thing you need is a web page that has some sort of embedded RSS feed.  The RSS Feed button (the ‘chiclet’) indicates the presence (or absence) of this.  If it’s greyed out, at least superficially it means there is no RSS feed associated with the page.  This is illustrated in the example below.  In some cases, the button will be greyed out, but will appear somewhere else on the web page.

Let’s assume for this simple tutorial that the chiclet is enabled, as it is on the The New Yorker web page shown below.  When you visit their page or any page with an embedded RSS feed, the chiclet turns a jaunty orange colour, as show in the screen shot below.

If you click on the RSS Feed button, you will get the default RSS page, shown below.  There will be a link embedded on the page that invites you to subscribe to the feed, as well as showing you the current stories making up the feed.

Click on this link, as shown above, and a dialogue box will appear that will ask you to confirm that you want to subscribe.  There are a couple of options, such as the name you want to associate with the feed in your Favorites and the folder in which you want to place it.  For the time being, leave these at their default settings — you can go back and change them later — and simply click the Subscribe button as shown below.

The next screen simply confirms that you have successfully subscribed to the feed.   This seems a bit unnecessary to me, but I guess it doesn’t hurt to see this either.   That’s it!  If you got this far, then you have successfully subscribed to The New Yorker (or whatever) RSS feed.

However, that’s only half the story.  The next thing you will want to do is to open the pane in IE that allows you to view and check the status of your feeds.  To do this, click the little gold Favorites star, as shown in the screen shot below, and the Favorites pane will appear.  If necessary, click the Feeds button at the top of the Favorites pane.  You should see the newly established feed for The New Yorker.  What’s more it should be in bold.  This means that there are new items in the feed that are ready to be viewed.

If you click the feed, you will see new articles that have appeared in the feed since you last looked at it. Once you click away from this feed, it will go from bold to normal type. When the feed is refreshed — the schedule for which is established below — and there are new articles, the feed name will once again appear in bold.

I’ve found it handy to keep the Favorites pane open while browsing other web pages.  To pin it in place, you click the small icon at the top right-hand corner of the Favorites pane.

By default, the feed will have been establish with properties that are generally acceptable.  The most important setting is how often the feed gets refreshed automatically.  By default, it is once every 24 hours, and for feeds for magazines, this is more than adequate.  However, some feeds are updated so frequently that you may want to up the frequency with which you update the feeds.  To do this, right click on the feed as shown below.

The properties page for the feed will appear, and you can then change the frequency of updates by clicking on the settings button.
 
That’s it.  You’re now up and running with IE as RSS feed reader.  Thank you very much for reading, and let me know what you think.

Previously, I discussed standards as they relate to naming tables in SQL Server 2005. This time;

Column Naming Rules

Base Names  The concepts modeled with database tables tend to be described in similar terms. Many tables contain a column with descriptive information, a column with a name or a column with amounts, for example. For these, it’s useful to have a list of standardized column names used repeatedly. These are referred to as Column Base Names, or just Base Names. The most important concept is that the table in which the Base Name is found provides a context.  If you are using one of the Base Names by itself, as will often be the case, it is inferred that the thing being described with the dsc Base Name or named with the nm Base Name is the idea being modeled in the table in which these columns are found.  More to the point, it’s not necessary to repeat some portion of the table name itself within the column name. For example, Whale.nm (think ‘whale name‘) reads a lot better than Whale.whaleNm (think ‘whale whale name‘). Nothing against whales - in fact, some of my best friends are whales — but the second whale in the identification of this column adds nothing in terms of clarity.

Elimination of Redundancy  Of course, there are many situations where there is a requirement for multiple instances of a given Base Name within a table. In the table Dog, for example, it’s reasonable to expect a nm column; Dog.nm. However, let’s assume the application design evolves and the dog’s formal show name needs to be captured. A column with the qualifying prefix show would be added to the column name; hence showNm. As a result, the Dog table contains Dog.nm and Dog.showNm. It’s also tempting to rename the existing nm column to nickNm or familiarNm. However, the two names don’t have equal status. For example, if you take your champion Weimaraner out for a scrape, you are not likely to say; “Not there, Hampson Whiskeyman of Foucalt!“, but rather, the less pretentious and slightly less definitive “Not there, Spot!” The latter is the name you regularly use, therefore it makes sense to use the Base Name for the familiar name of the dog in question. It’s sort of like a ‘default’ name.  There are situations, however, where names have absolutely equal status; Person.firstNm and Person.lastNm is a classic example. Unless you are Oprah, Cher or Madonna, you need both names (at least) to fully identify a given instance of Person. In this case, it’s important to eliminate the Base Name from the table. This reflects the fact that in this kind of situation, all nm columns have equal status. The presence — or absence — of the Base Name implicitly tells you something about the structure of the data being modeled.

Capitalization The first letter in the column name is lowercase, which is the opposite of table names described previously. If tables are the database analogue of class, then columns are the database analogue of class properties. I come from the school of thinking that the first letter of a property name is lowercase, to distinguish them from class names. So, Person.lastNm, never Person.LastNm. I believe this is in conflict with Microsoft’s approach to naming of properties, but first-letter-lowercase is my story, and I’m sticking to it. Similar to tables names, use camel case to distinguish the various parts of the column name, never underscores, spaces or some other character.

Abbreviations As with all names for database objects, do not abbreviate for all but the most self-evident ideas. The Base Names described above are an obvious exception to this rule. Due to their very frequent use, however Column Base Names become part of the visual furniture. You will see dsc, but in your mind, you will hear the verbose ‘description‘. In all other cases, no abbrevations — the argument for which was spelled out in the rules around naming tables.

Alphabetical Order Columns always appear in alphabetical order. The trick comes when modifying an existing table structure to add a new column name; I use SQL Server Management Studio to make table modifications, and it permits insertion of the column where it needs to go in the alphabetical sequence. You can also use the ALTER TABLE statement, but I’m fairly sure that you get no choice as to the sequence of the columns.  Alphabetical order is useful if you have a particularly long collection of columns in a table. You can glance at the structure and determine if a particular column exists or not. The significant exception to this rule is both primary and foreign keys, to be dealt with in a future post.

Summary Example

The following continues the example of the Dog table that I started above, and it applies all of the above rules;

Dog.birthDt
Dog.dsc
Dog.firstShowDt
Dog.heightAmt
Dog.lbl
Dog.mostRecentShowDt
Dog.nm
Dog.showNm
Dog.weightAmt

Keep in mind that there are a few other rules that I have not yet covered that will impact the table structure, but I will add those items to this basic table structure as and when they are covered in future posts.

Other Thoughts

When JOINing two tables that use the standards described above, you will have to get used to using the fully-qualified column name. This is because there is a pretty good likelihood your JOIN will expose two column names that are the same. For example;

SELECT DISTINCT
     nm
FROM
     Job INNER JOIN
     Role ON Role.id_Person = Person.id

will likely fail, because there is a pretty good chance that both Job and Role will have a nm column associated with them. Therefore, the syntax must read;

SELECT DISTINCT
     Job.nm
     ,Role.nm
FROM
     Job INNER JOIN
     Role On Role.id_Person = Person.id

At first, this would appear to be a significant drawback, but I believe in using fully-qualified columns names, anyway, for the purposes of making the SQL statement more readable. The example above also provides a sneak peak at standards for primary and foreign keys, but these will be covered in more detail in a future post.

Conclusion

There are quite a few other rules that I apply to the naming of columns in database tables, but I will leave those idea for a future post. Thank you very much for reading, and let me know what you think!

This is the first part in a series of posts on SQL Server 2005 object naming standards. In the previously posted Introduction, I laid out the case for the value of using these naming standards in general, and in this post, I talk specifically about table names.

Table Naming Rules

Singular  Table names are always singular;  Goose rather Geese, Crow as opposed to Crows, and Whale, not Whales.  Simple enough.  However, by saying singular, that does not preclude collective terms.  So Gaggle, Murder and Pod would all be perfectly good table names, whereas Gaggles, Murders and Pods would not.  To understand why, it helps to think about the columns that will eventually populate this table, and in particular, how the combination of table name and column name will sound when read or discussed.  I would argue that Crow.nm (think ‘crow name’) and Crow.dsc (’crow description’), make more sense than the plural equivalents (’crows name’ and ‘crows description’).  Or, stated another way, the name and description columns relate to a particular crow in one row of the table, not the entire population of crows in all the rows of the table.

One Noun   I strive for one noun that best describes the collection of data the table contains — the simpler the name, the more self-evident the table and its content becomes.  Car would be a good table name, whereas CarFender would not.  There can’t be much confusion as to what the Car table is likely to contain.  CarFender might contain information about cars, or fenders or maybe somebody who ends carfs (whatever they are), or possibly all three.  Just looking at the latter makes me want to split it into separate tables with some sort of relationship with each other.  If you’re really stuck for good, unique name, try plugging some possible names into a thesaurus, and see what synonyms exist.  You may be pleasantly surprised at what you find.  There is one major exception to the One Noun rule, described in Associative Tables, below.

Capitalize First Letter  The first letter of the table name is always capitalized.  I took a queue from object-oriented practices in this regard; tables seems like the database analogue of class, and the records within the table seem like instances of the class.  By convention, the first letter of a class name is capitalized, and hence, the database table name should follow that convention.  If it’s simply not possible to follow the One Noun rule noted immediately above, then use the so-called camel case to distinguish the words within the name.  Don’t resort to underscores, or worse yet, spaces in tables names to separate words — it’s just a waste of screen real estate, and does nothing that camel case doesn’t accomplish just as well.

Elimination of Redundancy   If you really work at applying the One Noun rule, it’s just a matter of time before there’s a situation where the same, simple noun describes at least two completely different ideas.  Point is a good example; it could represent a physical location on the surface of the earth, or a single bullet on a presentation slide, or maybe the sharp end of a pencil.  In some cases, the overlap may be eliminated by the use of schemas (more on that in a future post), but for the time being, this is the one situation where a single, clarifying adjective prefixed to the noun is in order. GeographicPoint and TalkingPoint will create the necessary distinction between the two ideas.  However, avoid preemptive use of this technique; “I had better name the table GeographicPoint just in case another type of point comes along in the future.“  Wait until you actually have an overlap, then deal with it, rather than trying to imagine all of the possible future situations — you’ll just drive yourself crazy and in the interim, degrade the simplicity and clarity of your table names.

Associative Tables  These tables resolve many-to-many relationships that exist in your data structure.  Recently, I have started breaking — er, evolving — the first, One Noun, rule because I came to the conclusion associative tables really are fundamentally different from other tables.  They describe a relationship, not a thing.  Hence in their case, I would use — horrors! — a transitive verb.  For example, the tables Presentation and Document are related in the sense that a given presentation could consist of many documents, and a given document could be used in many presentations.  This is a classic many-to-many relationship.  The associative table that resolves the relationship could be the transitive verb Contain.  Think ‘Document-Contain-Presentation’ and ‘Presentation-Contain-Document’.  I’m of mixed opinion about the use of the singular in this case.  I like the absolute nature of the Singular rule, but use of the plural does make it read better. So let’s go with singular, for the purposes of consistency, at least for now.

No Abbreviations For all but the most completely self-evident ideas, abbreviations should be avoided.  Just about any reason to use ultra-short tables names vaporized with the advent of near-zero-cost data storage and the various Intellisense-like development systems available today.  Let’s face it, you’re just not typing the entire table name all that often.  You will read it frequently, however, so what you lose in brevity is made up by the immense value of your code being understandable by yourself and others, now and years in the future.

Other Thoughts

Since early 2005, I have either led or participated in software development projects using the Agile methodology.  Primarily due to time constraints, only the tables that were actually needed during the then-current iteration were created.  Even if you’re not following Agile, it is strongly recommended that you create only the tables demanded by specific requirements you clearly understand.  To borrow from and butcher von Moltke a little; “A design never survives first contact with the user.“  What you learn in each iteration of development may well eliminate tables you were sure you needed.  At the same time, you will be required to create tables you previously never even imagined.  Let specific, current, feature-oriented requirements drive out structure  — always.

The Object Explorer pane of SQL Server Management Studio provides a pretty decent way of reviewing the tables you have created.  If you are successful in applying all of the rules above, you should be able to look at the list of tables and have a pretty good sense of the structure, without having to resort to system documentation that may (or more likely, may not) exist.  In fact, that’s the litmus test — if you look at the list of table names and you can’t more-or-less figure out what the structure is, then you’ve probably got some more work to do.

You may conclude that following all of the rules above very narrowly defines the possibilities; two people, separated by the time and space but looking at the same subject matter, might very well come up with very similar or even identical table names.  For example, if one of entities that is being described is a human being, exactly how many different words are there?  Eliminating profanity, pejoratives and the politically incorrect ones, not that many, really.  So why does everybody keep creating the very same Person table from scratch?  Good question, for which I can’t currently think of a good answer — the subject of a future post, possibly?

I will not attempt to address the question of logical vs. physical modelling, or the mapping of entities to physical objects — I may in a future post.  What’s certain, though, is that sooner or later the data winds up in a table structure within SQL Server 2005, and I assert that a little care and attention to its name will pay off over the long term.

Next up, naming standards for columns.  Thank you for reading, and please let me know what you think!