One database or Many database?

Hello All,
I am trying to design a database just like "NorthWind", but the difference
is this database will handle a lot of companies. The first thing I cannot
decide is, use one database to control all companies or each company has one
database.
The relative questions are:
1. Which one has better performance;
2. How many databases SQL server can have;
Any help will be appreciated.
MikeThe answer to 1 is, it depends on the application. The answer to 2 is,
as many as you have room for. I'd recommend getting a good book on
relational database design, like Mike Hernandez' "Database Design for
Mere Mortals", Microsoft Press. There aren't any cut-and-dried answers
to these kind of questions without understanding your specs.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Mon, 15 Dec 2003 15:51:54 -0500, "Mike Lincoln"
<mlincoln001@.hotmail.com> wrote:
>Hello All,
>I am trying to design a database just like "NorthWind", but the difference
>is this database will handle a lot of companies. The first thing I cannot
>decide is, use one database to control all companies or each company has one
>database.
>The relative questions are:
>1. Which one has better performance;
>2. How many databases SQL server can have;
>Any help will be appreciated.
>Mike
>|||> 1. Which one has better performance;
Like just about any other computing task with multiple paths, it depends.
> 2. How many databases SQL server can have;
32,767
http://www.aspfaq.com/2345
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Thank you, Guys. It helped a lot. I will think about this and post a
detailed project description later. Have a nice day and merry Cristmas.
Mike
"Mike Lincoln" <mlincoln001@.hotmail.com> wrote in message
news:uWA0b30wDHA.2396@.TK2MSFTNGP09.phx.gbl...
> Hello All,
> I am trying to design a database just like "NorthWind", but the difference
> is this database will handle a lot of companies. The first thing I cannot
> decide is, use one database to control all companies or each company has
one
> database.
> The relative questions are:
> 1. Which one has better performance;
> 2. How many databases SQL server can have;
> Any help will be appreciated.
> Mike
>|||One of the things no one mentioned was security... If you put all companies
information into a single database you must rely on views and Sps
(probably ) for security... It is more likely that one company might be
able to see another company's data...
When each company's information is stored in a different database, standard
sql security can assist to ensure data is secure...
Also consider backup/restore needs. With all data in a single database, if
one company needs to restore, then all companies will be restored...
--
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"Mike Lincoln" <mlincoln001@.hotmail.com> wrote in message
news:uWA0b30wDHA.2396@.TK2MSFTNGP09.phx.gbl...
> Hello All,
> I am trying to design a database just like "NorthWind", but the difference
> is this database will handle a lot of companies. The first thing I cannot
> decide is, use one database to control all companies or each company has
one
> database.
> The relative questions are:
> 1. Which one has better performance;
> 2. How many databases SQL server can have;
> Any help will be appreciated.
> Mike
>sql

on similar tables

hi all,
so i have these tables:
Company
Job
Resume
user searches for a company, views that company. there is a list of
"viewed" companies for that user. Users can attach/delete a (private to
themselves) note about that company.
same for jobs and resumes.
What I want to know is, should i have a CompanyNote, JobNote,
ResumeNote table, or have one Note table?
with the first option, can have a foreign key to the corresponding
Company/Job/Resume table, with the second option cannot.
TIA
Neili suppose i should have one note table:
NOTE
noteid
text
a Company table:
COMPANY
companyid
a User table:
USER
userid
and a COMPANY_USER_NOTE table
COMPANY_USER_NOTE
companyid
userid
noteid
and a JOB_USER_NOTE table:
JOB_USER_NOTE
jobid
userid
noteid
then foreign keys not a problem|||(neilmcguigan@.gmail.com) writes:
> i suppose i should have one note table:
> NOTE
> noteid
> text
> a Company table:
> COMPANY
> companyid
> a User table:
> USER
> userid
> and a COMPANY_USER_NOTE table
> COMPANY_USER_NOTE
> companyid
> userid
> noteid
> and a JOB_USER_NOTE table:
> JOB_USER_NOTE
> jobid
> userid
> noteid
> then foreign keys not a problem
But now you can have multiple notes for the same user to the same item.
And the same note can be connected to both job and company.
But maybe that's a feature?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||would the alternative would be...
table JobNotes:
UserID
JobID
NoteText
and table CompanyNotes:
UserID
CompanyID
NoteText
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97BB794BC5320Yazorman@.127.0.0.1...
> (neilmcguigan@.gmail.com) writes:
> But now you can have multiple notes for the same user to the same item.
> And the same note can be connected to both job and company.
> But maybe that's a feature?
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx