Project Management: Unique Skills Beyond Paper Documents

Commentary:  This is a series of posts that will follow highlights of an ongoing project I am currently engaging. The project is the development of a turnkey thematic shopping cart. My charter as the project manager is to develop the prototype site, establish baseline performance metrics or reasonable sales, and turnover the prototype deliverables. I am not at liberty of discussing specifics but will provide general highlights of the project in a series of sporadic posts.

Project Management and Unique Skills

While project managers create the standard project documents, technical specifications, and a plan of action and milestone (POA&M) at the operational level, the tactical piece requires unique skills. The technical requirements can be achieved tactically in many ways. Each approach has its benefits and drawbacks. For example, without getting into the security specifics, this project has a requirement to default to the lowest level of access. That way if someone attempted to load a session page directly from the browser without logging in, it will redirect to the login page by default. This required careful testing of the logical constructs ensuring that the default is always to redirect to the login on any failed state. This sounds simple but incorrectly using operators or poor logical structures may create a subtle vulnerability. Aside from the careful review of the code execution for security design, other error checking and database design issues were just as challenging.  Many-to-many relationships are one of these challenges. These relationships do little for data normalization but are useful especially in sales and distribution design patterns. 

First, let us discuss this site project in general which is product centric having some complex relationships. There are base prices, retail prices, sales prices, and special pricing.  The vendors may include the manufacturer, wholesalers, and retailers. Products are assigned to multiple marketing channels and fulfillment services may be through more than one source. Thus, each product item may have several prices, vendors, and fulfillment sources. Source selection is determined by the mix of products desired by the client. I managed these relationships in the database through the use of many-to-many relationships. In handling the many-to-many relationships I used what I called matrix tables as demonstrated in Illustration 1.

When calling data from these tables, I used SQL SELECT statements that related the many-to-many relationships without getting to complicated but is a little more than an ordinary SELECT statement.   For example:

SELECT * FROM TBL_Item, MTX_ItemVendor, TBL_Vendor, TBL_Prices WHERE TBL_Item.IID = MTX_ItemVendor.IID AND MTX_ItemVendor.VID = TBL_Vendor.VID AND TBL_Item.IID = TBL_Prices.IID ORDER BY ItemName, VendorName.  

I had to be sort of tricky on the adding of a product by breaking up information in the form into separate actions such as inserting basic item data then the base pricing followed by vendor assignments. First, I inserted the new item record using a SQL statement similar to this one;

INSERT INTO TBL_Item (Name, Desc, Status) VALUES ('#Name#', '#Desc#', '#Status#')

Then I grabbed the new record identity immediately;

SELECT @@IDENTITY as 'Identity' FROM TBL_Item

I set the Identity equal to the IID then turned to the pricing. In the management of the information additional pricing is added later during routine updates as required but first the base pricing is inserted as the product record is created. I did not show the pricing table in Illustration 1 but it is in a one-to-many relationship to the Item table. Creating this pricing record is a simple insertion of the price into the price table much like this following statement that uses the captured Identity;

INSERT INTO TBL_Prices (IID, Price, Reason) VALUES ('#IID#', '#Price#', 'Base Price')

Finally, I pulled the vendor identities from the form list box of vendors then related the vendor to the item  in a looping construct. Coldfusion performed the looping based on the number of selected vendors. I used incremental variables where n indicated the vendor in the list box options then looped and inserted the records for the selected vendors in the selected array list using a SQL statement like this;

INSERT INTO MTX_ItemVendor (IID, VID) VALUES ('#IID#', '#VIDn#') 

The actual code, constructs, and field names were more detailed than the what I provided here. Nonetheless, the handling many-to-many concepts are what is important. I tried to keep it as simple as possible without sacrificing performance or security. One important step was to understand the full process and when specific information was needed. Cramming all the information into one form and trying to do everything at once is not always the best solution. For example, alternate pricing can be an update to the item record when the pricing is needed. Thus, at item record creation only the base price is really needed. Likewise, the processing of the information in the form was broken up in a behind the scenes in the processing page in which the form data was handled in incremental steps. Many-to-many relationships can be useful in some design patterns and may even simplify what could have been a complicated table structure and SQL actions.

In my next post on this project, I want to discuss the error checking methods I used with some of the forms to vet business rules without loading the server with unnecessary processing.

Commentary: The next post is not scheduled so please monitor for it.   Please review my other postings. Feel free to email me atjames.bogden@gmail.com or post a comment. Thanks for reading!

Project Posts:

1. Ongoing Project: Combining Project Management with Hands On Skills
2. Project Management: Unique Skills Beyond Paper Documents
3. OnGoing Project Manages Business Rules