anthony.galano_at_gmail.com wrote:
> Currently we have a subscription service website which manages time
> and attendance for companies using a MySQL database. Currently all
> companies and users are in the same database. Every company has a
> companyid and every user has a userid and is joined to a company by
> the companyid. We do all standard select, insert, update, delete
> queries you would expect using the companyid and userid as keys for
> joining and selecting records across tables. The companyid and userid
> is looked up and stored in the session when a user logs in.
>
I can't be sure from your description above, but I hope the users don't
have login IDs to MySQL.
> This has worked well until recently where there have been a few
> concerns. One is the potential security risks of running all queries
> on the companyid condition. While we take every precaution to not pass
> the companyid in any forms or urls for sql injection if someone was
> able to alter their session companyid in some freak way they could get
> to the sensitive data of any other customer.
That seems to have little to do with your database design and more to do
with your session management.
> Also, any coding errors
> as some of our quries are quite complex have the potential for
> altering other users data with careless bugs (and there have been a
> few). More importantly though is the issue of locking data and
> preventing race conditions.
This sounds like it may be a problem with indexes, joins, and
discriminations. Depending on the statistics of the DB it may be able
to make better locking decisions.
> <snip>
> On top of that, employees
> have the ability to modify their records as well. If an admin runs a
> report and is reviewing it and an employee updates a record obviously
> the admin will not see it and someones data will be overwritten.
>
Marshall described a couple optimistic locking scenarios in his reply.
I'm unsure why they're unsatisfactory but in practice I've found them
reliable and efficient.
> <snip>
>
> On top of all this, recently we had a corruption in a MySQL table
> which affected ALL users records. It was fixed easily enough with
> MySQL's repair utilities but it affected service for all users for
> almost 20 minutes.
>
Again, not a DB design issue, but if the problem repeats itself maybe
there's a problem with your MySQL installation or MySQL itself?
> So, we thought about changing our approach to create a new database
> with every new company.
As Marshall says in his reply, separate DBs is a cop-out, not a design.
> <snip>
> And lastly, a single company's data can easily be backed up
> and restored in it's entiretly with affecting the rest of the system.
>
I would explore other backup solutions. Can MySQL backup tranlogs?
Instead of backing-up the entire DB just backup what changed. Perhaps
MySQL supports replication?
--
Visit <http://blogs.instreamfinancial.com/anything.php>
to read my rants on technology and the finance industry.
Received on Mon Apr 16 2007 - 22:00:46 CDT
