Building app to Classify / Describe Products - Overwhelmed somewhere between planning & execution

Question

Greetings!

I recently started working for a company that carries a line of 20,000 Surgical Instruments. Our data on all items is currently spotty and chaotic at best. I intend to fix this.

I have been tasked with redesigning the web site. As part of the project, I'm building an app to classify and describe all products. We don't do any direct sales on the site, but have a network of sales reps and distributors that will utilize this info.

A picture says a thousand words, so here's a link to a diagram I made showing what I'm trying to accomplish: http://i.imgur.com/gUuxB.png

I'm currently trying to achieve this with CakePHP / MySQL. I'm not too heavily invested in these, and am open to suggestions for alternatives. Perhaps a CMS already has this functionality? Some sort of Open-Source gizmo? Python / Django?

I'm having difficulties determining proper database structure and code logistics. I'm headed in to this project a novice, hoping to emerge as an intermediate.

Any advice on how to tackle this enormous task would be helpful. I've spent almost 4 weeks in the planning phase, and can no longer see the forest for the trees. My head's about to explode.

Thanks!

Answer 1

Who will be using the app? How many people at one time? Any particular reasons for choosing CakePHP?

From what I've heard, Rails and CakePHP have a lot of 'magic' involved; it becomes a problem when things suddenly don't work right and you have no idea why. If the app is only for people within the company and your ultimate goal is just to have these instruments classified, the easiest route would be to use Python with SQLite.

But easiest does not necessarily equal best:

Pros: - Using Python means that when you need to make changes to your code, it will be relatively painless. - Using SQLite equals ease of use; no setup required at all. If you're using Python 2.5 or higher, SQLite is built in, and it's very easy to use. - SQLite just interacts with one .db file, so when you need to move/change/export data from your database, there will be no hassle.

Cons: - SQLite doesn't strongly enforce types. This means even though you set an attribute as 'integer', someone can come along and put text there and SQLite will happily accept it. Depending on how much data entry other people may be doing, this could become a problem since you could have inconsistent data. - SQLite is only meant for a few clients at a time. If a lot of people will be using your database, you'll need to go with MySQL instead. - SQLite won't work with terabytes of data; this may or may not be a limitation for you.

Answer 2

It sounds like you need a rudimentary inventory system. A way to track the basic attributes of the items you've entered. You will also need some reporting, so the end users can query the system to get an idea of what is in the system.

Find a solution that you can reference or review to get idea's from. Your solutions are a combinations of idea's from previous applications. With this in mind, find examples of works you like. Their might be an application that has an intuitive interface. Use it's interface. You might find a system that as a nice search feature. Implement the search feature.

My advice in the enormity of the project is first; tackle the tasks one a time. Try to break the tasks down into small units. Don't worry about if it's the perfect solution. It will evolve. Second; this project might be beyond your skill level. It's not a bad thing to ask for help or admit you can't do it. Every company is different, so gauge your environment.

As a developer I often inherit applications that were outsourced or written by an inexperienced developer. Nothing frustrates another developer more than working on code that has no structure or consistency.

Answer 3

Worry about your database design first, then what kind of framework you'll use.

Regarding EAV, check out http://decipherinfosys.wordpress.com/2007/01/29/name-value-pair-design/ If you still really need to do it, consider PostgreSQL's hstore. (Google it, I don't have the rep to post the link.)

Answer 4

I think I like the choice of CakePHP in this instance. Basically you're just providing a web interface to your internal team so they can perform manual data classification, etc. So you just need to get a basic data editing interface up as quickly as possible. Cake's biggest strength, IMO, is its focus on rapid development. Sounds like a good fit here.

Cake also has a Tree behavior, so it might serve as a basis for your schema. And it strongly suggests sensible defaults, so it will force you to think of your data in a very structured manner.

That said, it's also not very nice when it solves the problem in a manner differently than the programmer wants. It's not as flexible as the others. The other big caution is that it's slow, but for an internal app like this, I don't envision that being a current major concern.

In short, it's a good way to force structure on your data (and code). It's fast for development. And you don't have a requirement for performance.

Answer 5

Nice drawing:)

Did you do any experiments with the actual table stucture? It does not look so hard. Here is a go while trying to keep things as simple as possible.

==products==
id[int]
number[varchar]: unique, ie. #50-334 
category_id[int]: has-one relation to category.id

==categories==
// What you call branches in your drawing.
id[int]
parent_id[int]: tree structure
title[varchar]
description[text]
image[varchar]

==attributes==
id[int]
attribute_type_id[int]: has-one relation to attribute_types.id
is_universal[bool]: is this attribute used for _all_ products?
name[varchar]
value[varchar]

==attribute_types==
id[int]
codetag[varchar]: name to identify the attribute type from the code. Each type haves different validation. Some types will maybe be validated using data from helper tables.
title[varchar]
description[text]

==products_has_attributes==
// "Join table" with many-to-many relationship between products and attributes.
id[int]: Optional
product_id[int]: has-one relation to products.id
attribute_id[int]: has-one relation to category.id

==categories_has_attributes==
// "Join table" with many-to-many relationship between categories and attributes.
id[int]: Optional
category_id[int]: has-one relation to products.id
attribute_id[int]: has-one relation to category.id

First rough draft. Read it as such.

Answer 6

Think about your user first. Then talk to your users. These sales reps have been working in this field longer than you and they will already have their own mental categorisations of the stock.

What kind of interface would they expect to see? The interface you design should reflect the most common use cases!

In what ways would they expect to query this database? If you consider the database design before asking these kind of questions then you may find that the most useful queries for the user are the ones that you haven't optimised for, and you have to jump through hoops to pull them off.

Make sure you have an intuitive feel for what the user expects before you even start thinking about database schemas or which technology to use.

Answer 7

This sort of thing is dead easy in Django (assuming I really understand what you're asking). A models.py something like this:

from django.db import models

class Item(models.Model):
   price = models.DecimalField(max_digits=9, decimal_places=2)
   material = models.CharField(max_length=100)

class Sprocket(Item):
   length = models.DecimalField(max_digits=9, decimal_places=6)
   shape = models.CharField(max_length=100) # straight/curved

class SprocketProduct(Sprocket):
   radius = models.FloatField()
   maxload = models.FloatField()
   weight = models.FloatField()
   color = models.CharField(max_length=100)

will more or less do the trick. Here, SprocketProduct inherits from Sprocket which inherits from Item.

Also, asking for Item.objects.all() will give you all items, Sprocket.objects.all() will give you only sprockets -- you get the idea.

Answer 8

Hi,

I agree with drspod - you need to understand what the users want - you may build something of technical genius, but without users it doesn't return the value on your time investment.

One you've understood that, I would recommend going down the Python/Django route, or consider Rails as a fast way of getting where you want to go.

Django particularly has excellent documentation, which should be enough to achieve your goal of becoming a intermediately experienced web developer.

Ben