List:General Discussion« Previous MessageNext Message »
From:Mark Phillips Date:October 3 2009 11:11pm
Subject:Re: Questions on Database Design
View as plain text  
On Sat, Oct 3, 2009 at 3:06 PM, Martin Gainty <mgainty@stripped> wrote:

>  depends on the relationship of the Data Tables and the Users that use them
> for instance if I was to setup a table of outgoing calls from 2 distinct
> individuals :
> Me>                      calls to HarvardMedicalSchool, MassGeneral,
> SomervilleHospital and AMA
> VereinDesKrankRufs>calls to Biff,Tony,EdSoprano and Destiny
> so as you can see the difference between my calls and Vereins calls should
> never be joined
> as Vereins customers are distinctly not mine and mine are not his
> Moreover my contact table would contain Degrees and titles where Vereins
> customers
> have no need for that
> So in this case it would make perfect sense for my Database to be separate
> and distinct from Vereins database..if for no other reason than the schemas
> are completely difference
> With an emphasis on security once Verein initiates populating his records
> on your DB by populating the same tables and using the same join
> relationships it will be impossible to force him to not use those tables
> or even to restrich his access to the slave server while you're updating
> the master
> You can restrict access by GRANT SELECT on the tables to Verein but that
> would last only a week or 2 until Verein requests update and insert access
> to the DB. Once the INSERT and UPDATE grants are made you wont be able to
> separate his records from yours
> Keep the 2 separate is my suggestion..MySQL is inexpensive and HW is cheap
> so this should be a low cost solution for you
> Keep us apprised and any feel free to inquire on any operational details
> you may require.
> Thanks! To make sure I understand. Even if the schemas are the same, if the
data is not related, nor is meant to be combined in some way (eg rolled up
or summed in some way), then creating a separate database for each user is a
better way to go; or at least a meaningful way to go. A side benefit is
greater security from the stand point that user a cannot get to user b's

Can't I achieve the same level of security if each row has a userID, and all
queries use a "where userID=xxxxxxx" clause?


> > Date: Sat, 3 Oct 2009 14:38:25 -0700
> > Subject: Questions on Database Design
> > From:
> > To: mysql@stripped
> >
> > I am new at database design, and my question relates to the trade-offs
> > between putting all data in one database or several for mysql. For
> example,
> > say I have an application where a users login from their mobile phones
> and
> > read/write data to a database. Say there are roughly 10-15 tables in the
> > database and each user will add approximately 20,000 records per year.
> Each
> > user should not have access to data from another user. Users have to
> > register in some way to create their database in the first place. When
> does
> > it make sense to give each user their own database versus putting all the
> > data into one database (ie one set of tables) and with multiple userIDs?
> 10
> > users? 1,000 users? Never?
> >
> > Thanks!
> >
> > Mark
> ------------------------------
> Hotmail: Free, trusted and rich email service. Get it
> now.<>

Questions on Database DesignMark Phillips3 Oct
  • Re: Questions on Database DesignJohn Meyer3 Oct
    • Re: Questions on Database DesignMark Phillips3 Oct
      • Re: Questions on Database DesignJohn Meyer4 Oct
        • Re: Questions on Database DesignMark Phillips4 Oct
Re: Questions on Database DesignMark Phillips4 Oct
  • Re: Questions on Database DesignJohn Meyer4 Oct
    • Questions on un-index searches and slow-query-logCharles Brown5 Oct
      • RE: Questions on un-index searches and slow-query-logGavin Towey5 Oct
      • Re: Questions on un-index searches and slow-query-logWalter Heck - OlinData.com6 Oct
Re: Questions on Database DesignMark Phillips4 Oct