Why You Should Learn Database Programming

[billbenson]

I was just thinking about this today and thought I'd make a post:

When I lost my career in 1999 and couldn't find a job in the dot com economic crash I bought a dummies book on web design and conned my way into a web design job. While doing that, I could see the value of databases for web sites. The benefits of giving custom web pages to different customers, quote generators, displaying different products on prior search history etc. So I bought a book on php / mysql and learned to program databases. That's 12 years ago. I'm not a good programmer or fast, but if I wanted something I always have been able to make it work.

What I didn't realize at the time is how important writing database programs would be to a sales business. Because of some luck, my website brings me more business than I can physically handle as a one man show. I started by doing quotes in Excel. I could have done what most people do and migrate to Quickbooks, but since I could do it in php, I just wrote a quote generator in php.

That database has grown into one that automates or minimizes the work required for many aspects of my business. One database can do quotes and orders, track customer activity, create custom web pages etc. What it really does for me is custom automation. Think of it as a custom automatic sprinkler system. Waters the bushes, shrubs, grass at different intervals at the push of a button and can be changed by season or other weather conditions.

Take someone like Ann. It probably doesn't make sense for her now as she has most of her processes in place, but she manages a lot of data that is shared between different functions. Inventory, popular items, customer lists... Had she started a database when she started the business it probably could be far more streamlined today. Ann, I'm not recommending you change, just using you as an example.

For anybody here who is starting a business or even some that have existing businesses it may make sense to learn database design. It took me two weeks in a dummies book but I had some programming in college. MS Access has a user friendly interface, but I suspect you really need to learn to code to take full advantage of the possibilities. Where it really makes sense is if you need to manage a lot of data or if growth will make that desirable.

I actually think this is what made my business successful in spite of a mountain of other errors I have made!

[Spider]

Bill, I accept your analysis of the benefits of choosing to use a database in a business. I take it that being able to write one's own database program is your preference but why is this better than using a ready-made, off-the-shelf application? I'm thinking, if one has built one's own database app. it would likely be difficult for someone else (an employee or consultant) to work on it later. A common program by, say, Microsoft or Oracle (?), would be familiar to consultants for future support and maintenance.

As long as one is going to remain a small business, where the single operator owner will always control and operate the company's software, the need for other hands to mess with it is nonexistent. But, if one is planning a large business, as I am currently, the support and maintenance of any of the company's software should not be dependent upon one person. In this case, it seems to me, a proprietory or open-source database (and other software) would be preferable.

Is that poor reasoning, in your opinion?

[billbenson]

When you use several programs you are effectively using multiple databases. Say you use ACT for sales account management, Quickbooks for book keeping, some other program for inventory etc. None of these programs talk to each other. Go back to my automatic sprinkler analogy. It's like when the sprinkler only waters the bushes once a week and the grass three times a week, and your new flower bed daily, you don't want them to all turn on at the same time or you won't have any water pressure. A sprinkler timer will make sure that that doesn't happen. One complete system.

The manufacturer I represent is a multi billion dollar company. They have acquired various companies over the years. When you go to their website and type a part number into the search bar you will get different results depending on where you are on the site. It can range from product not found (which you never want) to the manual and product images. That's probably because rather than incorporating everything over time into a single database, they are using multiple databases that don't talk to each other very well.

I'll make another analogy. Suppose you sell nuts and bolts as a small business. Most orders includes nuts and bolts, not just nuts. If you rent two storage units in different locations; one for nuts the other for bolts; to fulfill an order you have to go to two different locations. Sounds kind of stupid right?

But that is what you are doing if you keep your data in multiple locations. But think of inventory. You sell three nuts and one bolt the inventory is kept separately if you have different databases. You can't track trends for how many people buy more bolts than nuts? You can''t tell if most bolt orders are shipped to Houston while the nuts go to Iowa farms.

So for any business where information management is crucial to the business, good database management is important.

Then take that a step further and add a website. A good website generates a ton of valuable data. Lets say you use wordpress for your website as an example. It collects all your visitor information, what comments and threads if its a blog draw interest. If you have a shopping cart attached, what customers are buying etc. But here again you have a completely separate database for a specific function that being your website. Along with that you have stats that your website gives you like visitor IP's, browsers used, most visited pages, time on pages etc. Again all important data in different places.

So, ideally you want all of this data in one place. That way you can generate any report you want. Say you want to know what repeat customers you have from Michigan and what they bought. That would take information from your website stats, your customer database, and your products database. That's an odd example but you get the idea.

While all of the above may not be practical, the more information you put in one central place (database), the more information you gain about running your business.

The best time to start this is when you are starting your business. The worst time is when you have a large business with a mess of databases. The multi billion dollar company I represent is an example of that.

And that is only scratching the surface of what you can do with information. Custom web pages for your customers from Michigan etc...

Again, it doesn't really apply to businesses that don't need the data, but all will if they grow beyond a certain point.

[Zodcole]

Makes me never want a white collar job.

[greenoak]

our databases are crucial too....but so different from w hat you need.....some part of our crew probably knows 80% of our database in a face to face way...we are small and have face to face with our customers...and our business changes...for us everything depends on them, the buyers, coming to us, not us selling to them on line or arranging things on line...
..we have 3 parts....3500 on the snail mail .....almost 25oo on the email....and almost 2100 on the facebook data base.....the facebook part is about 2 yrs old and the snailmail part has some folks who have been on for 20 yrs!!!
i would love to hear w hat you would do with this database....i value our data a LOT... even tho its pretty primitive ... we only have 2 categories....retail and wholesale...

[billbenson]

Its hard for me to say, Ann as I don't know your business. The beauty of what happened to me is I started from day 1. My programming skills weren't that good but all I needed was a quote generator. As my business has grown, so has the data I retain and I continually improve my database.

I also don't use social media so I don't know how you can get the the information there into your own database. It sounds like your information is being hand entered. Perhaps in an excel sheet?

Let just say you have your information in excel sheets. Say your snail mail. has columns for customer name, address, phone, what products they like, etc. Is that same information duplicated in perhaps the email database. Are they on facebook as well. Somehow you want to get all of your information in one central database. That way you could stagger promotions based on what they like by snail mail, email etc.

If you include inventory and assign categories to your inventory, and incorporate that into your database, you could do mailings based on inventories and new products to people who like those products. When you do your face to face in the store, ask them if they would like a card or email when some product comes in. Maybe carry a tablet with you in the store to enter that information.

Because my initial needs were so simple, it was easy for me to start. I didn't have any data and just needed to write quotes. I've been at this for over 10 years now to slowly grow the db as I have time. If you do something like this, go in baby steps. Maybe get your email and snail mail combined into one database. Then start modifying the db so it has all the information in it to generate all the reports you need or even print out the snail mail you want when new inventory comes in etc.

Your problem is you are so far along that there is a lot of work here to make it productive for you. It's something to start when you are just getting started.

[Spider]

Bill, I get the potential problem with multiple databases and that it is the likely outcome of using multiple applications. I wasn't contemplating that. When I said, "..it seems to me, a proprietory or open-source database (and other software) would be preferable..," I meant a single proprietory database that all other software would feed into.

Maybe that would mean buying all one's software from a single source - like going totally Microsoft - MS Office, MS Excel, MS Project, MS CRM, MS ERP and so on.

Or, perhaps, various sw apps from different sw companies can be fed into a single proprietory database?

I am just concerned that a specially-written app would not be wise for a large organization because if the app-writer suddenly falls under a bus (or just wants to quit) bang goes one's company.

No?

[billbenson]

Yes, and you will probably need to do that in some form. For example, I don't have the money to build a custom ecommerce site. I wrote software that automatically downloads the info from the ecommerce site to my master database.

For a large organization, you would probably have a web / IT department so no one person is a key point of failure. If you are large, but farm out database management, I'd make it a joint project between more than one person. Perhaps have one person write the spec and another organization implement it using standard coding practices.

My post, however was pointed mostly at the people starting up or those that are pretty small still. If their business model is to grow, and they anticipate have substantial data over time, its worth the effort. Particularly since you can go in baby steps at that point.

My business has grown to a point where I get a large number of phone calls and emails a day. If I don't quote, it never becomes an order. I couldn't do it without my quote generator, so in and of itself it was worth learning it. Yes you could do that function in quickbooks, but as explained above you can't use that information for anything described above that quickbooks doesn't natively provide.

[greenoak]

thanks bill....we a re pretty streamlined.we record date and wholesale and retail thats about all....we delete by date sometimes..and not interested in adding too much, unless there was a big reason.... ...it all kind of works together and we sure dont want to coomputerize our inventory.....we are all about getting them to come here to the store....kind of general...
ours is simple but super valuable....to us....actually we could be a lot bigger and still use this simple plot...

[billbenson]

I think, Ann, if you had started from the beginning with what I am suggesting in this thread you would be glad you did. But you now have all your processes in place and don't seem to be interested in adding new stores etc., so its certainly something I wouldn't recommend for you unless you perchance had a relative who was a programmer or the likes. Your ROI wouldn't make sense.

I do think your business is a good example of one that would have made sense if you started it at the beginning of the business.

[Spider]

This conversation is so timely for my new business (being discussed here (http://www.small-business-forum.net/starting-your-business/5591-starting-substantial-business-bad-economy.html).) Thank you for starting it, Bill. In fact, the delay I am encountering right now is because of this very point - I cannot find a public speaker I know and trust who is sufficiently familiar with computer technology to be our technology partner.

I really want someone to be able to lay the groundwork, as you describe, while the business is being structured, rather than try to come back later and install an integrated databased system.

We will have great need over time for all sorts of technical wizardry. My initial model will have each partner responsible for certain aspects of creating seminars and running the company. Each partner will actually do the work at a rudimentary (holding) level until money is flowing well enough to hire people to do it. The partner will then be the person responsible for that part of the operation. Each partner will grow as the company grows.

I mentioned Microsoft and Oracle because I know they are two companies that have company-trained but independent associates we could hire. If the associate we hired disappeared, we could easily switch to another associate who would continue without missing a beat, because they would all be expert in the same software. I'm sure there are other companies with the same setup.

This is probably a more expensive route to follow but sounds a lot safer in the long run. Maybe more efficient, too.

[billbenson]

Remember, Spider, this is for lack of a better phrase "technology-marketing". They aren't separated anymore. Functions in business management need to have the dual skill. Its pretty rare still. Which is why it brings such opportunity to the companies large and small that take advantage of it.

[Spider]

In thinking about this further, I am realizing that one person to be responsible for all our technology will be expecting too much. I'm sure we will discover more things that are technology-driven as we progress, but even now I am expecting -


Event DJ, for background music, musical clips and intros, and miking of presenters for larger events.
Video all seminars, edit and create CDs for sale, then compile into home training courses.
Video clips for advertising and promotion.
Create and maintain YouTube channel, and other video services.
Powerpoint projection, or other, for onstage presentations, including whiteboard and flip charts.
Website as brochure, for search marketing, etc. and to accept registrations.
On-site ticket sales, product sales, scheduling and payments.
e-Mail list creation and marketing.
Database creation and maintenance for all the above.


True, much of this is future requirements, but even at the first seminar we will have to be up and running on several of these. In fact, this is where most of our upfront cost will be. It's quite a daunting list!

[billbenson]

A lot of what you mentioned can be farmed out. Remember that most of what you mention is marketing related. To manage the above you want a marketing person that understands the technologies, not the reverse. That's why so many companies have lousy websites. Websites are frequently under control of the IT department and the marketing people have no input and frequently no knowledge of what can be done through technology.

That's why the databases I mentioned in this thread are so important. They can be used for both process automation within your company and marketing. Knowledge of technology is no longer a skill that can be ignored by business owners and managers.

Everything I've mentioned so far in this thread can really be farmed out. The problem is you need to be able to write a good spec. For me being able to write that good spec has been an iterative process. I'm constantly thinking of improvements I can make to my database and programs.

[Spider]

Good point about getting a marketing person who understands technology rather than a technology person who understands marketing. Not sure which is the most rare overall, but I know neither! Nevertheless, I must find a solution. I'm going to start searching for a marketer who would be interested in learning IT.

Going back to databases - am I correct in understanding that your advice about being able to write "database" is more about getting the data out of it than putting the data into it? IOW, one can use a proprietory database application for data colletion and storage but ought to be capable of writing data extraction code for creating special reports? (which is called data mining, I suppose?)

[billbenson]

Think of a database as a bunch of excel sheets that can talk to each other. You have an excel sheet with customer information, another with order information, another with inventory information, another with product costs and prices, another with web site information such as visitor or customer location, all your keyword info etc, etc.


Going back to databases - am I correct in understanding that your advice about being able to write "database" is more about getting the data out of it than putting the data into it? IOW, one can use a proprietory database application for data colletion and storage but ought to be capable of writing data extraction code for creating special reports? (which is called data mining, I suppose?)

A proprietary db would be something like Quickbooks, ACT (used to be a popular account management program) etc.Generally these programs generate reports from the data entered into them. They also can usually export their database into an excel format or a cvs file. What they can't do is talk to each other. So you can't take information from ACT and Quickbooks and easily generate a report with some data from each. One central database can do that. Kind of like only being able to access information on half of the excel sheets in my first paragraph.

As you grow, at some point the amount of data you have is either unmanageable or gets lost. Kind of like not looking at your site stats to see what keywords are popular. Having all the data in one place allows you to generate reports or take actions on all of the data. It also allows for task automation ie not entering the same information into different databases frequently by different people.Or in a small business you may be manually entering the same information into different programs.

Here's a test. If you are entering information into a pc more than once, its one time to many! Wasted time. If you have information you aren't using; why? If there is marketing information available to you that you aren't saving; why?

Everything in the previous sentence is lost income.

[mailorder]

I use MS access for my database and if you setup your database right and know how to setup queries you can do about anything with it.

To pull my sales off my websites and print my order forums...
Log into website #1 and import data to access
Run a query, load the blue letterhead, and print. This gives me the packing list for each costumer.
Run a query and load shipping labels and print.
Log into website #2 and import data to access
Run a query, load the green letterhead, and print. This gives me the packing list for each costumer.
Run a query and load shipping labels and print.
Staple the shipping label to the packing list for each costumer
It's about 20min start to finish with my slow printer.


With the one database I can pull up about any information you want. I have about 100 queries already made and getting that information is just a few clicks away. If I need something else I spend the 5 to 20 min to set up a new query. If I’m pulling information that I will not use again I’ll do a quick query and export it into excel to play with it.

Quick books back end is a database with the front end being much easier to use than queries. The only thing that quick books dose that I would not want to do with access is import my banking accounts to it but I don't know why people do it anyways. If I had hourly employes I would take a good hard look at quick books for it's payroll but even that can be done with access.

I type information into my database for 3 reasons. Adding new products, changing price, or changing the quantity in my warehouse as I get shipments in or take out “samples”. After I get in shipments I can import that information into my shopping carts on my websites with a few clicks.


bill
If you used access, my SQL, Oracle, bill-costume you still need to be able to setup the database. If you don't setup the database right then everything is worthless. I paid someone $1,200 a few years ago work with me to update / optimize my database to fit my needs. It was the best $1,200 I ever invested and if my business levels keep increasing I'll be paying about $3,000 to get someone to come out and optimize it again.

[Spider]

...you can't take information from ACT and Quickbooks and easily generate a report with some data from each. One central database can do that...Yes, Bill, I got that. I guess I wasn't clear. When I said "proprietory database" I meant one, single, proprietiory database like an Oracle database, for example, into which the various applications (accounting, order fulfillment, project management, etc.) would feed data.

Presumably, when one sets up Quickbooks, you establish the data fields, field names, field order, and so on. When you set up Microsoft's CRM, Agile Project Management and other applications, you do the same. If all the separate applications are set up with exactly the same db format - same field names, order, etc. - these individual databases could be routed or copied to a central database, couldn't they?

Here's what I like about that setup, if possible. While having one database including all the data clearly has advantages, if somone in accounting hits a wrong button, or a bug screws up the accouting database, then CRM, Project management, seminar scheduling, e-mail marketing and everything else can proceed without interruption, if they have their own databases.

Can't the individual databases be set up so they have the same format as each other so they can be merged when cross-application data reports are wanted?

[billbenson]

MS access would be a good option for a lot of people because of the ease of use. The downside is you can't interface with php / mysql websites such as wordpress sites. Also, if you want to farm out the programming as you did, there are a lot more people that know php / mysql programming.

You can do what you did and have someone else set up the database structure. If you learn how to do it yourself, though, you will probably also find more creative ways to use the data

.
Can't the individual databases be set up so they have the same format as each other so they can be merged when cross-application data reports are wanted?

You can do it that way, but what if two departments are entering data in the same field on two different databases. Say sales and shipping for example. How do you update the master database from that. You don't know which is the most current or correct data. As in my example of the manufacturer I represent's web site. Depending on where you are on the site you will get different product search results. It's because there are multiple databases. That is something that you really want to avoid if possible.

[mailorder]

When it comes to My SQL I could set up a database and import the information I already have.
You can use PHP with access.
I don't use word press, but you can use it with access.
You can also use quick books to get data in and out access or My SQL.

If I would update my database I would go to SQL server 2008 for the back end and setup access or some other program to run on the front end. The only reason I see to upgrade is if I hit a size limitation or there is something I need in a different program.


Also, if you want to farm out the programming as you did, there are a lot more people that know php / mysql programming.
Yes I farmed out optimizing my database to a better layout. The same database layout would work in My SQL, SQL server 2008, or what ever database program you would use on the back end.



You can do it that way, but what if two departments are entering data in the same field on two different databases. Say sales and shipping for example. How do you update the master database from that. You don't know which is the most current or correct data.
If it was all on one database what would be the chances of this happening?


Depending on where you are on the site you will get different product search results. It's because there are multiple databases. That is something that you really want to avoid if possible.
There are not many reason to have multiple databases for one company. But this all comes down to the level of training and the ease on applying the technology.

I use access because it's something I learned in high school in 1998 and something I learned more about in 2001 in a computer 101 with a college class. It was also something that my wife learned in a computer class when she was getting her RN. I have played with SQL server 2008 and a good amount of what I know from access transfers over.

[Spider]

... what if two departments are entering data in the same field on two different databases. Say sales and shipping for example. How do you update the master database from that...I don't know but I would expect the person we hire when we get to that stage will have the answer.

This conversation is very enlightening. I knew there was going to be a lot to be considered, but everywhere I turn there is more and more technology. Splitting it into two is not going to suffice. By the time we get up there with Dale Carmegie Training (2,000 trainers around the world - 10-day and 14-day courses in many cities every night of the week, 1-day, 2-day and 3-day seminars....), all our partners will need to be quite expert in the technology.

This has changed my thinking of how we must set up our new company. So as not to disrupt this database thread, I will air my thoughts in the new business thread (http://www.small-business-forum.net/starting-your-business/5591-starting-substantial-business-bad-economy-11.html#post56395).

[billbenson]

@mailorder

Interesting, I didn't know MS had integrated MySQL. That would make Access an attractive solution to a lot of people.


If it was all on one database what would be the chances of this happening?

My point to Spider is the best design is to have one database. You could set it up with a restore to a prior date sort of function like Windows has if you like.

It sounds like you have done what I'm recommending to others here - if they have a lot of data. And your solution is much simpler than what I do, although coding is still required to do much of what I do.

[mailorder]

@mailorder

Interesting, I didn't know MS had integrated MySQL. That would make Access an attractive solution to a lot of people.
I would not want to use the word integrated. But you can export from access and import to My SQL to transfer your database. You can also use My SQL for the database and use access to manipulate the data, run queries, or the nice looking pages you build in design view to type in information (I don't use them).

One of the biggest things is the design of the data base. If it's done right then in one data base program it will work in a different data base program. You might need to go back and optimize your keys or make a few changes.

I have more thoughts but I got over 400 orders I need to pack from my black Friday sale and my automatic stapler is not working right.

[billbenson]

Mailorder, I'd be interested in knowing more about what you are doing. It seems we both do online sales. I'm not likely to start using Access, but it sounds like a much better solution for most of the people here than doing everything in code which is what I do.

[mailorder]

bill
I'm not trying to say that access is what everyone should be using. But also saw a lot of bad information being posted about databases.

With the amount of stuff out there I don't see how custom programing something is better than whats already out there. The big issue would to figure out what you wanted to do and then sit down a see what programs will do it for you.


P.S. Anyone want to work all night packing boxes and help me take them to the post office in the morning?

[billbenson]

bill
I'm not trying to say that access is what everyone should be using. But also saw a lot of bad information being posted about databases.

With the amount of stuff out there I don't see how custom programing something is better than whats already out there. The big issue would to figure out what you wanted to do and then sit down a see what programs will do it for you.


But you in fact have a custom database. Thats what the guy you paid 1200 dollars to did if I understand you correctly. He created the table structure with all the appropriate fields in a number of different tables for you. It sounds like you then wrote queries through the Access user interface.

One of the biggest errors you can make is using multiple databases as an organization. You can get away with it when you are small but it will create problems, become inefficient, and limit what you can do as you grow. It is never good to have the same data in two different locations. That's really from computer science 101.

[billbenson]

Here's a pretty good thread on a tech forum explaining the difference in access, php / mysql etc. MS Access vs MySQL - Access World Forums (http://www.access-programmers.co.uk/forums/showthread.php?t=190649)

[bacterozoid]

Edit: The first response to the forum post that Bill linked above is very good. Since I rarely use Access, I may be off on a few points. I'm generally correct, but I'm only an expert on MySQL.

Warning: This turned into a rather long post. Just trying to offer some of my thoughts since I deal with this stuff a lot.

Just want to weigh in here - I work full time as a PHP / MySQL developer. I'm also familiar with Microsoft SQL databases and have used Oracle for a few months.

The database platform you choose depends on a number of factors. Here's my opinions on the three most common:

- Oracle has expensive licensing and I don't believe it's worth it.
- MSsql is the most common to a lot of people. It's what you use when you start up MS Access. You can also have MSsql servers that you can use programming languages like PHP with. MSsql is free, but the server does have a cost. You don't need a server, but if you want to integrate with databases on other platforms or servers, you'll want this.
- MySQL is my weapon of choice. It's fast, free, and the community support is excellent.

I completely agree that getting your data into one place is important. I want to clarify, though, that you've been misusing the word "database". It's critical to use the same platform (mysql, mssql), but you may have separate databases inside that platform. The tree is:

> Platform (mysql, mssql, oracle, etc)
> Database
> Table
> Row
> Column

Choosing your platform should be based on what you (or your employees) have experience with. A MySQL database is very easy to set up (even on Windows) and works awesome when combined with PHP. If you're more comfortable using Access and setting your MSsql database up that way, then that's fine too. One major difference between MySQL and MSsql is that MSsql has Access which can build some nice paper reports. MySQL has PHPMyAdmin but you're still limited to getting "raw data". You would have to manually format it or build a custom PHP script to get the same result.

If you have a bunch of different applications, then the other issue (as discussed) is bringing all of that data in one place. Let's say you have Google Analytics to track website visits, an online shopping cart using MySQL/PHP, and QuickBooks for invoicing, payments, and payroll.

Let's say you want to bring all of that into a MSsql (Access) database. It will have three databases:

website_statistics
shopping_cart
quickbooks

Google Analytics has an export feature. I've never used it, but you can probably export to Excel and then import fairly easily into Access. Run this export manually once a week.

Your shopping cart will be a little more difficult. If it doesn't have a data export, you may have to write a PHP script to manually extract the data you want. This will take some time, but once you're done, you can click a button or have it run automatically once a day to bring in all of your online sales.

Finally, QuickBooks. This is harder than most people would mess with. QuickBooks does not give you direct access to the database. It has an API. To use it, you would have to write a program that sends requests to QuickBooks and asks for data. It would be very complex and take a long time to perfect, but it can be done.

All of these things require a lot of customization. Nobody's system is the same, so unless you have time to learn about it (and it's certainly not hard - it just takes some time), then you will have to pay someone to do this for you. In the end, you have to evaluate what you have and whether or not it's worth it.

I see a lot of value in having all my data accessible from one place, so I agree with Bill. I just wanted to offer some insight. I hope it was helpful and makes sense.

[Spider]

That's very interesting, BZ. This answers my question about the advantage of having separate databases for different aplications all feeding into a central database. If I understand you, you describe separate databases that are, in effect, one (because they are on the same platform.) Like an apartment building (the platform) with several floors (the databases) and multiple apartments on each floor (the tables) and different rooms in each apartment (the rows and columns) and furniture and accoutrements in the rooms (the data.)

I presume the databases can talk to each other, share common information and update each other, like the customer database automatically updating the shipping database when the shopping cart has a new purchase from an old customer with a new street address. And update the e-mailing list when said old customer indicates a change of e-mail.

All this suggests to me that a new company would be better off by purchasing a completely integrated package - accounting, shopping cart, mailing lists, CRM, project management, etc. - from a single supplier. All the customizing being contemplated in this thread is obviously quite necessary for older companies trying to pull their stuff together, and for companies with special requirements, but surely standard integrated packages of various sorts are available, are they not?

[mailorder]

Yes I hired out someone to optimize my tables it to make it run smother and so I could do more with it in the long run. I know how to set up tables, but at the same time I have only had a limited amount of experience do it. The larger the tables in size and in numbers the more important it is to have them optimized. Last time I counted I had over 40 tables in access and and if you added all the tables together it's right around 1.8 gigs. Since I can use PHP, Quick books (I don't use it), and other programs to use my access databases there is no reason to upgrade till I get close to hitting the size limits.

I know basic PHP but I chose not to use because I can do so much more with my data in access.

If you just use quick books then yes it dose not give you direct access to you you data. But you can also set it up to use the data in from the databases flavor of your choice.


Spider
Yes it would be better to have everything integrated into one database.
I would not go with purchasing integrated package. You would still need to do a lot of customizing as time goes on and it might be better to start from scratch with your tables.

[bacterozoid]

Edited for clarity.

Spider - nailed it with the apartment analogy. I wouldn't even merge multiple databases into one database - I would just leave them all separate. The only point I start worrying is when I have the same data duplicated between databases. Also, in general: you should have one database per application.

Let's say I have the following databases:

my_company
table: customers

shopping_cart
table: products
table: customers

Now I have a problem. There's no way I can go to the my_company database and say "how many products did customer X order?" because I have no way of knowing which of the rows in the customers table of the my_company database lines up to the same row in the customers table of the shoping_cart database. However, if I use the primary key of the customers table in the shopping_cart database inside the customers table of the my_company database, then I could do that. This won't happen naturally - you'll have to customize it. When creating the my_company database, you'll have to add a column called "shopping_cart_customer_id" so you can link them. Then, using the power of joins, you can get the data you need.

There's also no way to automatically update databases. I think you probably know that, but I'm mentioning it just to be sure. If you want your shopping cart to update your my_company database when the customer changes their address, you'll have to write a function that triggers when that event happens. It then becomes automatic...you just have to make it do that.

As far as a single package to do it all: probably not happening. A lot of web hosts do offer a ton of PHP/mySQL scripts that you can use on your website. That can go a long way towards using a single platform. Unfortunately, as businesses grow and adopt new software, things get complicated. Maybe you have to buy an inventory/ordering system one day that uses MSsql - now you have to find a way to integrate it with your mySQL shopping cart. I would do my absolute best to keep everything on the same platform, but it's tough sometimes.

The best systems are custom systems. Black box solutions will only go so far; that doesn't mean they aren't useful. Starting off with a generic shopping cart system is quick and easy and can make you money fast. As soon as you need to add a system to the mix, finding a way to integrate it can really pay off...but it also tends to be expensive.

[greenoak]

for the contrarian view.... total computerizTION AND deep into the inventory databases arent right for everyone.... for instance us....our inventory is huge and very outside the box... computerizing it would be a huge decision costing a new employee and my judgement is that it wouldnt be worth the trouble.....
for instance yesterday i spent 2500$$ and it filled 2 trucks and my big suv....the reciepts were auction tickets that might call a group of 20 pans , graniteware.or a pile of corbels a room..... i have probably 1000 items in the buy..... some will b e sold out in the parking lot during the unloading....some will be bundeled...some will be used in repurposing projects...some is just junk for the free pile or the half price room.... there will never be another group of stuff like this....there are no bar codes...lol.....i know what i paid and could easily price a pile of it to a buyer...some was free...like a box of 20 items and the value would be all in one of the items....SEE WHAT I MEAN? there would be no reason to log in all this info...it would take so much time and next week it would be a whole new pile..... then in the new products i buy...i try different stuff every year...so last years info wouldnt be too important..... i know tons of stuff in general about my inventory and whats sellling and not..... but i have no need for a perfect record.....it just wouldnt fit our operation...and would slow it down a lot....when im unloading the people want a price and want it now.... which i can do....
we are very modern and very computerized in lots of ways....but not for plugging in our big inventory....10,000 sq feet...
some businesses just dont fit the general advice.... i like the idea of KISS...KEEP IT SIMPLE... especially if it works.... we have a wonderful database of customers...and we keep it simple too.... no special emails...they all get the same one...once a month and when we have an event.... its as much as i want to do the work for...and its a lot of customers...

[billbenson]

bacterozoid - I have an ecommerce business. I did it differently than what you suggested. I both do telephone orders and online orders. For my online site I added three major tables; my_product_database, my_orders, my_orders_items. These also run locally on my pc via Linux and Xampp

- My_orders - has customer information (I need to break that off into a new table), my order number, general order info, whether its been invoiced or paid.
- My_orders_items - These are the products that are ordered, my order number (this is in both tables), quantity ordered, costs, prices, etc.
- My_product_database - This has information on the products I sell. My manufacturer doesn't give us a complete product listing or price list. They have annual price increases. As I call them for product information on products I don't already have in the db, I update this database. It's about 4000 products and descriptions now.

With these three tables I can write orders locally on my pc. Locally I use mostly for backup and writing scripts. So between these three tables I have all my order and product info, can write quotes, etc. What it also allows me to do is change shopping carts easily since all the cart information is there.

I started doing this from a dummies book I think in 2001. So I made a lot of mistakes, but as I saw them, I altered a table to accommodate them. When I first wrote the tables I assumed I would need table columns that I didn't think of so I included future1 to future10. Even though I believe that's not good design practice, its worked out as every so often I'll need a report or something that would require a field I didn't think of earlier. As I mentioned above, things like the customes should have been in their own table so I've made some structural errors. Its a days work for me to create a new table, move the customer info to that table and alter the existing table and scripts that use it. Actually two days to modify all of the scripts.

So in summary, its one click to download my online tables to my local pc for design and backup, I'm not locked into any cart and in fact am in the process of changing carts. I keep my tables within the ecommerce sites database. I can easily use my master products database is used to update pricing etc for the online site.

So my question is, why would it be better to put these in separate databases rather than tables within one db? Also I never alter the carts db. I'll always use a separate table if something is required. I suppose if I inventoried and shipped it might make sense, but even then, that would need to interact with the my_orders table. I've written hundreds of special scripts as will.

Incidentally, this is why I think its important as a small business person to start learning this from the beginning. Your needs are pretty low so its not hard to get started. Over time your needs grow but your knowledge grows slowly over time. This is true whether you use and access solution or a code solution as I do.

[Spider]

Back in the day, I had a fantastic hi-fi set up, using different components, because Pioneer made the best turntables and Sony made the best amps and Revox made the best tape recorder - I designed my own system and I built my own speakers (including making the cabinets).... But that was after I started with a single integrated set up from one manufacturer. Now-a-days we are back to fully integrated packages. That's all I have now, too, because all I want is background music, now. Unless one is professionally involved with IT, or has an enthusiasm for the subject, fully integrated packages are the best - because they are fully integrated.

Yes, I'm sure, just like hi-fi back then, different manufacturers of IT components excel at making different components, and an aficionado can build a far superior set up by combining the best from different manufacturers. But if all you need is background music, all that effort is not necessary to create a good working system.

That's what I believe, anyway. There is IT for IT's sake, and there is IT for business users. You guys are in the first camp and I am in the second. Unless you can come up with a sound business argument for not using an integrated system - which argument I fully welcome - an integrated system from one manufacturer will be best for my new company, I am thinking.

[billbenson]

Back in the day, I had a fantastic hi-fi set up, using different components, because Pioneer made the best turntables and Sony made the best amps and Revox made the best tape recorder - I designed my own system and I built my own speakers (including making the cabinets).... But that was after I started with a single integrated set up from one manufacturer. Now-a-days we are back to fully integrated packages. That's all I have now, too, because all I want is background music, now. Unless one is professionally involved with IT, or has an enthusiasm for the subject, fully integrated packages are the best - because they are fully integrated.

Yes, I'm sure, just like hi-fi back then, different manufacturers of IT components excel at making different components, and an aficionado can build a far superior set up by combining the best from different manufacturers. But if all you need is background music, all that effort is not necessary to create a good working system.

That's what I believe, anyway. There is IT for IT's sake, and there is IT for business users. You guys are in the first camp and I am in the second. Unless you can come up with a sound business argument for not using an integrated system - which argument I fully welcome - an integrated system from one manufacturer will be best for my new company, I am thinking.

Back in the old day and probably still today, all of your stereo components had standard interfaces and talked to each other. Proprietary databases like quickbooks don't do that or at least make you jump through hoops to do it. Your stereo system is a bad analogy.

Although using your stereo example, when you build your own database that suits your needs, you are doing something like your old stereo in that you are taking the components you like, hooking them together and getting exactly what you want!

[billbenson]

Spider, I think one of the things your having trouble with is understanding how databases work. I couldn't find very many decent basic descriptions but look at the flat file vs relational databases at Database 101: An introduciton to Databases (http://www.catbase.com/Tutorials/database-101.html)

[mailorder]

I under stand why a company don't keep track of there inventory in database and that every person likes different setups that work for them. Different types of work need different setups of databases.


For the ones with a database how big is it in megs or gigs?
I'm asking this because there are size limits on size and the larger it grows the more the tinny issues show up. I'm thinking that access have a max table size of 10 megs and total data base size of 2 gigs (My SQL is the same depending on install).


Table setup... There is way to much to list it all but will try to give an idea on how I have mine laid out. I'm only listing the primary 7 tables and more or less what I keep in each one. There are a bunch of smaller tables that I did not list.

Contact table. Name, address (billing & shipping), email, costumer ID # (biased off unique entry), phone number, date of last order,

Product table. Name, current cost, max cost, manufacture, supplier #1, supplier #2, sale cost, order notes, reorder at, current inventory, product ID #, Pic (hyperlink), tiny product description.

Combined product tables (I do a lot of sets of items). Name, current cost, max cost, sales cost, product ID 1, product ID 2, product ID 3, product ID 4, product ID 5, product ID 6, current inventory, product ID #, Pic (hyperlink), tiny product description, cost.

Order table company #1. costumer ID, product ID (each one gets it's own column (format yes/no)), order ID, credit card verification number, time of order, date of order, time shipped, date shipped, USPS tracking #, USPS confirm #, total price, shipping cost.

Order table company #2. costumer ID, product ID (each one gets it's own column (format yes/no)), order ID, credit card verification number, time of order, date of order, time shipped, date shipped, USPS tracking #, USPS confirm # total price, shipping cost.

Supplier table. Name, address, phone #, list of all my product ID # they sell, there product ID #, items per case, normal cost per case, max cost per case, contact person #1, contact person #2

Supplies table. Supplier ID, product ID, amount ordered, date, order #, cost, shipping cost.


Tables like mine have lots of relation ships. I try not to have the same information more than one table tables but at the same time I try not have tons of wasted space. Each block in every data base will take up space. When your database starts getting larger you start looking at what your doing wrong (time to optimize) or you start looking at upgrading.

[Spider]

Yes, Bill, I read that page and can confirm that I knew all it contained, but that is probably the extent of my knowledge of databases. I first encountered the term, relational database, in the 1980s, if I remember correctly, when I was dabbling with CP/M-86 OS. Of course, they are much more advanced today.

I also confirm that I realize my hi-fi analogy is like building my own database that suits my needs, taking the components I like, hooking them together and getting exactly what I want. The point I was trying to make was I wanted the best from my music system and I was interested in topic - I used to read hi-fi magazines, studied component specifications, compared wow and flutter of new styluses and tape machines, frequency ranges, etc. I would sit and listen to my music - not have it on while I was reading - sit and listen. It was important to get as natural a sound as humanly possible.

I can promise you, I will not be sitting at my computer watching my database do its thing. Neither do I listen to music as I did before so a relatvely cheap integrated one-component sound system is adequate today. Of my IT, I want it as simple and failsafe as possible. It can be less than the best, less efficient and provide less than optimum reports. What it must not do is crash when I have 2,000 people in the hotel lobby waiting to be checked in in 15 minutes to see Brian Tracy!

[billbenson]

According to this link MySQL :: MySQL 5.0 Reference Manual :: E.7.3 Limits on Table Size (http://dev.mysql.com/doc/refman/5.0/en/table-size-limit.html) MySQL on Linux has a maximum table size of 2G. I couldn't find anything that mentioned maximum database size. My new site is a cart on Wordpress with 3500 products. The total size of the db is 60M. My largest table is 700k and that's a big table. Worrying about exceeding a maximum table size doesn't seem a concern.

As far as optimizing your db, I don't believe records are actually deleted in mysql but rather pointers don't reference that data anymore. There are ways to delete that data completely. bacterozoid, correct me if I am wrong on that.


Of my IT, I want it as simple and failsafe as possible. It can be less than the best, less efficient and provide less than optimum reports. What it must not do is crash when I have 2,000 people in the hotel lobby waiting to be checked in in 15 minutes to see Brian Tracy!

Spider, you do that by redundancy, hot standby etc. Just like web hosts that offer 100% uptime. They may have redundant servers in different cities. One server goes down it switches to the other. Using multiple databases proprietary or otherwise doesn't make you any more secure and creates problems for your IT department rather than reduce them.

[mailorder]

bill
With Linux I think it's up to the hardware your running it on. I have not used My SQL on Linux but to delete something I think you have to shrink / rebuild to purge the database of unused columns and rows.

My database grows by about 20 megs a year and I have about 1,500 products currently. My growth is because of order history witch I must keep per the rules of my credit card processor. I have used the information in my database a few times to fight charge backs, but I think this should be it's own thread.

[Spider]

...Spider, you do that by redundancy, hot standby etc. Just like web hosts that offer 100% uptime. They may have redundant servers in different cities. One server goes down it switches to the other. Using multiple databases proprietary or otherwise doesn't make you any more secure and creates problems for your IT department rather than reduce them.This conversation had me somewhat convinced that this wasn't necessarily the case...


...With the amount of stuff out there I don't see how custom programing something is better than whats already out there. The big issue would to figure out what you wanted to do and then sit down a see what programs will do it for you...


...I completely agree that getting your data into one place is important. I want to clarify, though, that you've been misusing the word "database". It's critical to use the same platform (mysql, mssql), but you may have separate databases inside that platform...That last is what I had originally envisaged when I said:

...I get the potential problem with multiple databases and that it is the likely outcome of using multiple applications. I wasn't contemplating that. When I said, "..it seems to me, a proprietory or open-source database (and other software) would be preferable..," I meant a single proprietory database that all other software would feed into...Following the terminology being used in this thread (and throughout the industry, I suppose) I ought to have said - a single platform that all other databases would serve under.

I think we are actually on the same page, Bill, because that it what you seem to be saying:

...For my online site I added three major tables; my_product_database, my_orders, my_orders_items. ..True, you have three tables in one database, but how different, really, is that from three databases in one platform? I'm thinking a table is a sort of sub-database, isn't it?

[billbenson]

There is a optomize_table command in mysql which appears to be analogous to defragmenting a disk. It's recommended for tables that have had a lot of deletions of data as new data is placed where the old data was so it's not in sequential order on the hard disk. That's all I have found relative to optimizing the database. Of course proper table structure makes things work better but I don't see how that can be an automated task. I did see something yesterday that talked about Access using short path names as part of optimization. But sounds like a windows thing to me. One of the reasons I went to Linux is I had problems with paths using linux / php / mysql on a windows platform. I couldn't get globals to work.

Anyway, this thread has gone way of base from my original intention which was to encourage people who deal with a lot of data to consider using databases as they will save a lot of time, add productivity, give many more marketing options etc. And its not that difficult if you start out in baby steps.

[billbenson]

True, you have three tables in one database, but how different, really, is that from three databases in one platform? I'm thinking a table is a sort of sub-database, isn't it?

Spider, go back to my earlier definition of a database. It's like a collection of excel sheets that interact with each other. If you have one excel sheet (table) in a database it is not a relational database which is what you want.

Lets define a platform. Lets call it where your host puts your database on your web site. Say your host allows you to have three databases. You may want to have one database consisting of 10 tables (excel sheets) for shipping and receiving, one more db with a bunch of tables for internet sales, and one more for quickbooks type of financial stuff. You can do that and easily move or grab data from one place to another or create reports using data from each. You could also put all of the information under one database. Essentially the platform is a place where you can have multiple databases and they can easily talk to each other. You can build it on your pc using access or apache / php / mysql as well. You can actually have them in different locations but that is beyond the scope of this thread.

What is not one platform are three different programs running proprietary databases on your pc.

[Spider]

Yes. Like I said, we seem to be on the same page.

[mailorder]

Anyway, this thread has gone way of base from my original intention which was to encourage people who deal with a lot of data to consider using databases as they will save a lot of time, add productivity, give many more marketing options etc. And its not that difficult if you start out in baby steps.
When I read your first post I thought you were trying to do something different. The more I read your post the more bad facts about what you could do with database programs.

[bacterozoid]

Hot topic :)

Bill, to go back a little bit to your original point: I think that learning about databases is very helpful for any small business owner. It's just like taxes or sales in that it takes time to learn it, there are benefits to knowing it, and you can dig as deep as you want to figure the stuff out. For me, databases and programming are easy because I've spent years on it...just like taxes are easy to any good CPA.

I think a good starting point is to sit down and think about what reports you want to have. Is it really that useful to mirror your QuickBooks database on mysql? If so, how many hours can you put into making that happen?

A quick note on optimization/speed:

I can only speak to mySQL size constraints - at work I use tables daily that are over 2 GB. Our entire database sever is 60+ GB. Bill, that link you posted was correct, but almost all Linux servers these days are using ext3, so 4TB is really your limit...and if you hit that then you've got other problems.

Database speed, however, should not really ever be an issue. Mailorder, I'm not sure what $3,000 pays for, but I question the value of that investment. Organizing your tables is important so you know where to look for data. If your queries are slow and you have less than 100,000 rows, then you simply need an index or you're doing your query wrong. What kind of database do you have? Does it run slow?

I've also never pushed the "optimize tables" button in mySQL. It might help somewhat, but again, you're going to get the best speed out of better designs and better queries.

[Spider]

I'm loving this conversation - even the technical bits I don't comprehend. But, I figure, if one reads enough of what one doesn't understand from people who do, one is bound to eventually find some sense in it.

Let me just say this, though, from the company management perspective - There will always be differences of opinion about what is best in particular circumstances. The manager's job is to make decisions and he can only make decisions based on advice from experts with his (the manager's) other, broader understanding and intentions for the company. It helps, of course, for the manager to understand what the experts are talking about. If the manager, the operatives and the guy that sweeps up at the end of the day are one and the same person, he may need to make time to learn how to program a database. If the manager is head of a 50-person company or larger, he needs only to understand it, not be able to do it.

I imagine that Jim McNerny (chief of Boeing) understands databases to some degree but I'd guess that he could not program one, or if he could, I'll bet he doesn't touch Boeing's databases.

[bacterozoid]

For my online site I added three major tables; my_product_database, my_orders, my_orders_items. These also run locally on my pc via Linux and Xampp

...

So my question is, why would it be better to put these in separate databases rather than tables within one db?


I'm reaching back a page to address this quickly. The tables you described would fit well into one single database. I start using separate databases when I have distinct products...like a shopping cart, a blog, and a support portal. Each of those applications will have a lot of their own tables. If you try to use one database for all of them, you could run into problems where two applications use the same table...then things would break.

If you want to be able to link data between applications (like your shopping cart and support portal), then you do a little bit of customization (maybe add a column here and there) to enable that sort of thing.

We've kind of covered this already and I think we all understand - just want to make sure we're all on the same page.

Spider - I agree...there's no better way to make decisions on this stuff than to have some understanding of it yourself. My boss manages us great because he used to be in my shoes. I think a lot of successful businessmen have some understanding of all of these pieces so they can make them work well together.

[billbenson]

I'm reaching back a page to address this quickly. The tables you described would fit well into one single database. I start using separate databases when I have distinct products...like a shopping cart, a blog, and a support portal. Each of those applications will have a lot of their own tables. If you try to use one database for all of them, you could run into problems where two applications use the same table...then things would break.

If you want to be able to link data between applications (like your shopping cart and support portal), then you do a little bit of customization (maybe add a column here and there) to enable that sort of thing.

That makes sense bacterozoid. Also thanks for your help in this thread. You've made some very helpful posts.

[TimPiazza]

Bill, I am totally with you on that. I'm a Mac-head. My first database program was called "First-Data" or something like that, I bought it from the developer at MacWorld in '89. FileMaker was my big breakthrough. Over the years, I have been able to build some amazing things with FileMaker.

I know enough MySQL to write scripts, but not enough PHP to automate them. I hire out my PHP work. After a 5 year hiatus from database programming, I couldn't find a sales prospecting tool that met all of my needs and rediscovered FileMaker. The advantage for me is that I can write a database that does what I want, and it runs on my laptop, my iPad, and my iPhone.

It used to drive me nuts that my co-workers were using Excel spreadsheets to do things that were better accomplished by a database program. Viva la difference!

Tim

[billbenson]

Tim, just FYI, there are a number of free applications that allow you to run Apache / php / mysql on your local pc. I use a program called XAMPP. Its a one click install pretty much. I'd stay with First-Data though if it's working for you. As mentioned above, apparently there is a way to run MS Access with a mysql database. If that is true, I'd opt for using the MySQL backend as there will be things that you can do in code that you can't with a GUI front end. Using MySQL for the backend will give you that option down the road.

I also put my database both locally and online. Online because I can use it from any pc. Locally because it acts as a backup and I'm not dead in the water if my internet connection goes down. Its also faster.

Again though, using any database will prove valuable over time for most businesses

[TimPiazza]

Bill, thanks. Just for the record, I'm using FileMaker Pro to build custom database apps for my own use. I can run FMPro on a server with web access if I want, there's a workflow tracking system I wrote a decade ago that is still in use with one of the major appliance manufacturers.

Running php isn't an issue for me. Macs have a built-in Apache server, I can run php right on the machine. I can also install MAMP, as you suggest. I also have 3 servers for my business and personal websites on Linux. The problem is learning php.

I use FileMaker because it's a tool I know, I have used it from the beginning. These days, I need to concentrate on selling services rather than acquiring new skills. I'd love to master php, and javascript, and jquery, and ruby, and objective c. But my wife is a social worker and that won't pay the mortgage. :-)

Tim

[MyITGuy]

apparently there is a way to run MS Access with a mysql database.

Linked Tables via ODBC connections =D I use it to connect to multiple data sources to extract/manipulate data that my clients need.

I didn't get a chance to read through this entire thread, but definitely get the concept. The only thing I have to say on this subject, is it worth your time? I say this because I have expertise in programming and SQL database back ends, but I'm not going to go out and develop a new application/system from the ground up when I could find something that can do what I want it to do, or when I can pay someone else to do this task for me.

As a business owner you really need to put a dollar amount on your time and ensure that you're not working on something yourself that can be purchased off the shelf or done cheaper by someone else.

[billbenson]

Linked Tables via ODBC connections =D I use it to connect to multiple data sources to extract/manipulate data that my clients need.

I didn't get a chance to read through this entire thread, but definitely get the concept. The only thing I have to say on this subject, is it worth your time? I say this because I have expertise in programming and SQL database back ends, but I'm not going to go out and develop a new application/system from the ground up when I could find something that can do what I want it to do, or when I can pay someone else to do this task for me.

As a business owner you really need to put a dollar amount on your time and ensure that you're not working on something yourself that can be purchased off the shelf or done cheaper by someone else.

It depends on the business. A lot of people here are small businesses and intend to stay that way. Even if they have a couple of employees, outside sales people etc they can get by just fine with quickbooks, a management program for the outside sales people and contacts.

But lets say its a business that grows and has a lot of data. Clients, orders, online information, inventory, etc. At some point, making excel sheets to put into quickbooks or import from program to program becomes unmanageable. You end up with duplicate information in different databases (applications) that don't talk to each other. You have a mess.

Add to that the fact that most businesses today should have a website. For a good portion of the businesses, the website is a large selling entity. If you have a single database that is web compatible, you can do things like present custom screens and information to the visitors. If they come to your site from Florida, don't try to sell them a snow shovel. If they last searched for a snow shovel, show them snow shovels. You get what I mean.

I doubt anybody that has read this thread is going to try to learn php. But they can start from the beginning with a mysql db and a gui front end (at least earlier in this thread a poster said that access can be set up to work with mysql). That can prevent the problem of multiple proprietary applications / databases and also allow them to bring in a programmer whey they grow to a point that it makes sense.