The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

E-commerce blues...

Well, I've just run into a wall.  I'm designing an online store (asp.net with classic ASP for checkout due to PayPalAPI) with the caveat that my company has multiple clients that at some point may end up using the store.  So that means I need to add a ClientCode field to nearly every table in the database as each of these clients may have the same category of items (e.g. Clothing, Gifts, etc.) and I have to repeatedly test for this variable to make sure I retrieve the right values. 

To make matters worse some items (e.g. Clothing) can have sizes and colors that I need to allow the customer to pick where appropriate.. so I've created a ProductDetails table that contains this information and is related to the Products table on the ProductID field.  The issue is that now the shopping cart needs to differentiate between the same product  (i.e. same ProductID) but different specifications (e.g. one L red shirt, one S blue shirt, etc.) and display them seperately.  Plus, when the user confirms an order it has to add this information to an Orders table, again differentiating between products with the same ID but different specs.

Figured I'd vent about this... the specifications changed about a month ago (been working on it for about two) so I had to redo practically the entire thing.. and now management is expecting it done relatively soon because the project with the client has finished and they promised it to this client to get their business long before they knew if it was possible to do, but really... the architecture for it is such a house of cards with so many potential variables that may or may not ever come into play.  It's essentially an online store template that any number of our clients can get customized with thier own products and their own layout, and to be frank it's driving me nuts!  I'm tempted to just admit defeat and give up, even if it costs me my job.  I'm rather new to doing e-commerce, and honestly after seeing what a hassle it is, I'd rather not do it again!
Wayne M.
Friday, September 22, 2006
 
 
I'd love a project like this. Sounds like you needed a more generic design up front. Every vendor will have different systems for variations on a product, none of which will be expected to correspond between vendors. So you need to allow a product ID and any number of qualifier codes to be appended to the product ID.
onanon
Friday, September 22, 2006
 
 
Wayne you've probably thought of this, but why not have completely seperate systems for different clients? Not big enough?
D in PHX Send private email
Friday, September 22, 2006
 
 
In your second paragraph, with the clothes.

It seems that you need to have the cart handling the specialization of a generic style of clothing.

Maybe seperate the products into two types, 'straightforward' and 'specific'.

So the straightforward are just one size (like a candlestick or pet rock) and the specific requires a choice of what size, color, etc.

Hope that helps.
D in PHX Send private email
Friday, September 22, 2006
 
 
ZenCart is an excellent eCommerce open source project that has most of these problems solved.  ZenCart is itself a fork from the OSCommerce project:

http://www.zencart.com
Herbert Sitz Send private email
Friday, September 22, 2006
 
 
ZenCart is a great ecommerce product and I use it on my sites but..

Wayne appears to want to use a shopping cart that will allow multiple stores(clients) within the same shopping cart instance.  ZenCart can't do this.  The only easy way I know of to accomplish this is to create separate carts in different directories as:

www.sitename/client1
www.sitename/client2
etc..

This would also create a separate database for each cart and would accomplish what appears to be his objective.
ps Send private email
Friday, September 22, 2006
 
 
Two thoughts ...
(1) If you are trying to sell from several suppliers through a single cart (so that each supplier gets his/her share of the revenue) why not create a new identifier for products in the cart - this would be a string comprising the productId, the supplier code and data to identify the size and style. If the buyer completed her/his transaction that new identifier would be stored permanently as a record of the item purchased. If it was necessary, it would be possible to parse the identifier and look up the product and supplier data behind it. However I suspect that there would be little need for this for completed transactions (i.e. no significant processing time penalty). It also has the benefit that the products can be marked obsolete in the database (i.e. not offered for sale on a future date) without invalidating the historical data about past sales.

(2) If every cart will only hold goods from one supplier (i.e. several virtual separate stores) the above approach probably works also. But a customer will have "inadvertently" selected one of the suppliers when he/she visits what s/he thinks is a single store, and will only "see" products for that supplier. However the invisible accounting stuff would need to separate revenues due to each supplier in the same way as the first case.

In both cases I assume that the product record also includes the supplier code where appropriate.
Robin2 Send private email
Friday, September 22, 2006
 
 
Wayne,

I realize you've already done the rework, and you're not asking for advice per se, but I think your dilemma underscores the importance of generic, normalized database design. I haven't seen your design, and I'm not throwing stones, but what you're trying to build is really VERY complex because so much stuff can change. And there are almost infinite combinations of client to product to feature, not to mention orders, inventory, pricing, specials, shipping, etc.

No separate databases. The problem is they'll never scale. Use a good, old-fashioned relational database that can handle all of the data requirements.

For what it's worth, here's what I came up with as far as a database design goes for an online store, and this is just off the top of my head in about an hour. Give me a day, and I'm certain it would be a bit more extensive:

"My company has multiple clients that at some point may end up using the store.  I need to add a ClientCode field to nearly every table in the database as each of these clients may have the same category of items (e.g. Clothing, Gifts, etc.) and I have to repeatedly test for this variable to make sure I retrieve the right values."

Handle the many-to-many relationship between Clients and Product Categories this way: 

tblClient
- ClientID (PK) /* or 'ClientCode' */
- ClientName
- ClientDesc

tblProductCategory
- ProductCategoryID (PK)
- ProductCategoryName

tblClientProductCategory
- ClientID (FK)
- ProductCategoryID (FK)
- FromDate
- ToDate

FromDate and ToDate are important, since they can describe the currency of a particular relationship. For example, Client_X may want Clothing for awhile, and then not want it, and then want it again. It's good for answering business-oriented questions, like, 'When did Client_X have Clothing the first time?' Good for audits, too.

"To make matters worse some items (e.g. Clothing) can have sizes and colors that I need to allow the customer to pick where appropriate, so I've created a ProductDetails table that contains this information and is related to the Products table on the ProductID field."

Nope. Don't do that. That one table will be a nightmare to maintain, since available sizes and colors may change; and, it's not really a good idea to store multivalues in a single field. Better to break out all of your entities/nouns into separate tables, and associate them in associative tables. Do this instead:

tblAttribute
- AttributeID (PK)
- AttributeName  /* e.g., 'Color', 'Size', 'Screen Width', 'Horsepower', etc. */

tblProductCategoryAttribute
- ProductCategoryID (FK)
- AttributeID (FK)

Now we have Clothing associated with 'Size' and 'Color', and Cigars associated with 'Brand' and 'CountryOrigin'. These tables may seem too fine-grained, but trust me, when the customer wants to change them around for a particular ProductCategory, you'll be glad you took the time. tblProductCategoryAttribute is really important, because it will allow you to keep track of which attributes belong to which product. 
 
To document which ProductCategories have which possible valid values of a particular attribute, use these:

tblValue
- ValueID (PK)
- ValueText

ValueText will be words like 'medium', '17"', '12C', 'blue', etc. And:

tblAttributePossibleValue
- AttributeID (FK)
- ValueID (FK)

So now we have access to good *possible* attribute values, like 'color:green', 'color:blue', 'size:medium', 'size:12C', 'CountryOrigin:Cuba'.

Here's where you finally get to describe that shoes have colors of brown, blue and taupe, and sizes of five thru sixteen but not thirteen, from July 3 - August 17 2006:

tblProductCategoryAttributeValue
- ClientID (FK)
- ProductCategoryID (FK)
- AttributeID (FK)
- ValueID (FK)
- isValid
- FromDate
- ToDate

All of the tables above are essentially lookups for you to populate your online store with the correct product information.

"Plus, when the user confirms an order it has to add this information to an Orders table, again differentiating between products with the same ID but different specs."

When the customer clicks on a red shirt size medium, you can now capture that information from the Order Form:

tblCustomerOrder
- OrderID
- ClientID (FK)
- CustomerID (FK)
- OrderDate

This has a 1-M relationship to:

tblCustomerOrderProduct
- OrderID (FK)
- ProductCategoryID (FK)
- Quantity

...which has a 1-M relationship to:

tblCustomerOrderProductDetails
- OrderDetailsID (PK)
- OrderID(FK)
- ProductCategoryID (FK)
- AttributeID (FK)
- ValueID (FK)

Haven't even touched inventory stock or pricing, and we haven't looked into the different Products for each ProductCategory, but you see the point.

At this point a lot of developers will ask, "Why can't you just store all of that data as string text with the product?" Answer: You could, but requirements might change again, you'll be rewriting the whole database. True, this is more upfront work, but when you build an administration-facing app to maintain all of this data, it'll sing. Been there. Done it both ways; this is better.

"Figured I'd vent about this... the specifications changed about a month ago...."

E-Commerce is frigheningly complex in certain cases. When requirements change, it's even worse. But, standard models will go a long way to helping any developer (even old ones) overcome the obstacles. Good luck.
Albi-wan
Friday, September 22, 2006
 
 
Albi-wan, thank you very much for your suggestions.. I'll see how much of it can be implemented.. alas I fear that I may have to do another redesign of it, and that's going to be hard with management breathing down my neck to get it working so they can fulfil their promise to the client.
Wayne M. Send private email
Friday, September 22, 2006
 
 
Wayne, Albi-Wan was clear enough and I'll not add anything. Just good luck and do not feel helpless.
Sevenoaks Send private email
Tuesday, September 26, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz