hi,
i started programming about 5 years ago to improve my ecommerce activity. i started with calc formulas and macros, then i moved to bash and i finally approached php and sql.

in the years, my scripts substited me in most of my routine activities:

• a calc spreasheet with the inventory and the orders is converted to csv.
• a hundred of bash scripts get data from the csv and make invoices, send email to customers, manage incoming emails, keep my ebay and amazon accounts updated, make stats out of my sales data and help in making new orders and a billion of other tasks.
• a couple of years ago i spent a few months learning php and created from scratch an ecommerce website that works like a charm. scratch means scratch: no kind of CMS, no framework nor anything else but the code i wrote with my keyboard.

the outlook is the one of an eiffel tower made of matches: wonderful, but...

• nobody can make it work but me. if i die, my wife will never be able to run our business by herself.
• like the eiffel tower above, it's very fragile: the overall code quality is very poor (i still use some scripts i wrote at the beginning of my activity, when i was a total newbie) and it often happens that manteinance is needed.
• many of the scripts (dozens) depend on each other, so it is VERY hard to improve a script without touching many others.
• integration between system components is so high that i can't even edit the ecommerce php code without modifying some bash scripts... an incredible ball of wool...
• data organization is just bad: when i needed a db i sometimes created a csv, some other made a calc spreadsheet, some other filled myql tables as if they were calc spreadsheets. just bad, as i wrote.

don't limit your imagination: if a programming error can be made, but sure i did it or i'm doing it in the next few hours...


the eiffel tower has been pretty stable in the last few weeks, so i started wondering about building a "wife-friendly" system. where to start from?

1) making it from scratch or customizing existing software?

• starting from scratch means becoming a programmer first (i'm a sculptor, by now), then spending a lot of time writing megabytes of code the right way. i'm pretty sure i will be able do it, but how long will it take? 6 months, 1 year, 5 years?
• customizing existing software means spending some time finding the best open source software to start from, then being able to modify it. it would probably be faster then starting from scratch, but i'm not 100% sure i will ever be able to do it: when i tried in the past i've always faced too complex code for my skills and finally gave up.
  if i were a programmer this problem wouldn't exist, but if i must become a programmer then it would be better to start from scratch, probably... or not?

2) let's say i probably must become a programmer: what to learn?

• php
  - pros: i already know some things. flexible (can probably replace 99% of my bash scripts). there's plenty of wonderful open source software i could use.
  - cons: hard manteinance (i read it is updated frequently and this leads to deprecated code).
• ruby
  - pros: i read somewhere it's rock solid: write good code and it will be done forever.
  - cons: i'm a total newbie. can probably replace 90% of my bash scripts, but some php code will be needed for the ecommerce website.

"probably" is definitely the most common word of this post: i studied A LOT in the last weeks, but i'm far away from being sure about anything. can you please address me the right way?

1. Prefer to customize existing software

2. Choose Ruby

While your existing solution may be a loose collection of components of dubious and varying quality, one that's grown organically over a period of years and is basically held together by string, sticy tape and chewing gum, creating it has put you in a unique position. You are now in possesson of some extremely important knowledge: You know an awful lot about how this solution should have been created in the first place.

In any software project, the most important phase is the one where you're figuring out the requirements, and then, based on those requirements, determine what would be the most appropriate tools to get the job done. This "getting your ducks in a row" part is vital, because it determines how the entire product or solution will be designed, which in turn will decide how well the final product will perform under varions conditions. Needless to say, getting this part right is of the utmost importance.

As it turns out, this is really difficult. So difficult in fact, that many advocate an approach called "make one to throw away" (from Frederic Brooks' legendary book "The Mythical Man Month") where you create a working prototype and then just start from scratch, using the experiences from the process of creating the prototype to (hopefully) avoid the (worst) pitfalls when it comes to both design and implementation.

The idea behind the "make one to throw away" approch is much the same realization as the one behind the Agile methodology: one acknowledges the fact that it is almost impossible to get the specs exactly right (for a number of reasons) no matter how much time one spends in the initial phase of the project trying to hammer them out. Also, as you spend more and more time and resources to nail down the details, you quickly encounter the law of diminishing returns. So why not cut to the chase and start building, and simply make those mistakes and learn from them?

And that's basically what you've done, except the throwaway was pushed to production. Today you know that tight coupling between PHP code and bash scripts is probably a bad design. You know that CSV files aren't databases, and that databases aren't spreadsheets. You also know that besides getting the job done, a piece of software should be maintainable, and user functions should be user-manageable (I would guess that's what "wife-friendly" means in this context).

The bottom line is, one way or another you will have to start from scratch, while at the same time making sure your valuable experience is put to good use. You could go for an existing CMS, perhaps one you could use as a development platform or modify to suit your requirements, or you could make one yourself. Since we already know you're perfactly capable of doing it all yourself (you've already done it once), I'd say you should seriously consider creating your own. Because besides knowing a lot about how not to make a CMS, you're also in possession of another vital piece of information: you know exactly what your business needs, and that's knowledge no-one else is likely to have.

Whatever you end up choosing, make sure the design is reasonably well documented, and that the implementation is based on tried and tested components. While esoteric languages and cutting-edge databases may be fun to play with and could even have measurable advantages, getting someone else to maintain them later may prove difficult and expensive.

(And then there's the other side of the coin: while all-in-one frameworks may enable rapid development, they often come with a huge attack surface, which should be of concern for anyone creating an Internet-facing application.)

thanks for your conciseness...

i found several pieces of software i could start from, but they're all in php:

• http://www.gestionaleopen.org/ italian project, it has a virtuemart module for the ecommerce section and i'm pretty sure that virtuemart itself has some plugins to manage an ebay/amazon account...
• http://www.invoiceplane.com/ looks powerful, but very simple and clean... it has an italian version to satisfy the unpleasable italian burocracy.

could you write a couple of links of similar ror projects?
or maybe do you think i should implement a php project with my own ruby scripts?

thanks!

thanks for your analysis, you're almost right: 5 years ago, when i started building, i had no idea that it would have become such a complex system... i remember i only needed a way to generate a simple ebay html for each of my products. nowdays, every single task my system performs is clear in my mind, but i find it very difficult to get an overview of the whole system. in particular, i can't exactly recall what a particular data is used for.

an example: i calculate selling stats based on the number of days a particular product has been available in the last 12 months. if you ask me "what are those stats used for?" i can tell it's used to prepare new orders and calculate selling prices, but i can't go any further. i can't certainty tell if there's (or there is not) another script that uses that data. so, if i want to change the way that data is calculated or stored, i can't be 100% sure about how it will affect the whole system.

since i guess the very first step in building a new system would be the database project, may i ask you how real programmers do it? i tried and draw the "data flow" (don't know if it's the right expression, i mean something like a flow chart that shows which data is used for what) on paper, but i'd need a 10 square meters sheet: not that handy...

do you think a tool like mysql workbench could be useful and worth learning?

I can sort of relate to that. I've seen a few projects grow out of control in a similar manner, usually ones started to create a small tool or special-purpose utility meant to solve one particular problem in a quick-and-dirty fashion. Nothing even nearly as major or business-critical as what you're describing, but I still occasionally ended up with a Swiss Army knife-like program built from an unwieldy and mostly undocumented codebase.
Data modeling is almost a profession in itself. Before you can start creating an actual model, you need to know exactly what you need to store and access, so usually one can't jump right into the data modeling phase without fleshing out the business requirements first, but you probably have that pretty well covered.

How do actual developers do this? Well, that depends on how well acquainted they are with database theory, and unfortunately a lot of programmers don't really know much about this at all. I'm certainly no authority on database design, I'm not even a professional programmer, but as a sysadmin and a consultant I've seen enough horrible database design in large, commercial applications to conclude that an awful lot of "real programmers" know even less than I do.

What I can tell you is how they should do it. The formal way to design a database goes something like this: You start by creating a conceptual schema by deciding what kind of data entities you need to store (say, customers, products, orders, addresses and so on), and how they relate to one another (one customer may have many orders, does one order refer to exactly one customer? etc).

The next step would be to create a logical model by starting to think in actual database terms (organize data into tables with headings and cross-references). One goal of this phase is to normalize the database; that is, to eliminate redundancies by using "links" to data in other tables (foreign keys) instead of storing the same data twice or more in different tables.

Finally, the logical model is translated into a physical model, something that can actually be implemented using a specific database system. In practice, there's considerable overlap between these three phases, in particular the last two, as the database engine may offer functionality or impose constraints that could affect the design of the logical model.
I'm not familiar with MySQL Workbench, but from the documentation it seems to be a tool designed for visual data modeling, among other things. If it's any good, it could certainly be useful.