Questions on Database Design

--001485f9a8325c816504750eb377
Content-Type: text/plain; charset=ISO-8859-1

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

--001485f9a8325c816504750eb377--

Re: Questions on Database Design

Mark Phillips wrote:
> 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?
>



It's not so much how many users you have (though that may be a question
of data storage more than databases) as to what are they doing? Are the
actions related? If they are, then have one database with each user
having access to their records and their records only, which can easily
be done with terms of database security..

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Questions on Database Design

--001485f9a832341ddc04750ee769
Content-Type: text/plain; charset=ISO-8859-1

On Sat, Oct 3, 2009 at 2:47 PM, John Meyer wrote:

> Mark Phillips wrote:
>
>> 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?
>>
>>
>
>
>
> It's not so much how many users you have (though that may be a question of
> data storage more than databases) as to what are they doing? Are the
> actions related? If they are, then have one database with each user having
> access to their records and their records only, which can easily be done
> with terms of database security..
>

John,
Thanks. The data is private to each user; there is no sharing of data. I am
not sure what you mean by "are the actions related" Each user is
reading/writing independently of each other. Would that argue for separate
databases?

Mark

--001485f9a832341ddc04750ee769--

Re: Questions on Database Design

> John,
> Thanks. The data is private to each user; there is no sharing of data.
> I am not sure what you mean by "are the actions related" Each user is
> reading/writing independently of each other. Would that argue for
> separate databases?
>
> Mark


Are the actions of a similar nature (i.e. they're all writing the same
type of data and the databases themselves would be similar if not the
same)? Is there any sort of application that would traverse all of
those databases at once?
Also keep in mind that multiple databases increases your complexity. I
think we'd have a better idea if we knew a little more of the specifics
of this application.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Questions on Database Design

--00163630ebe90a5bc104751000f4
Content-Type: text/plain; charset=ISO-8859-1

On Sat, Oct 3, 2009 at 3:06 PM, Martin Gainty 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
data.

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

Mark

>
>
> > Date: Sat, 3 Oct 2009 14:38:25 -0700
> > Subject: Questions on Database Design
> > From:
> > To: mysql@lists.mysql.com
>
> >
> > 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.
>

--00163630ebe90a5bc104751000f4--

Re: Questions on Database Design

--0016e64098c65abf1b04751055b8
Content-Type: text/plain; charset=ISO-8859-1

On Sat, Oct 3, 2009 at 4:02 PM, John Meyer wrote:

>
> John,
>> Thanks. The data is private to each user; there is no sharing of data. I
>> am not sure what you mean by "are the actions related" Each user is
>> reading/writing independently of each other. Would that argue for separate
>> databases?
>> Mark
>>
>
>
> Are the actions of a similar nature (i.e. they're all writing the same type
> of data and the databases themselves would be similar if not the same)?


Each user will write the same type of data to the same schema. So the
databases schemas would be identical.

Is there any sort of application that would traverse all of those databases
> at once?
>

Not really necessary from the user's perspective.

Also keep in mind that multiple databases increases your complexity. I
> think we'd have a better idea if we knew a little more of the specifics of
> this application.
>

Sure, no great military secrets here. The application is a mobile softball
(baseball, basketball, soccer, etc.) score book. The data for each pitch
(softball = pitch type, who made what play, what the batter did, errors,
etc.) is entered on the cell phone, and stored in MySQL tables in order to
create game and season stats for a team and each player. This can also apply
to other sports. Each user is a team manager or scorekeeper. There really
isn't any need for team A to see/access team B's stats. A league may want to
do a special type of roll-up, but this app is really just for each team. I
am sure an app could be written to do the roll-up, but that is not the main
focus.

I think by your discussion, it may make sense to have separate databases for
each user instead of add a userID column to many of the tables to separate
each user's data from the other users. Does that make sense?

Mark

--0016e64098c65abf1b04751055b8--

Re: Questions on Database Design

Mark Phillips wrote:
> On Sat, Oct 3, 2009 at 3:06 PM, Martin Gainty 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
> data.
>
> Can't I achieve the same level of security if each row has a userID, and all
> queries use a "where userID=xxxxxxx" clause?
>
> Mark
>



no, don't confuse that with database security. There are too many ways
to get around that sort of trick through SQL injection attacks. Read
http://dev.mysql.com/doc/refman/5.4/en/privilege-system.html for a
starter on privileges and security.
But as long as you're not needing to regularly combine and aggregate the
data then creating separate databases is a reasonable option.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Questions on un-index searches and slow-query-log

Questions Folks: =20
(1) What do you about un-index searches. How can one report and monitor t=
hem?=20
(2) What do you do with the slow-query log. Are there any utilities or sc=
ripts out there to filter and manage this log?

Thanks



********************************************
This message is intended only for the use of the Addressee and
may contain information that is PRIVILEGED and CONFIDENTIAL.

If you are not the intended recipient, you are hereby notified
that any dissemination of this communication is strictly prohibited.

If you have received this communication in error, please erase
all copies of the message and its attachments and notify us
immediately.

Thank you.
********************************************

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Questions on un-index searches and slow-query-log

See
log-queries-not-using-indexes
option in my.cnf, used with the slow log.

http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html


Regards,
Gavin Towey


-----Original Message-----
From: Brown, Charles [mailto:CBrown@BMI.com]
Sent: Monday, October 05, 2009 12:59 PM
To: Mysql List
Cc: John Meyer; Mark Phillips
Subject: Questions on un-index searches and slow-query-log

Questions Folks:
(1) What do you about un-index searches. How can one report and monitor the=
m?
(2) What do you do with the slow-query log. Are there any utilities or scri=
pts out there to filter and manage this log?

Thanks



********************************************
This message is intended only for the use of the Addressee and
may contain information that is PRIVILEGED and CONFIDENTIAL.

If you are not the intended recipient, you are hereby notified
that any dissemination of this communication is strictly prohibited.

If you have received this communication in error, please erase
all copies of the message and its attachments and notify us
immediately.

Thank you.
********************************************

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com


The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Questions on un-index searches and slow-query-log

And an answer to 2): http://www.maatkit.org/doc/mk-query-digest.html

On Tue, Oct 6, 2009 at 02:59, Brown, Charles wrote:
> Questions Folks:
> (1) What do you about un-index searches. How can one report and monitor t=
hem?
> (2) What do you do with the slow-query log. Are there any utilities or sc=
ripts out there to filter and manage this log?
>
> Thanks
>
>
>
> ********************************************
> This message is intended only for the use of the Addressee and
> may contain information that is PRIVILEGED and CONFIDENTIAL.
>
> If you are not the intended recipient, you are hereby notified
> that any dissemination of this communication is strictly prohibited.
>
> If you have received this communication in error, please erase
> all copies of the message and its attachments and notify us
> immediately.
>
> Thank you.
> ********************************************
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=3Dlists@o=
lindata.com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg