Last week, I had to exercise the old gray muscle on some database design, and ran into a little gotcha when trying to make a high level conceptual database model in Visio.  Essentially, when you're using the ERD template, you can't model a many to many relationship without elaborating the model further with a junction table.

So that being established, I referred to my handy copy of Michael Hernandez's brilliant Database Design for Mere Mortals (by the way, I'm proud to be able to call him my friend), and some other articles including Jason Mauss' Database Naming Conventions for some opinions on naming things.

Many people suggest that the junction table name be a compound of the two tables comprising the many to many relationship (for example, DoctorsPatients).  In my opinion, this is fine only if the junction table will not contain other columns.  In many cases, the junction table will be used to record other information... order details, doctor assignments, etceteras.  In this case, I think it's better to formulate a name that describes the other content and let the two foreign keys (for the two tables with the many to many relationship) convey the many to many relationship.  For example, in the case of the Doctors and Patients, Assignments is more accurate than DoctorsPatients.

What do you think?

Over on my OZ blog, I wrote a couple of posts about using folders in SharePoint lists and document libraries.  I bemoaned the use of folders in SharePoint document libraries and lists as a way to organize large quantities of documents. 

Finding something in a digital folder hierarchy can be impossible unless you already know the hierarchy. For example, I've seen top level folders in these hierarchies that align with a company's departments or divisions, beneath these are folders for fiscal years, then maybe some folders that describe activities.  What's the problem with this? If I'm new to the company, I pretty much need to understand the business before I can find the document I need.

It seems pretty natural to want to organize things just like you might in your physical filing cabinet, but a filing cabinet doesn't do things like search or filtering, and a computer doesn't expose things in a tactile way like a filing cabinet.  Back in the DOS days, if you had a folder hierarchy like the one I described above and you wanted to find a file with a certain string of characters in the file name, you could issue a command like this:

DIR budget*.xls /s

This would search through the hierarchy relatively quickly and list the files matching the pattern along with the folder in which the file resides.  In Windows, you could use Search, but it's fairly slow because windows has to parse through the contents of the hierarchy one file at a time.  Then along came desktop search which indexes the file system, making searches much faster. Nice, but we're still using a pretty blunt tool. What if we could label files with certain terms appropriate to our business and then quickly expose them based on those labels? 

We can add custom document properties to a file, but in Windows explorer, there's no way to include the custom property as a column and then filter or group on that column.  For example, what if I put all my files in one folder, and all my documents had custom properties called "Fiscal Year" and "Division"? Then, what if I could have a shortcut on my desktop that opened Windows Explorer and showed a list of files from my folder whose custom properties contained 2007 for Fiscal Year, and Manufacturing for Division?  Or what if another shortcut listed all the files in my folder, but grouped things by Division and then by fiscal year? This would be like a Pivot Table for your files... but to me it's like Microsoft gave us these Custom Properties and then didn't give us anything we could do with them.

The bad news is that the file system can't do this.  The good news is that SharePoint can. 

Custom Document Properties are metadata.  Being able to manipulate file listings based on their metadata is one of the core pieces of functionality in SharePoint.  My opinion?--- don't use folders in the file system OR SharePoint.  In a SharePoint document library, custom document properties can be promoted or demoted to/from the document library's columns (effectively linking the two). I believe we should use SharePoint, properly describe our documents with Columns (and/or custom properties), and then use custom views to filter and/or group based on these.  It gives you the ability to simulate a folder like experience, but it's important to note that in SharePoint, if you nest documents in folders, this doesn't work.

Much has been written about business process analysis.  There are articles about Use Cases, Sequence Diagrams, and more.  It's my opinion that most small businesses can avoid this level of complexity when they're trying to understand their business processes, and can instead focus on understanding the roles and responsibilities of people as a start.

One tool that I like to use is a high level diagram that correlates responsibilities with individual people (or small teams), and can then be used to deconstruct the responsibilities to tasks and process flows.  I call it a responsibility diagram.  If you work in a small business, you likely have many jobs to do-- understanding these jobs is a great place to begin.  Here's an example from a small business that sells sewing project kits online:

Admittedly, this is a bit of a hybrid of several UML diagrams, but what it's really designed to do is get a small business owner (decision maker) thinking about the high level processes and lead them into decomposing them into more manageable workflows.  Once we've got this diagram, we can rename the person to a role, in this case, a suitable role name might be "Order Taker".  This will help to separate the tasks from a person, and may make it easier to identify responsibilities that should more naturally fall on other people.  In this example, why wouldn't the Inventory Adjustment be done by Kelly.

I call the rectangular box the responsibility boundary, and once the responsibility changes, the flow would be illustrated in another box.  Similarities to Use Case Diagrams and Sequence Diagrams are obvious to the initiated, but to the business owner, it's still a simple diagram that portrays the work their people do in an easy to understand way.

In Part 3, we'll talk about what happens when a responsibility crosses boundaries to another role.

Our city (and province) finds itself in a unique situation.  The local economy is booming thanks to high global oil prices, and the proximity of the Fort McMurray oil-sands, one of the largest oil deposits in the world.

Property values are up, retail sales are up, unemployment is way down, people from coast to coast are coming here to find work. Unfortunately, this leaves many small businesses scrambling to find staff.  It seems that everywhere you look in the city, you'll see a "Now Hiring" or "Help Wanted" sign.  In fact, the MacDonald's restaurants have taken to posting their pay rates on those portable roadside signs, and they're pretty competitive.  Our business is no stranger to this--- we're recruiting aggressively, advertising in numerous mediums.

One thing that a small business can do to be more efficient with what staff they do have is something the big guys have been doing for a while now--- Business Process Analysis.  Understanding what your company does and how it does it, and then documenting it in detail is the first step to improving efficiencies.

This series of posts will highlight some simple techniques that a small business can use to analyze, understand, and improve its business processes.  Please stay tuned.