Rails - Dec 2004 - question about host web based application using rails

Hi,

I am planning to develop a web based EMR system using rails and not sure
how to host them.

Do I need to have one rails set up with different database file for each
customer? Or all customers can share the same rails with a shared
database (in this case, I guess in each table I have to have a column to
indicate which customer the record belongs to)?

By the way, my name is chong and I originally from China. Currently I am
working as a software developer in USA.

Thanks
chong

On Wed, Dec 15, 2004 at 09:12:27AM -0600, ChongQing Xiao
wrote:
> I am planning to develop a web based EMR system using rails and not sure
> how to host them.
> 
> Do I need to have one rails set up with different database file for each
> customer? Or all customers can share the same rails with a shared
> database (in this case, I guess in each table I have to have a column to
> indicate which customer the record belongs to)?
> 
> By the way, my name is chong and I originally from China. Currently I am
> working as a software developer in USA.
You can share a database over several rails applications by setting the
ActiveRecord table_name_prefix or table_name_suffix attributes. Others
on the list could probably provide you with more information about
this issue.

marcel
-- 
Marcel Molina Jr. <marcel-WRrfy3IlpWYdnm+yROfE0A@public.gmane.org>

Hi, Marcel:

Thanks for the answer.

Just want to make my case clear

It will be the same rails application but different doctors will sign up
to use the application. The doctor should see only the information about
their own patient, schedule, patient record and so on.

This will be very similar to BaseCamp but I am not sure how David did
it.

chong

-----Original Message-----
From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of Marcel Molina
Jr.
Sent: Wednesday, December 15, 2004 9:20 AM
To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
Subject: Re: [Rails] question about host web based application using
rails

On Wed, Dec 15, 2004 at 09:12:27AM -0600, ChongQing Xiao
wrote:
> I am planning to develop a web based EMR system using rails and not
sure
> how to host them.
> 
> Do I need to have one rails set up with different database file for
each
> customer? Or all customers can share the same rails with a shared
> database (in this case, I guess in each table I have to have a column
to
> indicate which customer the record belongs to)?
> 
> By the way, my name is chong and I originally from China. Currently I
am
> working as a software developer in USA.
You can share a database over several rails applications by setting the
ActiveRecord table_name_prefix or table_name_suffix attributes. Others
on the list could probably provide you with more information about
this issue.

marcel
-- 
Marcel Molina Jr. <marcel-WRrfy3IlpWYdnm+yROfE0A@public.gmane.org>
_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

On Wed, 15 Dec 2004 09:29:10 -0600, ChongQing Xiao
<CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org>
wrote:
> It will be the same rails application but different doctors will sign up
> to use the application. The doctor should see only the information about
> their own patient, schedule, patient record and so on.
> 
> This will be very similar to BaseCamp but I am not sure how David did
> it.
If each account will be using a different subdomain (as Basecamp
does), you could set up something in the dispatcher to load a
different environment file (specifying a different database connection
within) depending on the hostname being accessed. I don''t have any
examples, since I haven''t actually done this---it''s just an
idea.

-- 
Regards,
John Wilger

-----------
Alice came to a fork in the road. "Which road do I take?" she asked.
"Where do you want to go?" responded the Cheshire cat.
"I don''t know," Alice answered.
"Then," said the cat, "it doesn''t matter."
- Lewis Carrol, Alice in Wonderland

Hi, John:

Thanks for point me to the right direction.
Do you know any document to explain how to load a different environment
file base on sub domain?

Thanks
chong

-----Original Message-----
From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of John Wilger
Sent: Wednesday, December 15, 2004 9:43 AM
To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
Subject: Re: [Rails] question about host web based application using
rails

On Wed, 15 Dec 2004 09:29:10 -0600, ChongQing Xiao
<CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org>
wrote:
> It will be the same rails application but different doctors will sign
up
> to use the application. The doctor should see only the information
about
> their own patient, schedule, patient record and so on.
> 
> This will be very similar to BaseCamp but I am not sure how David did
> it.
If each account will be using a different subdomain (as Basecamp
does), you could set up something in the dispatcher to load a
different environment file (specifying a different database connection
within) depending on the hostname being accessed. I don''t have any
examples, since I haven''t actually done this---it''s just an
idea.

-- 
Regards,
John Wilger

-----------
Alice came to a fork in the road. "Which road do I take?" she asked.
"Where do you want to go?" responded the Cheshire cat.
"I don''t know," Alice answered.
"Then," said the cat, "it doesn''t matter."
- Lewis Carrol, Alice in Wonderland
_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

On Wed, 15 Dec 2004 10:42:41 -0500, John Wilger
<johnwilger-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On Wed, 15 Dec 2004 09:29:10 -0600, ChongQing Xiao
> <CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org> wrote:
> > It will be the same rails application but different doctors will sign
up
> > to use the application. The doctor should see only the information
about
> > their own patient, schedule, patient record and so on.
> >
> > This will be very similar to BaseCamp but I am not sure how David did
> > it.
> 
> If each account will be using a different subdomain (as Basecamp
> does), you could set up something in the dispatcher to load a
> different environment file (specifying a different database connection
> within) depending on the hostname being accessed. I don''t have any
> examples, since I haven''t actually done this---it''s just
an idea.
> 
I don''t think chong specified that each account would be using a
different subdomain...

What do I mean is each customer (might be several users - such as nurse,
doctor) needs to separate from each other so they won''t see each
other''s
data.

That means they need to share the same table structure but I am not sure
What is the best way to separate the data?

Thanks
chong

-----Original Message-----
From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of Joe Van Dyk
Sent: Wednesday, December 15, 2004 10:15 AM
To: John Wilger; rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
Subject: Re: [Rails] question about host web based application using
rails

On Wed, 15 Dec 2004 10:42:41 -0500, John Wilger
<johnwilger-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On Wed, 15 Dec 2004 09:29:10 -0600, ChongQing Xiao
> <CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org> wrote:
> > It will be the same rails application but different doctors will
sign up
> > to use the application. The doctor should see only the information
about
> > their own patient, schedule, patient record and so on.
> >
> > This will be very similar to BaseCamp but I am not sure how David
did
> > it.
> 
> If each account will be using a different subdomain (as Basecamp
> does), you could set up something in the dispatcher to load a
> different environment file (specifying a different database connection
> within) depending on the hostname being accessed. I don''t have any
> examples, since I haven''t actually done this---it''s just
an idea.
> 
I don''t think chong specified that each account would be using a
different subdomain...
_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

Add a column to your table with an owner_id, then a seperate table
listing all the users.  This doesn''t deal with the situations where
some people may need access to other''s data, in which case you would
want to look into some form of access control.


On Wed, 15 Dec 2004 10:20:09 -0600, ChongQing Xiao
<CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org>
wrote:
> What do I mean is each customer (might be several users - such as nurse,
> doctor) needs to separate from each other so they won''t see each
other''s
> data.
> 
> That means they need to share the same table structure but I am not sure
> What is the best way to separate the data?
> 
> Thanks
> chong
> 
> -----Original Message-----
> From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of Joe Van Dyk
> Sent: Wednesday, December 15, 2004 10:15 AM
> To: John Wilger; rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> Subject: Re: [Rails] question about host web based application using
> rails
> 
> On Wed, 15 Dec 2004 10:42:41 -0500, John Wilger
<johnwilger-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> wrote:
> > On Wed, 15 Dec 2004 09:29:10 -0600, ChongQing Xiao
> > <CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org> wrote:
> > > It will be the same rails application but different doctors will
> sign up
> > > to use the application. The doctor should see only the
information
> about
> > > their own patient, schedule, patient record and so on.
> > >
> > > This will be very similar to BaseCamp but I am not sure how David
> did
> > > it.
> >
> > If each account will be using a different subdomain (as Basecamp
> > does), you could set up something in the dispatcher to load a
> > different environment file (specifying a different database connection
> > within) depending on the hostname being accessed. I don''t
have any
> > examples, since I haven''t actually done this---it''s
just an idea.
> >
> 
> I don''t think chong specified that each account would be using a
> different subdomain...
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

On Wed, 15 Dec 2004 10:20:09 -0600, ChongQing Xiao
<CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org>
wrote:
> What do I mean is each customer (might be several users - such as nurse,
> doctor) needs to separate from each other so they won''t see each
other''s
> data.
> 
> That means they need to share the same table structure but I am not sure
> What is the best way to separate the data?

If you only want to use one database, then you would relate everything
for that customer back to a "customer" record. You don''t
necessarily
need to put the customer id on every single row, though---as long as
the relational hierarchy pointed back to the correct customer and your
app was coded to deal with this correctly.

The problem with only using one database might be if you ever want to
split certain customers off onto their own server. It can still be
done, but it would be easier if each customer had their own instance
of the database.

-- 
Regards,
John Wilger

-----------
Alice came to a fork in the road. "Which road do I take?" she asked.
"Where do you want to go?" responded the Cheshire cat.
"I don''t know," Alice answered.
"Then," said the cat, "it doesn''t matter."
- Lewis Carrol, Alice in Wonderland

Hi, John:

Sorry that I didn''t make it clear. 
Actually, I prefer use separate database since if I have many tables, I
don''t want to each table to have a column to indicate which customer
each record belongs to.

If I use different table for different customers,
The thing I am not sure is 

Do I need to have just one rails instance and use sub domain to handle
different customers?
If yes, how to I specify different database for different subdomain?

Also, Can I have one rails installed but several different rails
instance running share the same rails code?

Thanks
chong





-----Original Message-----
From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of John Wilger
Sent: Wednesday, December 15, 2004 10:29 AM
To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
Subject: Re: [Rails] question about host web based application using
rails

On Wed, 15 Dec 2004 10:20:09 -0600, ChongQing Xiao
<CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org>
wrote:
> What do I mean is each customer (might be several users - such as
nurse,
> doctor) needs to separate from each other so they won''t see each
other''s
> data.
> 
> That means they need to share the same table structure but I am not
sure
> What is the best way to separate the data?

If you only want to use one database, then you would relate everything
for that customer back to a "customer" record. You don''t
necessarily
need to put the customer id on every single row, though---as long as
the relational hierarchy pointed back to the correct customer and your
app was coded to deal with this correctly.

The problem with only using one database might be if you ever want to
split certain customers off onto their own server. It can still be
done, but it would be easier if each customer had their own instance
of the database.

-- 
Regards,
John Wilger

-----------
Alice came to a fork in the road. "Which road do I take?" she asked.
"Where do you want to go?" responded the Cheshire cat.
"I don''t know," Alice answered.
"Then," said the cat, "it doesn''t matter."
- Lewis Carrol, Alice in Wonderland
_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

Well, typically you''d have these tables 

Records
--------------
id (and other record stuff)

Customer
-----------------
id (and other customer stuff)


Customer_Records
-----------------
customer_id
record_id

Then each record does not contain the customer id.

I don''t know why you''d want different tables for different
customers.
Or separate databases for that matter.


On Wed, 15 Dec 2004 10:40:43 -0600, ChongQing Xiao
<CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org>
wrote:
> Hi, John:
> 
> Sorry that I didn''t make it clear.
> Actually, I prefer use separate database since if I have many tables, I
> don''t want to each table to have a column to indicate which
customer
> each record belongs to.
> 
> If I use different table for different customers,
> The thing I am not sure is
> 
> Do I need to have just one rails instance and use sub domain to handle
> different customers?
> If yes, how to I specify different database for different subdomain?
> 
> Also, Can I have one rails installed but several different rails
> instance running share the same rails code?
> 
> Thanks
> chong
> 
> -----Original Message-----
> From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of John Wilger
> Sent: Wednesday, December 15, 2004 10:29 AM
> To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> Subject: Re: [Rails] question about host web based application using
> rails
> 
> On Wed, 15 Dec 2004 10:20:09 -0600, ChongQing Xiao
> <CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org> wrote:
> > What do I mean is each customer (might be several users - such as
> nurse,
> > doctor) needs to separate from each other so they won''t see
each
> other''s
> > data.
> >
> > That means they need to share the same table structure but I am not
> sure
> > What is the best way to separate the data?
> 
> If you only want to use one database, then you would relate everything
> for that customer back to a "customer" record. You don''t
necessarily
> need to put the customer id on every single row, though---as long as
> the relational hierarchy pointed back to the correct customer and your
> app was coded to deal with this correctly.
> 
> The problem with only using one database might be if you ever want to
> split certain customers off onto their own server. It can still be
> done, but it would be easier if each customer had their own instance
> of the database.
> 
> --
> Regards,
> John Wilger
> 
> -----------
> Alice came to a fork in the road. "Which road do I take?" she
asked.
> "Where do you want to go?" responded the Cheshire cat.
> "I don''t know," Alice answered.
> "Then," said the cat, "it doesn''t matter."
> - Lewis Carrol, Alice in Wonderland
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

Chong,

I would like to point out that having a single database is definitely 
the standard way in a web application like yours. As Joe said, you 
don''t need to define the user for every record in every table. You just
have to design a sound relational model in which every record can be 
connected to the correct user id.

It will be a lot more complicated if you want to have own database for 
every single customer. You will have to write scripts that create a new 
database whenever a new user registers for your service. And whenever 
you make some changes to the datamodel, you will have to change not one 
but all those databases. That is IMHO not how relational model is 
supposed to be used and it''s certainly against the DRY (don''t
repeat
yourself) principle.

What if you will have thousands of customers. Maintaining one database 
with all that information is certainly easier to handle than a whole 
bunch of separate databases. Also if you ever want to get some 
aggregate data over all of your customers, you will want to have the 
data in a single database.

I don''t mean that you should no way in hell use separate databases, 
just that you will have to have very good reasons for that. The reasons 
you have mentioned so far are not that good.

This all depends, of course. If you know you''ll only have a handful of 
customers, it might not be that hard to create separate db''s for them.

Cheers,
Jarkko


On 15.12.2004, at 18:40, ChongQing Xiao wrote:
> Hi, John:
>
> Sorry that I didn''t make it clear.
> Actually, I prefer use separate database since if I have many tables, I
> don''t want to each table to have a column to indicate which
customer
> each record belongs to.

>
> If I use different table for different customers,
> The thing I am not sure is
>
> Do I need to have just one rails instance and use sub domain to handle
> different customers?
> If yes, how to I specify different database for different subdomain?
>
> Also, Can I have one rails installed but several different rails
> instance running share the same rails code?
>
> Thanks
> chong
>
>
>
>
>
> -----Original Message-----
> From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of John Wilger
> Sent: Wednesday, December 15, 2004 10:29 AM
> To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> Subject: Re: [Rails] question about host web based application using
> rails
>
> On Wed, 15 Dec 2004 10:20:09 -0600, ChongQing Xiao
> <CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org> wrote:
>> What do I mean is each customer (might be several users - such as
> nurse,
>> doctor) needs to separate from each other so they won''t see
each
> other''s
>> data.
>>
>> That means they need to share the same table structure but I am not
> sure
>> What is the best way to separate the data?
>
>
> If you only want to use one database, then you would relate everything
> for that customer back to a "customer" record. You don''t
necessarily
> need to put the customer id on every single row, though---as long as
> the relational hierarchy pointed back to the correct customer and your
> app was coded to deal with this correctly.
>
> The problem with only using one database might be if you ever want to
> split certain customers off onto their own server. It can still be
> done, but it would be easier if each customer had their own instance
> of the database.
>
> -- 
> Regards,
> John Wilger
>
> -----------
> Alice came to a fork in the road. "Which road do I take?" she
asked.
> "Where do you want to go?" responded the Cheshire cat.
> "I don''t know," Alice answered.
> "Then," said the cat, "it doesn''t matter."
> - Lewis Carrol, Alice in Wonderland
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
--
Jarkko Laine
http://jlaine.net


_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

Hi,

There was a discussion sometime ago on this subject.  FWIW, I went the
multiple database route for flexibility.  I decided on that way for a
few different reasons, most of which I outlined here:

http://www.rubyonrails.org/show/HowtoUseMultipleDatabases

In my particular app, aggregation is a non-issue and I see a
tremendous advantage in having completely isolated
database/application versions.... but for every argument for seperate
databases, there are 3 *for* a unified database.  I''d recommend
sticking with a single db unless you know you want multiple db''s.

:)

Steve

On Wed, 15 Dec 2004 20:56:49 +0200, Jarkko Laine
<jarkko-k1O+Gnc6WpmsTnJN9+BGXg@public.gmane.org>
wrote:
> Chong,
> 
> I would like to point out that having a single database is definitely
> the standard way in a web application like yours. As Joe said, you
> don''t need to define the user for every record in every table. You
just
> have to design a sound relational model in which every record can be
> connected to the correct user id.
> 
> It will be a lot more complicated if you want to have own database for
> every single customer. You will have to write scripts that create a new
> database whenever a new user registers for your service. And whenever
> you make some changes to the datamodel, you will have to change not one
> but all those databases. That is IMHO not how relational model is
> supposed to be used and it''s certainly against the DRY
(don''t repeat
> yourself) principle.
> 
> What if you will have thousands of customers. Maintaining one database
> with all that information is certainly easier to handle than a whole
> bunch of separate databases. Also if you ever want to get some
> aggregate data over all of your customers, you will want to have the
> data in a single database.
> 
> I don''t mean that you should no way in hell use separate
databases,
> just that you will have to have very good reasons for that. The reasons
> you have mentioned so far are not that good.
> 
> This all depends, of course. If you know you''ll only have a
handful of
> customers, it might not be that hard to create separate db''s for
them.
> 
> Cheers,
> Jarkko
> 
> 
> On 15.12.2004, at 18:40, ChongQing Xiao wrote:
> 
> > Hi, John:
> >
> > Sorry that I didn''t make it clear.
> > Actually, I prefer use separate database since if I have many tables,
I
> > don''t want to each table to have a column to indicate which
customer
> > each record belongs to.
> 
> >
> > If I use different table for different customers,
> > The thing I am not sure is
> >
> > Do I need to have just one rails instance and use sub domain to handle
> > different customers?
> > If yes, how to I specify different database for different subdomain?
> >
> > Also, Can I have one rails installed but several different rails
> > instance running share the same rails code?
> >
> > Thanks
> > chong
> >
> >
> >
> >
> >
> > -----Original Message-----
> > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> >
[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of John Wilger
> > Sent: Wednesday, December 15, 2004 10:29 AM
> > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > Subject: Re: [Rails] question about host web based application using
> > rails
> >
> > On Wed, 15 Dec 2004 10:20:09 -0600, ChongQing Xiao
> > <CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org> wrote:
> >> What do I mean is each customer (might be several users - such as
> > nurse,
> >> doctor) needs to separate from each other so they won''t
see each
> > other''s
> >> data.
> >>
> >> That means they need to share the same table structure but I am
not
> > sure
> >> What is the best way to separate the data?
> >
> >
> > If you only want to use one database, then you would relate everything
> > for that customer back to a "customer" record. You
don''t necessarily
> > need to put the customer id on every single row, though---as long as
> > the relational hierarchy pointed back to the correct customer and your
> > app was coded to deal with this correctly.
> >
> > The problem with only using one database might be if you ever want to
> > split certain customers off onto their own server. It can still be
> > done, but it would be easier if each customer had their own instance
> > of the database.
> >
> > --
> > Regards,
> > John Wilger
> >
> > -----------
> > Alice came to a fork in the road. "Which road do I take?"
she asked.
> > "Where do you want to go?" responded the Cheshire cat.
> > "I don''t know," Alice answered.
> > "Then," said the cat, "it doesn''t
matter."
> > - Lewis Carrol, Alice in Wonderland
> > _______________________________________________
> > Rails mailing list
> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> > _______________________________________________
> > Rails mailing list
> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> --
> Jarkko Laine
> http://jlaine.net
> 
> 
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> 
> 
> 
>

Thanks Steve. The link is great.
Now I just need more thinking.

chong

-----Original Message-----
From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of Steve Kellock
Sent: Wednesday, December 15, 2004 1:10 PM
To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
Subject: Re: [Rails] question about host web based application using
rails

Hi,

There was a discussion sometime ago on this subject.  FWIW, I went the
multiple database route for flexibility.  I decided on that way for a
few different reasons, most of which I outlined here:

http://www.rubyonrails.org/show/HowtoUseMultipleDatabases

In my particular app, aggregation is a non-issue and I see a
tremendous advantage in having completely isolated
database/application versions.... but for every argument for seperate
databases, there are 3 *for* a unified database.  I''d recommend
sticking with a single db unless you know you want multiple db''s.

:)

Steve

On Wed, 15 Dec 2004 20:56:49 +0200, Jarkko Laine
<jarkko-k1O+Gnc6WpmsTnJN9+BGXg@public.gmane.org>
wrote:
> Chong,
> 
> I would like to point out that having a single database is definitely
> the standard way in a web application like yours. As Joe said, you
> don''t need to define the user for every record in every table. You
just
> have to design a sound relational model in which every record can be
> connected to the correct user id.
> 
> It will be a lot more complicated if you want to have own database for
> every single customer. You will have to write scripts that create a
new
> database whenever a new user registers for your service. And whenever
> you make some changes to the datamodel, you will have to change not
one
> but all those databases. That is IMHO not how relational model is
> supposed to be used and it''s certainly against the DRY
(don''t repeat
> yourself) principle.
> 
> What if you will have thousands of customers. Maintaining one database
> with all that information is certainly easier to handle than a whole
> bunch of separate databases. Also if you ever want to get some
> aggregate data over all of your customers, you will want to have the
> data in a single database.
> 
> I don''t mean that you should no way in hell use separate
databases,
> just that you will have to have very good reasons for that. The
reasons
> you have mentioned so far are not that good.
> 
> This all depends, of course. If you know you''ll only have a
handful of
> customers, it might not be that hard to create separate db''s for
them.
> 
> Cheers,
> Jarkko
> 
> 
> On 15.12.2004, at 18:40, ChongQing Xiao wrote:
> 
> > Hi, John:
> >
> > Sorry that I didn''t make it clear.
> > Actually, I prefer use separate database since if I have many
tables, I
> > don''t want to each table to have a column to indicate which
customer
> > each record belongs to.
> 
> >
> > If I use different table for different customers,
> > The thing I am not sure is
> >
> > Do I need to have just one rails instance and use sub domain to
handle
> > different customers?
> > If yes, how to I specify different database for different subdomain?
> >
> > Also, Can I have one rails installed but several different rails
> > instance running share the same rails code?
> >
> > Thanks
> > chong
> >
> >
> >
> >
> >
> > -----Original Message-----
> > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> >
[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of John
Wilger
> > Sent: Wednesday, December 15, 2004 10:29 AM
> > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > Subject: Re: [Rails] question about host web based application using
> > rails
> >
> > On Wed, 15 Dec 2004 10:20:09 -0600, ChongQing Xiao
> > <CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org> wrote:
> >> What do I mean is each customer (might be several users - such as
> > nurse,
> >> doctor) needs to separate from each other so they won''t
see each
> > other''s
> >> data.
> >>
> >> That means they need to share the same table structure but I am
not
> > sure
> >> What is the best way to separate the data?
> >
> >
> > If you only want to use one database, then you would relate
everything
> > for that customer back to a "customer" record. You
don''t necessarily
> > need to put the customer id on every single row, though---as long as
> > the relational hierarchy pointed back to the correct customer and
your
> > app was coded to deal with this correctly.
> >
> > The problem with only using one database might be if you ever want
to
> > split certain customers off onto their own server. It can still be
> > done, but it would be easier if each customer had their own instance
> > of the database.
> >
> > --
> > Regards,
> > John Wilger
> >
> > -----------
> > Alice came to a fork in the road. "Which road do I take?"
she asked.
> > "Where do you want to go?" responded the Cheshire cat.
> > "I don''t know," Alice answered.
> > "Then," said the cat, "it doesn''t
matter."
> > - Lewis Carrol, Alice in Wonderland
> > _______________________________________________
> > Rails mailing list
> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> > _______________________________________________
> > Rails mailing list
> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> --
> Jarkko Laine
> http://jlaine.net
> 
> 
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> 
> 
> 
>
_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

Why do you think you need multiple databases?


On Wed, 15 Dec 2004 13:34:06 -0600, ChongQing Xiao
<CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org>
wrote:
> Thanks Steve. The link is great.
> Now I just need more thinking.
> 
> chong
> 
> -----Original Message-----
> From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of Steve Kellock
> Sent: Wednesday, December 15, 2004 1:10 PM
> To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> Subject: Re: [Rails] question about host web based application using
> rails
> 
> Hi,
> 
> There was a discussion sometime ago on this subject.  FWIW, I went the
> multiple database route for flexibility.  I decided on that way for a
> few different reasons, most of which I outlined here:
> 
> http://www.rubyonrails.org/show/HowtoUseMultipleDatabases
> 
> In my particular app, aggregation is a non-issue and I see a
> tremendous advantage in having completely isolated
> database/application versions.... but for every argument for seperate
> databases, there are 3 *for* a unified database.  I''d recommend
> sticking with a single db unless you know you want multiple db''s.
> 
> :)
> 
> Steve
> 
> On Wed, 15 Dec 2004 20:56:49 +0200, Jarkko Laine
<jarkko-k1O+Gnc6WpmsTnJN9+BGXg@public.gmane.org>
> wrote:
> > Chong,
> >
> > I would like to point out that having a single database is definitely
> > the standard way in a web application like yours. As Joe said, you
> > don''t need to define the user for every record in every
table. You
> just
> > have to design a sound relational model in which every record can be
> > connected to the correct user id.
> >
> > It will be a lot more complicated if you want to have own database for
> > every single customer. You will have to write scripts that create a
> new
> > database whenever a new user registers for your service. And whenever
> > you make some changes to the datamodel, you will have to change not
> one
> > but all those databases. That is IMHO not how relational model is
> > supposed to be used and it''s certainly against the DRY
(don''t repeat
> > yourself) principle.
> >
> > What if you will have thousands of customers. Maintaining one database
> > with all that information is certainly easier to handle than a whole
> > bunch of separate databases. Also if you ever want to get some
> > aggregate data over all of your customers, you will want to have the
> > data in a single database.
> >
> > I don''t mean that you should no way in hell use separate
databases,
> > just that you will have to have very good reasons for that. The
> reasons
> > you have mentioned so far are not that good.
> >
> > This all depends, of course. If you know you''ll only have a
handful of
> > customers, it might not be that hard to create separate db''s
for them.
> >
> > Cheers,
> > Jarkko
> >
> >
> > On 15.12.2004, at 18:40, ChongQing Xiao wrote:
> >
> > > Hi, John:
> > >
> > > Sorry that I didn''t make it clear.
> > > Actually, I prefer use separate database since if I have many
> tables, I
> > > don''t want to each table to have a column to indicate
which customer
> > > each record belongs to.
> >
> > >
> > > If I use different table for different customers,
> > > The thing I am not sure is
> > >
> > > Do I need to have just one rails instance and use sub domain to
> handle
> > > different customers?
> > > If yes, how to I specify different database for different
subdomain?
> > >
> > > Also, Can I have one rails installed but several different rails
> > > instance running share the same rails code?
> > >
> > > Thanks
> > > chong
> > >
> > >
> > >
> > >
> > >
> > > -----Original Message-----
> > > From:
rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > >
[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of John
> Wilger
> > > Sent: Wednesday, December 15, 2004 10:29 AM
> > > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > Subject: Re: [Rails] question about host web based application
using
> > > rails
> > >
> > > On Wed, 15 Dec 2004 10:20:09 -0600, ChongQing Xiao
> > > <CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org> wrote:
> > >> What do I mean is each customer (might be several users -
such as
> > > nurse,
> > >> doctor) needs to separate from each other so they
won''t see each
> > > other''s
> > >> data.
> > >>
> > >> That means they need to share the same table structure but I
am not
> > > sure
> > >> What is the best way to separate the data?
> > >
> > >
> > > If you only want to use one database, then you would relate
> everything
> > > for that customer back to a "customer" record. You
don''t necessarily
> > > need to put the customer id on every single row, though---as long
as
> > > the relational hierarchy pointed back to the correct customer and
> your
> > > app was coded to deal with this correctly.
> > >
> > > The problem with only using one database might be if you ever
want
> to
> > > split certain customers off onto their own server. It can still
be
> > > done, but it would be easier if each customer had their own
instance
> > > of the database.
> > >
> > > --
> > > Regards,
> > > John Wilger
> > >
> > > -----------
> > > Alice came to a fork in the road. "Which road do I
take?" she asked.
> > > "Where do you want to go?" responded the Cheshire cat.
> > > "I don''t know," Alice answered.
> > > "Then," said the cat, "it doesn''t
matter."
> > > - Lewis Carrol, Alice in Wonderland
> > > _______________________________________________
> > > Rails mailing list
> > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > > _______________________________________________
> > > Rails mailing list
> > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > >
> > --
> > Jarkko Laine
> > http://jlaine.net
> >
> >
> > _______________________________________________
> > Rails mailing list
> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> >
> >
> >
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

Hi, Joe:

If I can figure out a good way to use just one database without too much
change for the table schema, I would definitely go with single database.

One thought I have now is to have different record range for different
customer.

Such as
CustomerTable

	CustomerID  IDStart 	IDEnd
	1		1		10000
	2		100001	200000

This seems like a simple solution but I need to think about how to use
ActiveRecord so that when filing and query data, it will be transparent
use these range.

Any comment on this solution?

Thanks
chong


-----Original Message-----
From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of Joe Van Dyk
Sent: Wednesday, December 15, 2004 2:20 PM
To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
Subject: Re: [Rails] question about host web based application using
rails

Why do you think you need multiple databases?


On Wed, 15 Dec 2004 13:34:06 -0600, ChongQing Xiao
<CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org>
wrote:
> Thanks Steve. The link is great.
> Now I just need more thinking.
> 
> chong
> 
> -----Original Message-----
> From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of Steve
Kellock
> Sent: Wednesday, December 15, 2004 1:10 PM
> To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> Subject: Re: [Rails] question about host web based application using
> rails
> 
> Hi,
> 
> There was a discussion sometime ago on this subject.  FWIW, I went the
> multiple database route for flexibility.  I decided on that way for a
> few different reasons, most of which I outlined here:
> 
> http://www.rubyonrails.org/show/HowtoUseMultipleDatabases
> 
> In my particular app, aggregation is a non-issue and I see a
> tremendous advantage in having completely isolated
> database/application versions.... but for every argument for seperate
> databases, there are 3 *for* a unified database.  I''d recommend
> sticking with a single db unless you know you want multiple db''s.
> 
> :)
> 
> Steve
> 
> On Wed, 15 Dec 2004 20:56:49 +0200, Jarkko Laine
<jarkko-k1O+Gnc6WpmsTnJN9+BGXg@public.gmane.org>
> wrote:
> > Chong,
> >
> > I would like to point out that having a single database is
definitely
> > the standard way in a web application like yours. As Joe said, you
> > don''t need to define the user for every record in every
table. You
> just
> > have to design a sound relational model in which every record can be
> > connected to the correct user id.
> >
> > It will be a lot more complicated if you want to have own database
for
> > every single customer. You will have to write scripts that create a
> new
> > database whenever a new user registers for your service. And
whenever
> > you make some changes to the datamodel, you will have to change not
> one
> > but all those databases. That is IMHO not how relational model is
> > supposed to be used and it''s certainly against the DRY
(don''t repeat
> > yourself) principle.
> >
> > What if you will have thousands of customers. Maintaining one
database
> > with all that information is certainly easier to handle than a whole
> > bunch of separate databases. Also if you ever want to get some
> > aggregate data over all of your customers, you will want to have the
> > data in a single database.
> >
> > I don''t mean that you should no way in hell use separate
databases,
> > just that you will have to have very good reasons for that. The
> reasons
> > you have mentioned so far are not that good.
> >
> > This all depends, of course. If you know you''ll only have a
handful
of
> > customers, it might not be that hard to create separate db''s
for
them.
> >
> > Cheers,
> > Jarkko
> >
> >
> > On 15.12.2004, at 18:40, ChongQing Xiao wrote:
> >
> > > Hi, John:
> > >
> > > Sorry that I didn''t make it clear.
> > > Actually, I prefer use separate database since if I have many
> tables, I
> > > don''t want to each table to have a column to indicate
which
customer
> > > each record belongs to.
> >
> > >
> > > If I use different table for different customers,
> > > The thing I am not sure is
> > >
> > > Do I need to have just one rails instance and use sub domain to
> handle
> > > different customers?
> > > If yes, how to I specify different database for different
subdomain?
> > >
> > > Also, Can I have one rails installed but several different rails
> > > instance running share the same rails code?
> > >
> > > Thanks
> > > chong
> > >
> > >
> > >
> > >
> > >
> > > -----Original Message-----
> > > From:
rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > >
[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of John
> Wilger
> > > Sent: Wednesday, December 15, 2004 10:29 AM
> > > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > Subject: Re: [Rails] question about host web based application
using
> > > rails
> > >
> > > On Wed, 15 Dec 2004 10:20:09 -0600, ChongQing Xiao
> > > <CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org> wrote:
> > >> What do I mean is each customer (might be several users -
such as
> > > nurse,
> > >> doctor) needs to separate from each other so they
won''t see each
> > > other''s
> > >> data.
> > >>
> > >> That means they need to share the same table structure but I
am
not
> > > sure
> > >> What is the best way to separate the data?
> > >
> > >
> > > If you only want to use one database, then you would relate
> everything
> > > for that customer back to a "customer" record. You
don''t
necessarily
> > > need to put the customer id on every single row, though---as long
as
> > > the relational hierarchy pointed back to the correct customer and
> your
> > > app was coded to deal with this correctly.
> > >
> > > The problem with only using one database might be if you ever
want
> to
> > > split certain customers off onto their own server. It can still
be
> > > done, but it would be easier if each customer had their own
instance
> > > of the database.
> > >
> > > --
> > > Regards,
> > > John Wilger
> > >
> > > -----------
> > > Alice came to a fork in the road. "Which road do I
take?" she
asked.
> > > "Where do you want to go?" responded the Cheshire cat.
> > > "I don''t know," Alice answered.
> > > "Then," said the cat, "it doesn''t
matter."
> > > - Lewis Carrol, Alice in Wonderland
> > > _______________________________________________
> > > Rails mailing list
> > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > > _______________________________________________
> > > Rails mailing list
> > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > >
> > --
> > Jarkko Laine
> > http://jlaine.net
> >
> >
> > _______________________________________________
> > Rails mailing list
> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> >
> >
> >
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

What do you mean by "different record range for different customers"?

Let me see if I can understand:  Each customer can have many records,
right?  And each record is linked to one and only one customer, right?

If so, you''d have a table for all Customers.  Each Customer would be
represented by one row in the Customer table, and each Customer would
be uniquely idenitified by the field ''id''.  You''d
have another table
for all Records.  Each record would be uniquely identified by the
field ''id''.  You''d have (yet) another table called
Customer_Record.
That table would have two fields: customer_id and record_id.  You
would map each record to a customer in this table.  So, if customer
with id ''2'' had records with ids ''30'' and
''40'', you''d have two entries
in Customer_Record.  The first entry would have customer_id =
''2'' and
record_id = ''30'' and the second entry would have customer_id =
''2'' and
record_id = ''40''.  You could then (using SQL, or better, use
ActiveRecord) to query Customer_Record and figure out what records
belong to what customers.




On Wed, 15 Dec 2004 14:38:21 -0600, ChongQing Xiao
<CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org>
wrote:
> Hi, Joe:
> 
> If I can figure out a good way to use just one database without too much
> change for the table schema, I would definitely go with single database.
> 
> One thought I have now is to have different record range for different
> customer.
> 
> Such as
> CustomerTable
> 
>         CustomerID  IDStart     IDEnd
>         1               1               10000
>         2               100001  200000
> 
> This seems like a simple solution but I need to think about how to use
> ActiveRecord so that when filing and query data, it will be transparent
> use these range.
> 
> Any comment on this solution?
> 
> Thanks
> chong
> 
> -----Original Message-----
> From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of Joe Van Dyk
> Sent: Wednesday, December 15, 2004 2:20 PM
> To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> Subject: Re: [Rails] question about host web based application using
> rails
> 
> Why do you think you need multiple databases?
> 
> On Wed, 15 Dec 2004 13:34:06 -0600, ChongQing Xiao
> <CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org> wrote:
> > Thanks Steve. The link is great.
> > Now I just need more thinking.
> >
> > chong
> >
> > -----Original Message-----
> > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> >
[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of Steve
> Kellock
> > Sent: Wednesday, December 15, 2004 1:10 PM
> > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > Subject: Re: [Rails] question about host web based application using
> > rails
> >
> > Hi,
> >
> > There was a discussion sometime ago on this subject.  FWIW, I went the
> > multiple database route for flexibility.  I decided on that way for a
> > few different reasons, most of which I outlined here:
> >
> > http://www.rubyonrails.org/show/HowtoUseMultipleDatabases
> >
> > In my particular app, aggregation is a non-issue and I see a
> > tremendous advantage in having completely isolated
> > database/application versions.... but for every argument for seperate
> > databases, there are 3 *for* a unified database.  I''d
recommend
> > sticking with a single db unless you know you want multiple
db''s.
> >
> > :)
> >
> > Steve
> >
> > On Wed, 15 Dec 2004 20:56:49 +0200, Jarkko Laine
<jarkko-k1O+Gnc6WpmsTnJN9+BGXg@public.gmane.org>
> > wrote:
> > > Chong,
> > >
> > > I would like to point out that having a single database is
> definitely
> > > the standard way in a web application like yours. As Joe said,
you
> > > don''t need to define the user for every record in every
table. You
> > just
> > > have to design a sound relational model in which every record can
be
> > > connected to the correct user id.
> > >
> > > It will be a lot more complicated if you want to have own
database
> for
> > > every single customer. You will have to write scripts that create
a
> > new
> > > database whenever a new user registers for your service. And
> whenever
> > > you make some changes to the datamodel, you will have to change
not
> > one
> > > but all those databases. That is IMHO not how relational model is
> > > supposed to be used and it''s certainly against the DRY
(don''t repeat
> > > yourself) principle.
> > >
> > > What if you will have thousands of customers. Maintaining one
> database
> > > with all that information is certainly easier to handle than a
whole
> > > bunch of separate databases. Also if you ever want to get some
> > > aggregate data over all of your customers, you will want to have
the
> > > data in a single database.
> > >
> > > I don''t mean that you should no way in hell use separate
databases,
> > > just that you will have to have very good reasons for that. The
> > reasons
> > > you have mentioned so far are not that good.
> > >
> > > This all depends, of course. If you know you''ll only
have a handful
> of
> > > customers, it might not be that hard to create separate
db''s for
> them.
> > >
> > > Cheers,
> > > Jarkko
> > >
> > >
> > > On 15.12.2004, at 18:40, ChongQing Xiao wrote:
> > >
> > > > Hi, John:
> > > >
> > > > Sorry that I didn''t make it clear.
> > > > Actually, I prefer use separate database since if I have
many
> > tables, I
> > > > don''t want to each table to have a column to
indicate which
> customer
> > > > each record belongs to.
> > >
> > > >
> > > > If I use different table for different customers,
> > > > The thing I am not sure is
> > > >
> > > > Do I need to have just one rails instance and use sub domain
to
> > handle
> > > > different customers?
> > > > If yes, how to I specify different database for different
> subdomain?
> > > >
> > > > Also, Can I have one rails installed but several different
rails
> > > > instance running share the same rails code?
> > > >
> > > > Thanks
> > > > chong
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > -----Original Message-----
> > > > From:
rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > >
[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of John
> > Wilger
> > > > Sent: Wednesday, December 15, 2004 10:29 AM
> > > > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > > Subject: Re: [Rails] question about host web based
application
> using
> > > > rails
> > > >
> > > > On Wed, 15 Dec 2004 10:20:09 -0600, ChongQing Xiao
> > > > <CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org>
wrote:
> > > >> What do I mean is each customer (might be several users
- such as
> > > > nurse,
> > > >> doctor) needs to separate from each other so they
won''t see each
> > > > other''s
> > > >> data.
> > > >>
> > > >> That means they need to share the same table structure
but I am
> not
> > > > sure
> > > >> What is the best way to separate the data?
> > > >
> > > >
> > > > If you only want to use one database, then you would relate
> > everything
> > > > for that customer back to a "customer" record. You
don''t
> necessarily
> > > > need to put the customer id on every single row, though---as
long
> as
> > > > the relational hierarchy pointed back to the correct
customer and
> > your
> > > > app was coded to deal with this correctly.
> > > >
> > > > The problem with only using one database might be if you
ever want
> > to
> > > > split certain customers off onto their own server. It can
still be
> > > > done, but it would be easier if each customer had their own
> instance
> > > > of the database.
> > > >
> > > > --
> > > > Regards,
> > > > John Wilger
> > > >
> > > > -----------
> > > > Alice came to a fork in the road. "Which road do I
take?" she
> asked.
> > > > "Where do you want to go?" responded the Cheshire
cat.
> > > > "I don''t know," Alice answered.
> > > > "Then," said the cat, "it doesn''t
matter."
> > > > - Lewis Carrol, Alice in Wonderland
> > > > _______________________________________________
> > > > Rails mailing list
> > > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > > > _______________________________________________
> > > > Rails mailing list
> > > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > > >
> > > --
> > > Jarkko Laine
> > > http://jlaine.net
> > >
> > >
> > > _______________________________________________
> > > Rails mailing list
> > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > >
> > >
> > >
> > >
> > _______________________________________________
> > Rails mailing list
> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> > _______________________________________________
> > Rails mailing list
> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

Chong,

On 15.12.2004, at 22:38, ChongQing Xiao wrote:
>
> One thought I have now is to have different record range for different
> customer.
Please, don''t! :-)
>
> Such as
> CustomerTable
>
> 	CustomerID  IDStart 	IDEnd
> 	1		1		10000
> 	2		100001	200000
>
> This seems like a simple solution but I need to think about how to use
> ActiveRecord so that when filing and query data, it will be transparent
> use these range.
There is *absolutely* no reason for doing that. That is so against the 
relational database model principles :-/

Just do it like this:

create table customers (
	id int primary key,
  ...other customer-related attributes...
);

create table records (
	id int primary key,
	customer_id int references customers(id),
...other record-related attributes...
);

That is the way relational databases are meant to be (leave the 
references part away if you''re using MySQL). Besides, your concern 
about having the customer id in the record table is not very valid. 
Like I said, it''s how relational model works. Also, Rails will then 
automatically give you the right records for a given customer (or vice 
versa), without you sweating about the customer_id yourself. You *will* 
need the customer id in the record table for Rails associations to work 
at all.

I would recommend that you''d take a look at some relational database 
design tutorial. One classic is Philip Greenspun''s ''SQL for
Web Nerds*
(http://philip.greenspun.com/sql/). If anyone knows a better 
alternative, please give some pointers.

//jarkko
>
> Any comment on this solution?
>
> Thanks
> chong
>
>
> -----Original Message-----
> From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of Joe Van Dyk
> Sent: Wednesday, December 15, 2004 2:20 PM
> To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> Subject: Re: [Rails] question about host web based application using
> rails
>
> Why do you think you need multiple databases?
>
>
> On Wed, 15 Dec 2004 13:34:06 -0600, ChongQing Xiao
> <CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org> wrote:
>> Thanks Steve. The link is great.
>> Now I just need more thinking.
>>
>> chong
>>
>> -----Original Message-----
>> From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
>>
[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of Steve
> Kellock
>> Sent: Wednesday, December 15, 2004 1:10 PM
>> To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
>> Subject: Re: [Rails] question about host web based application using
>> rails
>>
>> Hi,
>>
>> There was a discussion sometime ago on this subject.  FWIW, I went the
>> multiple database route for flexibility.  I decided on that way for a
>> few different reasons, most of which I outlined here:
>>
>> http://www.rubyonrails.org/show/HowtoUseMultipleDatabases
>>
>> In my particular app, aggregation is a non-issue and I see a
>> tremendous advantage in having completely isolated
>> database/application versions.... but for every argument for seperate
>> databases, there are 3 *for* a unified database.  I''d
recommend
>> sticking with a single db unless you know you want multiple
db''s.
>>
>> :)
>>
>> Steve
>>
>> On Wed, 15 Dec 2004 20:56:49 +0200, Jarkko Laine
<jarkko-k1O+Gnc6WpmsTnJN9+BGXg@public.gmane.org>
>> wrote:
>>> Chong,
>>>
>>> I would like to point out that having a single database is
> definitely
>>> the standard way in a web application like yours. As Joe said, you
>>> don''t need to define the user for every record in every
table. You
>> just
>>> have to design a sound relational model in which every record can
be
>>> connected to the correct user id.
>>>
>>> It will be a lot more complicated if you want to have own database
> for
>>> every single customer. You will have to write scripts that create a
>> new
>>> database whenever a new user registers for your service. And
> whenever
>>> you make some changes to the datamodel, you will have to change not
>> one
>>> but all those databases. That is IMHO not how relational model is
>>> supposed to be used and it''s certainly against the DRY
(don''t repeat
>>> yourself) principle.
>>>
>>> What if you will have thousands of customers. Maintaining one
> database
>>> with all that information is certainly easier to handle than a
whole
>>> bunch of separate databases. Also if you ever want to get some
>>> aggregate data over all of your customers, you will want to have
the
>>> data in a single database.
>>>
>>> I don''t mean that you should no way in hell use separate
databases,
>>> just that you will have to have very good reasons for that. The
>> reasons
>>> you have mentioned so far are not that good.
>>>
>>> This all depends, of course. If you know you''ll only have
a handful
> of
>>> customers, it might not be that hard to create separate
db''s for
> them.
>>>
>>> Cheers,
>>> Jarkko
>>>
>>>
>>> On 15.12.2004, at 18:40, ChongQing Xiao wrote:
>>>
>>>> Hi, John:
>>>>
>>>> Sorry that I didn''t make it clear.
>>>> Actually, I prefer use separate database since if I have many
>> tables, I
>>>> don''t want to each table to have a column to indicate
which
> customer
>>>> each record belongs to.
>>>
>>>>
>>>> If I use different table for different customers,
>>>> The thing I am not sure is
>>>>
>>>> Do I need to have just one rails instance and use sub domain to
>> handle
>>>> different customers?
>>>> If yes, how to I specify different database for different
> subdomain?
>>>>
>>>> Also, Can I have one rails installed but several different
rails
>>>> instance running share the same rails code?
>>>>
>>>> Thanks
>>>> chong
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> -----Original Message-----
>>>> From:
rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
>>>>
[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of John
>> Wilger
>>>> Sent: Wednesday, December 15, 2004 10:29 AM
>>>> To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
>>>> Subject: Re: [Rails] question about host web based application
> using
>>>> rails
>>>>
>>>> On Wed, 15 Dec 2004 10:20:09 -0600, ChongQing Xiao
>>>> <CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org> wrote:
>>>>> What do I mean is each customer (might be several users -
such as
>>>> nurse,
>>>>> doctor) needs to separate from each other so they
won''t see each
>>>> other''s
>>>>> data.
>>>>>
>>>>> That means they need to share the same table structure but
I am
> not
>>>> sure
>>>>> What is the best way to separate the data?
>>>>
>>>>
>>>> If you only want to use one database, then you would relate
>> everything
>>>> for that customer back to a "customer" record. You
don''t
> necessarily
>>>> need to put the customer id on every single row, though---as
long
> as
>>>> the relational hierarchy pointed back to the correct customer
and
>> your
>>>> app was coded to deal with this correctly.
>>>>
>>>> The problem with only using one database might be if you ever
want
>> to
>>>> split certain customers off onto their own server. It can still
be
>>>> done, but it would be easier if each customer had their own
> instance
>>>> of the database.
>>>>
>>>> --
>>>> Regards,
>>>> John Wilger
>>>>
>>>> -----------
>>>> Alice came to a fork in the road. "Which road do I
take?" she
> asked.
>>>> "Where do you want to go?" responded the Cheshire
cat.
>>>> "I don''t know," Alice answered.
>>>> "Then," said the cat, "it doesn''t
matter."
>>>> - Lewis Carrol, Alice in Wonderland
>>>> _______________________________________________
>>>> Rails mailing list
>>>> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
>>>> http://lists.rubyonrails.org/mailman/listinfo/rails
>>>> _______________________________________________
>>>> Rails mailing list
>>>> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
>>>> http://lists.rubyonrails.org/mailman/listinfo/rails
>>>>
>>> --
>>> Jarkko Laine
>>> http://jlaine.net
>>>
>>>
>>> _______________________________________________
>>> Rails mailing list
>>> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
>>> http://lists.rubyonrails.org/mailman/listinfo/rails
>>>
>>>
>>>
>>>
>> _______________________________________________
>> Rails mailing list
>> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
>> http://lists.rubyonrails.org/mailman/listinfo/rails
>> _______________________________________________
>> Rails mailing list
>> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
>> http://lists.rubyonrails.org/mailman/listinfo/rails
>>
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
--
Jarkko Laine
http://jlaine.net


_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

Hi, Joe:

To make it clear, each customer means the customer who uses the software
(say a small clinic).

In your suggestion, what will happen if I have many tables? 
For example, if I have the following tables
Patient, Provider, ApptType and so on, 

Are you suggesting that I need to have a customer_? Mapping table for
each table I have.

In my solution, I only need have one table and the ID range will applied
to all tables in my database.

Thanks
chong


-----Original Message-----
From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of Joe Van Dyk
Sent: Wednesday, December 15, 2004 2:50 PM
To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
Subject: Re: [Rails] question about host web based application using
rails

What do you mean by "different record range for different customers"?

Let me see if I can understand:  Each customer can have many records,
right?  And each record is linked to one and only one customer, right?

If so, you''d have a table for all Customers.  Each Customer would be
represented by one row in the Customer table, and each Customer would
be uniquely idenitified by the field ''id''.  You''d
have another table
for all Records.  Each record would be uniquely identified by the
field ''id''.  You''d have (yet) another table called
Customer_Record.
That table would have two fields: customer_id and record_id.  You
would map each record to a customer in this table.  So, if customer
with id ''2'' had records with ids ''30'' and
''40'', you''d have two entries
in Customer_Record.  The first entry would have customer_id =
''2'' and
record_id = ''30'' and the second entry would have customer_id =
''2'' and
record_id = ''40''.  You could then (using SQL, or better, use
ActiveRecord) to query Customer_Record and figure out what records
belong to what customers.




On Wed, 15 Dec 2004 14:38:21 -0600, ChongQing Xiao
<CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org>
wrote:
> Hi, Joe:
> 
> If I can figure out a good way to use just one database without too
much
> change for the table schema, I would definitely go with single
database.
> 
> One thought I have now is to have different record range for different
> customer.
> 
> Such as
> CustomerTable
> 
>         CustomerID  IDStart     IDEnd
>         1               1               10000
>         2               100001  200000
> 
> This seems like a simple solution but I need to think about how to use
> ActiveRecord so that when filing and query data, it will be
transparent
> use these range.
> 
> Any comment on this solution?
> 
> Thanks
> chong
> 
> -----Original Message-----
> From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of Joe Van Dyk
> Sent: Wednesday, December 15, 2004 2:20 PM
> To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> Subject: Re: [Rails] question about host web based application using
> rails
> 
> Why do you think you need multiple databases?
> 
> On Wed, 15 Dec 2004 13:34:06 -0600, ChongQing Xiao
> <CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org> wrote:
> > Thanks Steve. The link is great.
> > Now I just need more thinking.
> >
> > chong
> >
> > -----Original Message-----
> > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> >
[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of Steve
> Kellock
> > Sent: Wednesday, December 15, 2004 1:10 PM
> > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > Subject: Re: [Rails] question about host web based application using
> > rails
> >
> > Hi,
> >
> > There was a discussion sometime ago on this subject.  FWIW, I went
the
> > multiple database route for flexibility.  I decided on that way for
a
> > few different reasons, most of which I outlined here:
> >
> > http://www.rubyonrails.org/show/HowtoUseMultipleDatabases
> >
> > In my particular app, aggregation is a non-issue and I see a
> > tremendous advantage in having completely isolated
> > database/application versions.... but for every argument for
seperate
> > databases, there are 3 *for* a unified database.  I''d
recommend
> > sticking with a single db unless you know you want multiple
db''s.
> >
> > :)
> >
> > Steve
> >
> > On Wed, 15 Dec 2004 20:56:49 +0200, Jarkko Laine
<jarkko-k1O+Gnc6WpmsTnJN9+BGXg@public.gmane.org>
> > wrote:
> > > Chong,
> > >
> > > I would like to point out that having a single database is
> definitely
> > > the standard way in a web application like yours. As Joe said,
you
> > > don''t need to define the user for every record in every
table. You
> > just
> > > have to design a sound relational model in which every record can
be
> > > connected to the correct user id.
> > >
> > > It will be a lot more complicated if you want to have own
database
> for
> > > every single customer. You will have to write scripts that create
a
> > new
> > > database whenever a new user registers for your service. And
> whenever
> > > you make some changes to the datamodel, you will have to change
not
> > one
> > > but all those databases. That is IMHO not how relational model is
> > > supposed to be used and it''s certainly against the DRY
(don''t
repeat
> > > yourself) principle.
> > >
> > > What if you will have thousands of customers. Maintaining one
> database
> > > with all that information is certainly easier to handle than a
whole
> > > bunch of separate databases. Also if you ever want to get some
> > > aggregate data over all of your customers, you will want to have
the
> > > data in a single database.
> > >
> > > I don''t mean that you should no way in hell use separate
databases,
> > > just that you will have to have very good reasons for that. The
> > reasons
> > > you have mentioned so far are not that good.
> > >
> > > This all depends, of course. If you know you''ll only
have a
handful
> of
> > > customers, it might not be that hard to create separate
db''s for
> them.
> > >
> > > Cheers,
> > > Jarkko
> > >
> > >
> > > On 15.12.2004, at 18:40, ChongQing Xiao wrote:
> > >
> > > > Hi, John:
> > > >
> > > > Sorry that I didn''t make it clear.
> > > > Actually, I prefer use separate database since if I have
many
> > tables, I
> > > > don''t want to each table to have a column to
indicate which
> customer
> > > > each record belongs to.
> > >
> > > >
> > > > If I use different table for different customers,
> > > > The thing I am not sure is
> > > >
> > > > Do I need to have just one rails instance and use sub domain
to
> > handle
> > > > different customers?
> > > > If yes, how to I specify different database for different
> subdomain?
> > > >
> > > > Also, Can I have one rails installed but several different
rails
> > > > instance running share the same rails code?
> > > >
> > > > Thanks
> > > > chong
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > -----Original Message-----
> > > > From:
rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > >
[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of John
> > Wilger
> > > > Sent: Wednesday, December 15, 2004 10:29 AM
> > > > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > > Subject: Re: [Rails] question about host web based
application
> using
> > > > rails
> > > >
> > > > On Wed, 15 Dec 2004 10:20:09 -0600, ChongQing Xiao
> > > > <CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org>
wrote:
> > > >> What do I mean is each customer (might be several users
- such
as
> > > > nurse,
> > > >> doctor) needs to separate from each other so they
won''t see
each
> > > > other''s
> > > >> data.
> > > >>
> > > >> That means they need to share the same table structure
but I am
> not
> > > > sure
> > > >> What is the best way to separate the data?
> > > >
> > > >
> > > > If you only want to use one database, then you would relate
> > everything
> > > > for that customer back to a "customer" record. You
don''t
> necessarily
> > > > need to put the customer id on every single row, though---as
long
> as
> > > > the relational hierarchy pointed back to the correct
customer
and
> > your
> > > > app was coded to deal with this correctly.
> > > >
> > > > The problem with only using one database might be if you
ever
want
> > to
> > > > split certain customers off onto their own server. It can
still
be
> > > > done, but it would be easier if each customer had their own
> instance
> > > > of the database.
> > > >
> > > > --
> > > > Regards,
> > > > John Wilger
> > > >
> > > > -----------
> > > > Alice came to a fork in the road. "Which road do I
take?" she
> asked.
> > > > "Where do you want to go?" responded the Cheshire
cat.
> > > > "I don''t know," Alice answered.
> > > > "Then," said the cat, "it doesn''t
matter."
> > > > - Lewis Carrol, Alice in Wonderland
> > > > _______________________________________________
> > > > Rails mailing list
> > > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > > > _______________________________________________
> > > > Rails mailing list
> > > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > > >
> > > --
> > > Jarkko Laine
> > > http://jlaine.net
> > >
> > >
> > > _______________________________________________
> > > Rails mailing list
> > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > >
> > >
> > >
> > >
> > _______________________________________________
> > Rails mailing list
> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> > _______________________________________________
> > Rails mailing list
> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

On 15.12.2004, at 22:50, Joe Van Dyk wrote:
> What do you mean by "different record range for different
customers"?
>
> Let me see if I can understand:  Each customer can have many records,
> right?  And each record is linked to one and only one customer, right?
>
> If so, you''d have a table for all Customers.  Each Customer would
be
> represented by one row in the Customer table, and each Customer would
> be uniquely idenitified by the field ''id''. 
You''d have another table
> for all Records.  Each record would be uniquely identified by the
> field ''id''.  You''d have (yet) another table
called Customer_Record.
> That table would have two fields: customer_id and record_id.
You don''t need that extra table if one record can only be connected to 
one customer (like it would be anyway if you''d have different
db''s for
different customers). In the RDBMS land that would be called a 
one-to-many relationship (one customer can have many records, but one 
record always belongs to one customer). You only need the extra table 
in case of many-to-many relationships.

Just put a foreign key field (customer_id) in the records table (see 
the post I just sent).

//jarkko

_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

Whoops.  You don''t need a separate table for a many-to-one
relationship (what you have).  You do need one for a many-to-many
(say, if a Record could be for more than one Customer), which is what
I mainly work with.  My bad.

So as Jarkko pointed out, just have the customer_id in each Record entry.  


On Wed, 15 Dec 2004 14:57:22 -0600, ChongQing Xiao
<CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org>
wrote:
> Hi, Joe:
> 
> To make it clear, each customer means the customer who uses the software
> (say a small clinic).
> 
> In your suggestion, what will happen if I have many tables?
> For example, if I have the following tables
> Patient, Provider, ApptType and so on,
> 
> Are you suggesting that I need to have a customer_? Mapping table for
> each table I have.
> 
> In my solution, I only need have one table and the ID range will applied
> to all tables in my database.
> 
> Thanks
> chong
> 
> -----Original Message-----
> From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of Joe Van Dyk
> Sent: Wednesday, December 15, 2004 2:50 PM
> To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> Subject: Re: [Rails] question about host web based application using
> rails
> 
> What do you mean by "different record range for different
customers"?
> 
> Let me see if I can understand:  Each customer can have many records,
> right?  And each record is linked to one and only one customer, right?
> 
> If so, you''d have a table for all Customers.  Each Customer would
be
> represented by one row in the Customer table, and each Customer would
> be uniquely idenitified by the field ''id''. 
You''d have another table
> for all Records.  Each record would be uniquely identified by the
> field ''id''.  You''d have (yet) another table
called Customer_Record.
> That table would have two fields: customer_id and record_id.  You
> would map each record to a customer in this table.  So, if customer
> with id ''2'' had records with ids ''30''
and ''40'', you''d have two entries
> in Customer_Record.  The first entry would have customer_id =
''2'' and
> record_id = ''30'' and the second entry would have
customer_id = ''2'' and
> record_id = ''40''.  You could then (using SQL, or better,
use
> ActiveRecord) to query Customer_Record and figure out what records
> belong to what customers.
> 
> 
> On Wed, 15 Dec 2004 14:38:21 -0600, ChongQing Xiao
> <CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org> wrote:
> > Hi, Joe:
> >
> > If I can figure out a good way to use just one database without too
> much
> > change for the table schema, I would definitely go with single
> database.
> >
> > One thought I have now is to have different record range for different
> > customer.
> >
> > Such as
> > CustomerTable
> >
> >         CustomerID  IDStart     IDEnd
> >         1               1               10000
> >         2               100001  200000
> >
> > This seems like a simple solution but I need to think about how to use
> > ActiveRecord so that when filing and query data, it will be
> transparent
> > use these range.
> >
> > Any comment on this solution?
> >
> > Thanks
> > chong
> >
> > -----Original Message-----
> > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> >
[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of Joe Van Dyk
> > Sent: Wednesday, December 15, 2004 2:20 PM
> > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > Subject: Re: [Rails] question about host web based application using
> > rails
> >
> > Why do you think you need multiple databases?
> >
> > On Wed, 15 Dec 2004 13:34:06 -0600, ChongQing Xiao
> > <CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org> wrote:
> > > Thanks Steve. The link is great.
> > > Now I just need more thinking.
> > >
> > > chong
> > >
> > > -----Original Message-----
> > > From:
rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > >
[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of Steve
> > Kellock
> > > Sent: Wednesday, December 15, 2004 1:10 PM
> > > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > Subject: Re: [Rails] question about host web based application
using
> > > rails
> > >
> > > Hi,
> > >
> > > There was a discussion sometime ago on this subject.  FWIW, I
went
> the
> > > multiple database route for flexibility.  I decided on that way
for
> a
> > > few different reasons, most of which I outlined here:
> > >
> > > http://www.rubyonrails.org/show/HowtoUseMultipleDatabases
> > >
> > > In my particular app, aggregation is a non-issue and I see a
> > > tremendous advantage in having completely isolated
> > > database/application versions.... but for every argument for
> seperate
> > > databases, there are 3 *for* a unified database.  I''d
recommend
> > > sticking with a single db unless you know you want multiple
db''s.
> > >
> > > :)
> > >
> > > Steve
> > >
> > > On Wed, 15 Dec 2004 20:56:49 +0200, Jarkko Laine
<jarkko-k1O+Gnc6WpmsTnJN9+BGXg@public.gmane.org>
> > > wrote:
> > > > Chong,
> > > >
> > > > I would like to point out that having a single database is
> > definitely
> > > > the standard way in a web application like yours. As Joe
said, you
> > > > don''t need to define the user for every record in
every table. You
> > > just
> > > > have to design a sound relational model in which every
record can
> be
> > > > connected to the correct user id.
> > > >
> > > > It will be a lot more complicated if you want to have own
database
> > for
> > > > every single customer. You will have to write scripts that
create
> a
> > > new
> > > > database whenever a new user registers for your service. And
> > whenever
> > > > you make some changes to the datamodel, you will have to
change
> not
> > > one
> > > > but all those databases. That is IMHO not how relational
model is
> > > > supposed to be used and it''s certainly against the
DRY (don''t
> repeat
> > > > yourself) principle.
> > > >
> > > > What if you will have thousands of customers. Maintaining
one
> > database
> > > > with all that information is certainly easier to handle than
a
> whole
> > > > bunch of separate databases. Also if you ever want to get
some
> > > > aggregate data over all of your customers, you will want to
have
> the
> > > > data in a single database.
> > > >
> > > > I don''t mean that you should no way in hell use
separate
> databases,
> > > > just that you will have to have very good reasons for that.
The
> > > reasons
> > > > you have mentioned so far are not that good.
> > > >
> > > > This all depends, of course. If you know you''ll
only have a
> handful
> > of
> > > > customers, it might not be that hard to create separate
db''s for
> > them.
> > > >
> > > > Cheers,
> > > > Jarkko
> > > >
> > > >
> > > > On 15.12.2004, at 18:40, ChongQing Xiao wrote:
> > > >
> > > > > Hi, John:
> > > > >
> > > > > Sorry that I didn''t make it clear.
> > > > > Actually, I prefer use separate database since if I
have many
> > > tables, I
> > > > > don''t want to each table to have a column to
indicate which
> > customer
> > > > > each record belongs to.
> > > >
> > > > >
> > > > > If I use different table for different customers,
> > > > > The thing I am not sure is
> > > > >
> > > > > Do I need to have just one rails instance and use sub
domain to
> > > handle
> > > > > different customers?
> > > > > If yes, how to I specify different database for
different
> > subdomain?
> > > > >
> > > > > Also, Can I have one rails installed but several
different rails
> > > > > instance running share the same rails code?
> > > > >
> > > > > Thanks
> > > > > chong
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > -----Original Message-----
> > > > > From:
rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > > >
[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of John
> > > Wilger
> > > > > Sent: Wednesday, December 15, 2004 10:29 AM
> > > > > To:
rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > > > Subject: Re: [Rails] question about host web based
application
> > using
> > > > > rails
> > > > >
> > > > > On Wed, 15 Dec 2004 10:20:09 -0600, ChongQing Xiao
> > > > > <CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org>
wrote:
> > > > >> What do I mean is each customer (might be several
users - such
> as
> > > > > nurse,
> > > > >> doctor) needs to separate from each other so they
won''t see
> each
> > > > > other''s
> > > > >> data.
> > > > >>
> > > > >> That means they need to share the same table
structure but I am
> > not
> > > > > sure
> > > > >> What is the best way to separate the data?
> > > > >
> > > > >
> > > > > If you only want to use one database, then you would
relate
> > > everything
> > > > > for that customer back to a "customer"
record. You don''t
> > necessarily
> > > > > need to put the customer id on every single row,
though---as
> long
> > as
> > > > > the relational hierarchy pointed back to the correct
customer
> and
> > > your
> > > > > app was coded to deal with this correctly.
> > > > >
> > > > > The problem with only using one database might be if
you ever
> want
> > > to
> > > > > split certain customers off onto their own server. It
can still
> be
> > > > > done, but it would be easier if each customer had their
own
> > instance
> > > > > of the database.
> > > > >
> > > > > --
> > > > > Regards,
> > > > > John Wilger
> > > > >
> > > > > -----------
> > > > > Alice came to a fork in the road. "Which road do I
take?" she
> > asked.
> > > > > "Where do you want to go?" responded the
Cheshire cat.
> > > > > "I don''t know," Alice answered.
> > > > > "Then," said the cat, "it
doesn''t matter."
> > > > > - Lewis Carrol, Alice in Wonderland
> > > > > _______________________________________________
> > > > > Rails mailing list
> > > > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > > > > _______________________________________________
> > > > > Rails mailing list
> > > > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > > > >
> > > > --
> > > > Jarkko Laine
> > > > http://jlaine.net
> > > >
> > > >
> > > > _______________________________________________
> > > > Rails mailing list
> > > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > > >
> > > >
> > > >
> > > >
> > > _______________________________________________
> > > Rails mailing list
> > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > > _______________________________________________
> > > Rails mailing list
> > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > >
> > _______________________________________________
> > Rails mailing list
> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> > _______________________________________________
> > Rails mailing list
> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

Chong,

On 15.12.2004, at 22:57, ChongQing Xiao wrote:
> Hi, Joe:
>
> To make it clear, each customer means the customer who uses the 
> software
> (say a small clinic).
>
> In your suggestion, what will happen if I have many tables?
> For example, if I have the following tables
> Patient, Provider, ApptType and so on,
>
> Are you suggesting that I need to have a customer_? Mapping table for
> each table I have.
>
> In my solution, I only need have one table and the ID range will 
> applied
> to all tables in my database.
Yes, but that is not a valid relational database model. You will save 
yourself a lot of frustration later on if you design your database 
according to the relational model. Please get yourself some book or a 
tutorial on this, it is *essential* for your rails app.

Cheers,
Jarkko

--
Jarkko Laine
http://jlaine.net


_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

I think I might not express my question clearly.

This is the case.
I plan to design a web based EMR system to be used by small clinics.
The database might have many tables, say patient, provider, schedule,
user, user_role.

For different clinics to use my web based application, their data has to
be separated from each other.

If I use one database, one suggesting I get is to create a one to many
mapping table for each table in my database. (Joe, please correct if I
have a wrong assumption here)

Say Customer_to_Patient, Customer_to_Provider, Customer_to_Schedule and
so on. (Customer means each clinic that will use my application)

I think this will work but sounds like a big overhead.

Do people have any other suggestion?

Thanks
chong



-----Original Message-----
From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of Jarkko Laine
Sent: Wednesday, December 15, 2004 3:04 PM
To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
Subject: Re: [Rails] question about host web based application using
rails

Chong,

On 15.12.2004, at 22:57, ChongQing Xiao wrote:
> Hi, Joe:
>
> To make it clear, each customer means the customer who uses the 
> software
> (say a small clinic).
>
> In your suggestion, what will happen if I have many tables?
> For example, if I have the following tables
> Patient, Provider, ApptType and so on,
>
> Are you suggesting that I need to have a customer_? Mapping table for
> each table I have.
>
> In my solution, I only need have one table and the ID range will 
> applied
> to all tables in my database.
Yes, but that is not a valid relational database model. You will save 
yourself a lot of frustration later on if you design your database 
according to the relational model. Please get yourself some book or a 
tutorial on this, it is *essential* for your rails app.

Cheers,
Jarkko

--
Jarkko Laine
http://jlaine.net

I made a mistake in one of my previous postings.  The relationship you
described above is a one-to-many relationship.  One customer has many
schedules, one customer has many patients, etc.

You do not need to have a joining table (like Customer_Patient) with
this type of relationship.  You do need a joining table if you have a
many-to-many relationship.

So you don''t need all those Customer_Provider tables.  You''d
have one
Customer table and one Provider table, and store the customer_id in
each Provider entry.


On Wed, 15 Dec 2004 16:27:40 -0600, ChongQing Xiao
<CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org>
wrote:
> I think I might not express my question clearly.
> 
> This is the case.
> I plan to design a web based EMR system to be used by small clinics.
> The database might have many tables, say patient, provider, schedule,
> user, user_role.
> 
> For different clinics to use my web based application, their data has to
> be separated from each other.
> 
> If I use one database, one suggesting I get is to create a one to many
> mapping table for each table in my database. (Joe, please correct if I
> have a wrong assumption here)
> 
> Say Customer_to_Patient, Customer_to_Provider, Customer_to_Schedule and
> so on. (Customer means each clinic that will use my application)
> 
> I think this will work but sounds like a big overhead.
> 
> Do people have any other suggestion?
> 
> Thanks
> chong
> 
> -----Original Message-----
> From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of Jarkko Laine
> Sent: Wednesday, December 15, 2004 3:04 PM
> To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> Subject: Re: [Rails] question about host web based application using
> rails
> 
> Chong,
> 
> On 15.12.2004, at 22:57, ChongQing Xiao wrote:
> 
> > Hi, Joe:
> >
> > To make it clear, each customer means the customer who uses the
> > software
> > (say a small clinic).
> >
> > In your suggestion, what will happen if I have many tables?
> > For example, if I have the following tables
> > Patient, Provider, ApptType and so on,
> >
> > Are you suggesting that I need to have a customer_? Mapping table for
> > each table I have.
> >
> > In my solution, I only need have one table and the ID range will
> > applied
> > to all tables in my database.
> 
> Yes, but that is not a valid relational database model. You will save
> yourself a lot of frustration later on if you design your database
> according to the relational model. Please get yourself some book or a
> tutorial on this, it is *essential* for your rails app.
> 
> Cheers,
> Jarkko
> 
> --
> Jarkko Laine
> http://jlaine.net
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

* ChongQing Xiao (CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org) [041215
17:30]:
> I plan to design a web based EMR system to be used by small clinics.
> The database might have many tables, say patient, provider, schedule,
> user, user_role.
> 
> For different clinics to use my web based application, their data has to
> be separated from each other.
> 
> If I use one database, one suggesting I get is to create a one to many
> mapping table for each table in my database. (Joe, please correct if I
> have a wrong assumption here)
> 
> Say Customer_to_Patient, Customer_to_Provider, Customer_to_Schedule and
> so on. (Customer means each clinic that will use my application)
> 
> I think this will work but sounds like a big overhead.
Getting more specific probably requires more knowledge of the business
arrangements your customers will actually have (which might actually
require a crystal-ball on your part since these might not be made clear
until you bring on a new customer with a different setup).  The overhead
for mapping customers to their various associated resources shouldn''t
be
that high -- and if it is, profile the code and optimize it. 

Your business domain (with which I''m familiar, having maintained and
working on rebuilding a medical workflow application -- so the customer
entities have the same general layout as in an EMR) requires a degree of
freedom at the customer<->resources level that is best represented
relationally as either one-to-many or many-to-many mappings (depending
on whether you''ve got things like subcontractors involved or not,
hopefully not), and I can say with some confidence that the developer
time gained by doing this the clean relational way with a
high-productivity tool like Rails will pay off in the long-run.

If you find that having all these mappings in place slows things down,
do your profiling and keep in mind a couple of obvious possible
optimizations:

  - Many times interactivity is hampered by being forced to repeatedly
    pull together disparate table relationships to build a state map for
    things like permission checking ("can user X update object Y?",
"can
    customer A view the list of documents from Provider B?", etc.).
    This can often be relieved by loading the relationship graph into
    the session once and keeping it around over many transactions.  This
    can become slightly more complex when you need to be informed that
    some component has changed, but can be dealt with.  Storing
    dependent object graphs as CLOBs/BLOBs isn''t unheard of either.

  - Speeding up session and database access through fast caching can be
    effective as well.  I keep speaking about this in the future-tense,
    but memcached looks as if it will make a great fit with Rails for
    fast session retrieval (probably even breaking session affinity in a
    cluster) as well as speeding object lookups.  For many objects
    (Customers, Providers) an EMR is read-heavy.  Even for the documents
    in the EMR typically they are written much less than they are read.
    This provides a potentially good mix for optimizing by methods like
    these.

Best,
Rick
-- 
 http://www.rickbradley.com    MUPRN: 325
                       |  one empty set, so
   random email haiku  |  nothing can contain IlyaZ>
                       |  more than one of them.

Chong,

On 16.12.2004, at 00:27, ChongQing Xiao wrote:
> I think I might not express my question clearly.
I think we understand your question pretty well. Your problem field 
really isn''t that different from many others, but it is really hard to 
help you if you don''t trust or believe what we have said in our 
previous replies.
>
> This is the case.
> I plan to design a web based EMR system to be used by small clinics.
> The database might have many tables, say patient, provider, schedule,
> user, user_role.
Yes, that is how relational databases and a vast majority of all Rails 
applications work.
>
> For different clinics to use my web based application, their data has 
> to
> be separated from each other.
This is really the case in 99.999% of all database applications and we 
haven''t given (and probably won''t give) you any advices that
wouldn''t
have taken that into account.
>
> If I use one database, one suggesting I get is to create a one to many
> mapping table for each table in my database. (Joe, please correct if I
> have a wrong assumption here)
No, you won''t. Like I said in my previous message, a one-to-many 
relationship doesn''t need a mapping table, it only needs a foreign key 
column in one of the tables that form the relationship (e.g. 
customer_id in records table).
>
> Say Customer_to_Patient, Customer_to_Provider, Customer_to_Schedule and
> so on. (Customer means each clinic that will use my application)
They should be no different than the relationship described above. 
Unless of course they are many-to-many relationships, when you will 
need the mapping table. But that is another story.
>
> I think this will work but sounds like a big overhead.
It might sound but it *really isn''t*. After you have designed your 
model well, Rails will go a long way helping you with the associations. 
In fact you won''t need to sweat because of the foreign keys or mapping 
tables a whole lot at all. You just say Customer#records in your rails 
app and you will get all the records that belong to that single 
customer. But you will need to have your model designed sanely, 
otherwise even Rails (or ActiveRecord, actually) can''t help you. It 
can''t understand that records 10000 to 20000 belong to a single 
customer etc. without much manual work. All that work can be avoided if 
you just give up and design your database using the relational model. 
And in your proposal, what would you do when that given customers gets 
his 10002th record?

Excuse me if I start to sound like a broken record, but I really feel 
you would need to get a better grip on the relational database design 
before you start tackling a project of that class. It doesn''t really 
matter if you''re having one database or separate databases for each 
customer, they still have to be designed relationally and normalized to 
a high degree. Otherwise you can''t get all the help Rails has to offer 
you and you will shoot yourself in your own foot big time.

Please, try to find a book on this subject or read some of many 
introductions in the web. Here''s a nice and short tutorial on database 
design:
http://www.databasejournal.com/sqletc/article.php/1469521

You should probably also read the related article on database 
normalization:
http://www.databasejournal.com/sqletc/article.php/26861_1474411_1

Cheers,
Jarkko

--
Jarkko Laine
http://jlaine.net


_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

Hi, Jarkko:

Just want to make sure I understand your suggestion.
So in each table (say patient, provider, schedule and so on)
I need to have an extra column to indicate which customer the record
belongs too. (Say customer_ID for each table which will pointed to a
customer table )

Actually, this is my first thought but I was hoping there is other
better way to do it since I don''t like have to go through customer# to
access each table. 

I agree having a cusotmerID in each table is a better solution than my
original hack suggestion.

Thanks again for all the suggestions.
Chong

-----Original Message-----
From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of Jarkko Laine
Sent: Thursday, December 16, 2004 1:51 AM
To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
Subject: Re: [Rails] question about host web based application using
rails

Chong,

On 16.12.2004, at 00:27, ChongQing Xiao wrote:
> I think I might not express my question clearly.
I think we understand your question pretty well. Your problem field 
really isn''t that different from many others, but it is really hard to 
help you if you don''t trust or believe what we have said in our 
previous replies.
>
> This is the case.
> I plan to design a web based EMR system to be used by small clinics.
> The database might have many tables, say patient, provider, schedule,
> user, user_role.
Yes, that is how relational databases and a vast majority of all Rails 
applications work.
>
> For different clinics to use my web based application, their data has 
> to
> be separated from each other.
This is really the case in 99.999% of all database applications and we 
haven''t given (and probably won''t give) you any advices that
wouldn''t
have taken that into account.
>
> If I use one database, one suggesting I get is to create a one to many
> mapping table for each table in my database. (Joe, please correct if I
> have a wrong assumption here)
No, you won''t. Like I said in my previous message, a one-to-many 
relationship doesn''t need a mapping table, it only needs a foreign key 
column in one of the tables that form the relationship (e.g. 
customer_id in records table).
>
> Say Customer_to_Patient, Customer_to_Provider, Customer_to_Schedule
and
> so on. (Customer means each clinic that will use my application)
They should be no different than the relationship described above. 
Unless of course they are many-to-many relationships, when you will 
need the mapping table. But that is another story.
>
> I think this will work but sounds like a big overhead.
It might sound but it *really isn''t*. After you have designed your 
model well, Rails will go a long way helping you with the associations. 
In fact you won''t need to sweat because of the foreign keys or mapping 
tables a whole lot at all. You just say Customer#records in your rails 
app and you will get all the records that belong to that single 
customer. But you will need to have your model designed sanely, 
otherwise even Rails (or ActiveRecord, actually) can''t help you. It 
can''t understand that records 10000 to 20000 belong to a single 
customer etc. without much manual work. All that work can be avoided if 
you just give up and design your database using the relational model. 
And in your proposal, what would you do when that given customers gets 
his 10002th record?

Excuse me if I start to sound like a broken record, but I really feel 
you would need to get a better grip on the relational database design 
before you start tackling a project of that class. It doesn''t really 
matter if you''re having one database or separate databases for each 
customer, they still have to be designed relationally and normalized to 
a high degree. Otherwise you can''t get all the help Rails has to offer 
you and you will shoot yourself in your own foot big time.

Please, try to find a book on this subject or read some of many 
introductions in the web. Here''s a nice and short tutorial on database 
design:
http://www.databasejournal.com/sqletc/article.php/1469521

You should probably also read the related article on database 
normalization:
http://www.databasejournal.com/sqletc/article.php/26861_1474411_1

Cheers,
Jarkko

--
Jarkko Laine
http://jlaine.net

Chong,

On 16.12.2004, at 17:26, ChongQing Xiao wrote:
> Hi, Jarkko:
>
> Just want to make sure I understand your suggestion.
> So in each table (say patient, provider, schedule and so on)
> I need to have an extra column to indicate which customer the record
> belongs too. (Say customer_ID for each table which will pointed to a
> customer table )
>
> Actually, this is my first thought but I was hoping there is other
> better way to do it
It''s not really a matter of better or worse ways. If (and because 
you''re using Rails, when) you are using a relational database to store 
your data, that is *THE WAY* associations between two tables are made. 
Please try to believe that and read the tutorials I pointed you to in 
my previous reply. You really can''t develop a Rails app without the 
basic understanding of how the relational model works.
> since I don''t like have to go through customer# to
> access each table.
You don''t have to do that. That just is helluva lot easier to write 
customer.records than Records.find_all "customer_id = #{customer.id}",
if you already have a customer object at hand.

//jarkko

--
Jarkko Laine
http://jlaine.net


_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

On Wed, 15 Dec 2004, Steve Kellock wrote:
> http://www.rubyonrails.org/show/HowtoUseMultipleDatabases
Another way to go that gives you a certain level of separation but with
less pain would be to put each customer in a separate schema. Then you
could just change the table prefix on a per-request basis, so that
"SELECT ... FROM invoice" turns into "SELECT ... FROM
cust_a.invoice" or
"SELECT ... FROM cust_b.invoice" as appropriate.

(Obligatory warning: as usual, probably doesn''t work with MySQL.)

cjs
-- 
Curt Sampson  <cjs-gHs2Wiolu3leoWH0uzbU5w@public.gmane.org>   +81 90 7737
2974   http://www.NetBSD.org
     Make up enjoying your city life...produced by BIC CAMERA

Joe Van Dyk wrote:
>Why do you think you need multiple databases?
>
I know this is somewhat late, but I have some experience with a massive 
multiple database system where each user''s information had the
potential
of residing on a separate database.  Our load was so intense that it was 
ridiculously expensive to "scale up" to a larger big-iron database 
server.  Instead we devised a "scale-out" load-balancing scheme that 
used a user''s username to determine which database their data was on
and
connected to that for each request.  user-db mappings were stored in 
memory in a dynamic trie structure that could be accessed with custom 
PHP functions.  Tables holding infrequently-updated common data were 
duplicated on each host and synchronized when necessary.  Transferring 
user data to a different server when load got too high had too be 
accomplished programmatically, since we had inter-server transactions in 
MySQL.  In short, it was complicated, but it did work and it saved us a 
lot of money.  This was for a web analytics product where our servers 
got a hit every time someone accessed our clients'' web sites, one of 
which was eBay, for example.  That may give you some notion of the 
magnitude of the problem.  So for some specialized cases, a multiple 
database strategy can be very useful.

Carl

Carl Youngblood wrote:
> Joe Van Dyk wrote:
>
>> Why do you think you need multiple databases?
>>
> Transferring user data to a different server when load got too high 
> had too be accomplished programmatically, since we had inter-server 
> transactions in MySQL.
Correction: we had _no_ inter-server transactions in MySQL.  In fact, at 
the time MySQL didn''t have transactions period, other than with table 
locking.

I apologise in advance for mentioning java stuff,  but It''s
interesting that you mention EBay.  Their architecture revolves around
multiple databases and a completely stateless system.

http://www.manageability.org/blog/stuff/about-ebays-architecture/view

"What enables our horizontal scalability is content based routing. So,
if imagine eBay has on any given day 60 million items. We do not want
to store that in one behemoth Sun machine. [snip] let us scale it
across; may be, many Sun machines, but how you get to the right one?
There is the content-based routing idea that comes in play. So, the
idea was that given some hint, find out which of my 20 physical
database hosts do I need to go to. The other cool thing about this is
that failover could be defined."


On Tue, 04 Jan 2005 16:14:53 -0800, Carl Youngblood
<carlwork-0CEYHQKyN7s@public.gmane.org> wrote:
> Joe Van Dyk wrote:
> 
> >Why do you think you need multiple databases?
> >
> I know this is somewhat late, but I have some experience with a massive
> multiple database system where each user''s information had the
potential
> of residing on a separate database.  Our load was so intense that it was
> ridiculously expensive to "scale up" to a larger big-iron
database
> server.  Instead we devised a "scale-out" load-balancing scheme
that
> used a user''s username to determine which database their data was
on and
> connected to that for each request.  user-db mappings were stored in
> memory in a dynamic trie structure that could be accessed with custom
> PHP functions.  Tables holding infrequently-updated common data were
> duplicated on each host and synchronized when necessary.  Transferring
> user data to a different server when load got too high had too be
> accomplished programmatically, since we had inter-server transactions in
> MySQL.  In short, it was complicated, but it did work and it saved us a
> lot of money.  This was for a web analytics product where our servers
> got a hit every time someone accessed our clients'' web sites, one
of
> which was eBay, for example.  That may give you some notion of the
> magnitude of the problem.  So for some specialized cases, a multiple
> database strategy can be very useful.
> 
> Carl
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> 

-- 
Cheers

Koz