Hi,

I am not that experienced with joins and was curious if anyone would take a minute to tell me how I can improve the following:

    SELECT i.hardwareID, t.hdwCat1 + '/' + c.hdwCat2 AS hdwType, i.hdwDescr
    FROM tblHdwInventory i
    JOIN tblHdwCat1 t ON i.hdwCat1 = t.hdwCat1ID
    JOIN tblHdwCat2 c ON i.hdwCat2 = c.hdwCat2ID
    JOIN tblLocationHistory h ON i.hardwareID = h.hdwID
    WHERE h.facilityID = @clinicID
    AND hdwOrSys = 0

tblHdwInventory includes descriptive information about the hardware (e.g., type, category, description). Tables tblHdwCat1 and tblHdwCat2 are also just lookup tables - the integer ID value is stored in tblHdwInventory and the descriptions are in tblHdwCat1 and tblHdwCat2. Table tblLOcationHistory relates the Hardware item ID with the assigned facility's ID, so the purpose of this query is to pull the description of all hardware assigned to the selected facility.

Thank you in advance for any advice.

John

This query really can't be improved. If you have a value in one table and this is used as a lookup key into another to retrieve a value from that second table, then a JOIN is just exactly the way you do that.

I do have some table design observations, though.

I think that your use of 'hdwCat1' in your 'tblHdwInventory' table to contain the ID value of the entry in 'tblHdwCat1' whose key is 'hdwCat1ID' is a bit confusing, especially when the 'tblHdwCat1' has a column named 'hdwCat1' which is something entirely different. It certainly confused me in your other post. :) You would be better off to name your columns consistently to reflect their use and contents, so, for example, that anyone can see that an ID column contains an ID value no matter what table it appears in.

I'm a bit suspicious of your use of the names 'cat1' and 'cat2' in your 'tblHdwInventory' table. Do you really have two entirely different types of categories (like 'color' and 'size', say), or are these repetitions of the same category, with one being perhaps an alternate for the other? If the latter, then your structure violates a fundamental precept of table design.

This is almost certainly a matter of taste, but why do you use a 'tbl' prefix for your table names? Everything in an SQL query revolves around the use of tables, so IMO, the prefix is redundant, it adds nothing to anyone's understanding of the query, and just clutters things. YMMV.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Hi Jeff,

Thank you for your response and observations.

Yes, I must admit the naming of hdwCat1/hdwCat1ID is sloppy.

The cat1 and cat2 actually do represent different categories. Cat2 is a subcategory of cat1, and can be NULL. For example, Cat1 might be "Monitor" and when Cat1 = "Monitor", Cat2 could be "LCD" or "CRT", etc.

The "tbl" prefix comes from a habit I got into while programming Access databases. I kind of unthinkingly carried it over into SQL.

All good observations and I greatly appreciate them. It helps me improve my coding when I can get the observations of others.

Thank you,

John

They'll be lots of (bad) habits you'll have to unlearn making the transition from Access. :D

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

using "tbl" is a good habit, not a bad one. It lets you know at a glance that you are dealing with a table directly versus a view or a stored procedure.

I have been doing database stuff for a long time and I am very glad I stuck to my naming conventions. They have saved a ton of searching out where things are at in a database when a problem occurs.

In programming it is also a great practice to use the same type of naming conventions for controls and such. This is not a new concept or a bad one at that. I do agree it is better to make your column names different than table names

I encourage you to keep up your naming convention as it will really save you trouble in the future.

Chris

There is no accounting for taste, and variable/object naming conventions are frequently the subject of religious wars. That having been said, and not (necessarily) being desirous of starting one ;), I disagree that using a 'tbl' prefix is a good habit. IMO it adds absolutely nothing to a query other than making it hard to read. I think the "pidgen" Hungarian notation of identifying tables by prefixing "tbl" serves no useful purpose. I believe Hungarian notation is an abomination to lucid code, and I find it interesting that Microsoft is now discouraging its use in its .NET environments.

If you are reading a query, you are dealing with a table. Since you already know you are dealing with a table, why bother to reinforce that by redundantly prefixing with something that repeats the obvious? Do you also define table aliases in your FROM clauses in your queries by prefixing "tbl" to those as well? If not, your convention is not consistent, since an alias is also a reference to a table.

Are you one of those who also uses a form of Hungarian notation for column names to encode the datatype, using, say, 'iCustomerID' so we all know that the ID column is an integer? If not, your convention is not consistent, since you feel obligated to encode the table datatype in the name of the table, you must also feel obligated to encode the datatype everywhere else. (If you do, I don't want to read those queries... :D )

Still, I do encourage the use of some kind of convention; almost anything is better than a haphazard nothing. The kind of convention I use, though, centers around describing the purpose or function of the object that can be embodied in the name. If I can discern the purpose or function from the name, it almost always immediately follows that I can infer the datatype of the object, since, after all, I am familiar with the objects I am manipulating, and I try for consistency in the assignment of object names as much as possible. Consistency is more important than dogma, I think (is that dogma? ).

I use a particular naming convention for stored procedures; not so much to identify them as a stored procedure per se but rather to provide some form of mnemonic device to aid in identifying the procedure's purpose and function.

I tend to NOT use a convention for views. A view, after all, is a "virtual" table, and in many cases I'm quite happy to allow users of the view to labor under the impression that they are dealing with a 'real' table. The programmers or other users are quite oblivious to the fact that the 'table' they are querying is actually a view, and that allows me a level of control over the environment (in terms of access security), and, more importantly, a level of abstraction to the physical implementation of the database.

Do whatever works for you, though. The above is just one man's opinion...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Hi All,

I agree with Jeff that using psuedo-hungarian notation for database objects is not a "good habit". Whilst Chris has noted that it may help with DB maintenance issues, the fact remains that there are plenty of quick, graphical tools that will tell you what objects you're dealing with -and- there are plenty of well established guidelines on developing documentation for databases: we all have data dictionaries right?

On the other hand, I disagree with Jeff that one should not use hungarian notation for writing code. There are fewer tools that will, accurately, tell you what type of object or type you are dealing with, and fewer defined documentation methods.

Whilst Jeff does, correctly, say that MS is recommended the dropping of hungarian notation in .Net apps, this is predicated on the developer using Visual Studio.Net which has tool tips that tell you what type the object is. Anyone using any other IDE (or notepad) does not have access to this functionality.

Cheers
Ken

www.adOpenStatic.com