Database Design

msound

Hey Hey! I've decided that I would like to begin development of an open source online accounting system. Basically just something that could track inventory use, purchases, receivables, sales, etc. etc. The PHP portion of it should be easy since it's really only going to consist of simply DB queries and some basic arithmetic.

The only thing I'm unsure of is how to store the data in MySQL. Let's assume for a minute that a single company has several different locations. Each location should have it's own accounting information that is unaffected by the others.

Assuming I create tables like: Inventory, Purchases, Receivables, CashSales, etc.
Do I a: just create a Table Field named "location_id" and make that a foreign key that corresponds with the primary key of a Locations table? Or b: Simply give each location it's own Database, and then each database would share an identical table structure?

I know best practice would probably say to keep all of the data in one DB and use a location identifier to sort through the data, but one of my goals is to make location completely independent. If one of the MySQL tables becomes corrupt, all of the locations would then be unable to submit their reports.

If I use a separate database for each location, then a corrupt table would only have an impact on the database it's under. The rest of the company would be able to continue working as usual.

So what do you think?

MarkR

In general, if you have several objects with the same schema, they should really be one object.

Having fewer objects in the database is going to make things easier. Fewer bigger tables is usually better than more smaller tables. It makes your code smaller and reports easier to make.

If I use a separate database for each location, then a corrupt table would only have an impact on the database it's under. The rest of the company would be able to continue working as usual.

However, that should never happen. If it does, you have bigger problems to worry about.

Mark

msound

A buddy of mine hosts a phpbb forum that gets a fair amount of traffic. He called me up a few times saying that a DB table had been corrupt, and I had to write a quick php script that had to run a repair of the table name. That just kind of makes me nervous because if a table occasionally does become corrupt, all of the locations could potentially be affected.

On newer hardware, let's say a 3GHz P4 or Xeon w/ 1GB DDR 2 RAM and an SATA2 hard drive, how many entries in a DB would it take before I started noticing a slow down in performance?

Piranha

I agree with Mark, normally you should use one database. And normally it should be no problem with corrupt DB tables. But don't use a normal PC for this, you should really get a server that is set up by proffesionals, then they know how to make sure everything is working all the time.

If you want added security it is better to run a backup database that store the exact same things, but are in a different computer on a different location and so on. But that is normally not nessecary, it's almost only banks and other companies with special need for security that runs it this way. If you run backup once a day then the worst thing that can happen is that you lose one days work, and compared to the cost of making a fool-proof system that is not much money.

msound

Yeah i've decided to go with option A. I'll consider getting a second server for replication if the need arises. Thanks guys!