Ahmad,
Thank you very much for your reply.
You brought up some very interesting points and I appreciate your
thoughts/ideas.
I will re-think the design and give your thoughts major consideration.
Best regards,
M
-----Original Message-----
From: Ahmad Maredia
[mailto:mssqldba-ezmlmshield-x83560704.[Email address protected]
Sent: Tuesday, August 30, 2005 8:33 AM
To: LazyDBA Discussion
Subject: RE: [BULK] Database Development
Myra,
You have multiple options, actually 3 that I can see.
The first as you have pointed out is that you can have 300 databases
each having 6 tables. The downside to this would be the database
maintenance overhead that would come with managing 300 databases. The
positives to this approach would be that each database would be
relatively smaller and easier to manage. Also retrieving and saving of
data would take a lot less time since each table in each database will
have relatively lesser number of records. Loss of one database would
mean that only one customers data is lost and the data of the other 299
is safe.
The second option as you have pointed out is to have 6 databases with
300 tables. This does not seem to a feasible option as each of the
table would have to be named with a prefix or a suffix and it might
become difficult to program for this kind of a database design. For
example there is a table called 'Users'. In this kind of a design, you
would have tables called 'Users_1', 'Users_2', 'Users_3', 'Users_4'....
and so on. Also a retrieval that requires a join between two tables
would have to join over across two different databases since the other
table is in another database.
According to me this would be the worst design to follow.
Another option that you have not identified is that you could have only
1 database with only 7 tables. The seventh table would store details of
each of your 300 customers. The other 6 tables would have an additional
column added to them called CUST_NO or something like that based on your
naming conventions. This CUST_NO column would hold the value of the
primary key value of each customer and identify to which customer each
row of data belongs to. The upside to this approach is that it would
save you a lot of database maintenance. However the downside to this
approach is performance. Due to the relatively large amount of data in
each table (all the customers' data is in one table) data retrieval and
saving/updating might take relatively longer. However adding a few
indexes might take care of that. Also loss of a database means all your
customers' data is lost and that means 300 unhappy customers to deal
with. However that can be taken care of by putting proper backup and
disaster management procedures in place. I know nothing is fool proof
but this is risk vs. reward.
Regards,
Ahmad
-----Original Message-----
From: Eduardo Olivera
[mailto:mssqldba-ezmlmshield-x76457567.[Email address protected]
Sent: Dienstag, 30. August 2005 17:35
To: LazyDBA Discussion
Subject: RE: [BULK] Database Development
Are the tables the same across databases?
Ed
-----Original Message-----
From: Myra
[mailto:mssqldba-ezmlmshield-x92389156.[Email address protected]
Sent: Tuesday, August 30, 2005 7:59 AM
To: LazyDBA Discussion
Subject: [BULK] Database Development
Importance: Low
A new SQL project was just "handed-down" to me.
Right now we have 1 database for each customer. Each database has 6
tables that have the same structure as every other database. Before the
project ends, there will be around 300 databases (1 for each customer).
Since we are still in the early stages of developement and not beyond
the "point of no return", I am re-evaluating the design. Would it be
better to have 6 databases with 300 tables in each database (1 table for
each of the 300 customers in 6 different databases) or keep the original
design of 300 databases with 6 tables in each database?
We deal from 100,000 to 25 million records for 1 customer. I have
thought of the maintenance of 300 databases, compared to the fact that
if one database had 300 tables and that database became corrupt and we
couldn't restore the backup then we have lost major information for 300
mad customers. Are is there a better/different design?
Thank you for your time.
M
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page