sql server programming: [quoted text, click to view]

Multiple databases is insane. This is the extreme of attribute
splitting. Constructing a consistent, unified model of your
enterprise will be just about impossible. Sharing things like
inventory and discount schedules will be a screaming pain at best.

[quoted text, click to view]

I would go with one database for all customers. From administrating
point of view it seems much better. Any modification from metadata is
done only once instead of doing modification many times (once for each
database). You'll need to make sure that any customer will be able to
view\modify only his own records, but that can be done with the right
design. Connection pooling is on a server level and not on database
level, so this has nothing to do with the decision if you should have
one database or many databases.

Adi

I have worked on various systems where there are multiple customers and the
system is configured such that for each customer there is a separate
database. I have also worked on systems where there are multiple customers
and only one database.

My particular preference would be for a database per customer. However, I am
currently working on a system that has separate databases for each customer
but they wish to move to a single database for all customers in order to
simplify their application.

I'm not so sure about this. Apparently one of the problems is connection
pooling which is database specific, or so I'm told, though I have to admit
I'm not that familiar with pooling and don't know if that is a restriction,
in which I can understand that eventually a limit will be reached for the
number of connections. So does pooling actually have to be database specific
or can a connection be used for different databases, in which case it isn't
much of a restriction at all?

But in general I would be interested to know what other people think in
regard to the one database per customer approach versus the single database
for all (or multiple) customers.

TIA

Michael MacGregor
Database Architect

Michael
I would also prefer one database for all customers. I think the final
decision depends on their business requirements. Are all databases located
on the same server?
Do you need to create a cross database relationship? How many customers and
as result , how many databases you are going to have? What is about
maintaning as Adi already pointed?



[quoted text, click to view]

Hi Uri,

FYI
There are no cross database relationships.
There are a lot of customers already and more on the way - possibly the
biggest reason for moving to a one-for-all.
Maintenance may be an issue, I am not sure yet as I still have to do more
analysis on the schema.
Possible application issues with regard to how connection pooling has been
implemented, though this would seem to be not as important as it seems.
Single server instance on an active/passive cluster.

MTM

Ah, Mr. Celko, so kind of you to respond. Haven't had you respond to one of
my posts for a very long time, not that you probably remember me, I'm just a
lowly database architect up in Canada. Saw something about attending a
one-day seminar of yours recently which I would have to attend but
unfortunately can't. Any chance you can come to Ontario?

Is there absolutely no situation where you would have multiple databases? As
I mentioned I have worked on systems where this was the preferred model. In
fact my day-job employer likes to do it that way, but my other employer is
not so keen. For the day-job, there is no issue with stuff such as inventory
etc. as this simply doesn't apply. Reference tables, of which there are few
even though there are FK candidate data (schema is not in 3NF) do not
contain the same data from one customer to another, so for their system
having multiple databases works, and there are very good reasons to do so,
including customer requirements.

However, the other job I have, the schema and reference data is essentially
the same, at least from what I've seen so far, however, there is the
advantage of actual physical separation of data. On the other hand, they are
expanding rapidly and taking on new customers all the time, and having to
create new databases each time, with the associated pool of connections is
fast becoming an issue. So for them multiple databases does not appear to be
the way to go.

So, I'd just like to make sure that there is never, ever any situation under
which you would consider multiple databases, one per customer?

Michael MacGregor
Database Architect

Thank you Adi.

The connection pooling is from java, and to be honest I have very little
experience with it so that is the information I am interested in, but does
indeed affect the decision as it is a concern for my customer and part of
the reasoning behind wanting to move to a one-for-all so I need to know more
about it in order to understand how it affects what I recommend and why.

MTM

[quoted text, click to view]

That is SQL Saturday in Orlando. I have not been to Canada in a long
time -- loved Toronto, especially when the CD$1.00 was US$0.80 :).

[quoted text, click to view]

If each database is a separate universe unto itself. That is the idea
of a data model. And maybe for some really strong physical security
reason that cannot be done with encryption and access control.

[quoted text, click to view]

No 3NF? You are dead. Can explain why every customer is logically
different from every other customer? No common attributes, like you
would expect? I cannot believe that.

[quoted text, click to view]

This is a really bad way to fake parallelism. Can I assume that each
customer gets his own piece of hardware, too? With modern hardware
you can get fast response; the real issue is data integrity via a
normalized DB schema.

[quoted text, click to view]

I cannot imagine such a thing. Expensive, no data integrity, a
screaming bitch to maintain, etc.

So back to the main thread, what do you feel about multiple vs single
database?

Michael MacGregor
Database Architect

[quoted text, click to view]

And why won't a cross database view not help?

Yet another example of where you haven't a clue what you are talking about
and quite clearly need to get out and get some real experience rather than
from a book.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]


[quoted text, click to view]