Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Chris McB
Starting Member
5 Posts |
 Posted - 2002-11-19 : 09:30:39
|
| My background is in Access databases and VBA. Now we are moving to SQL. In Access, I have set up “master” databases, like Employees and Customers that are used by more than one Access application and have them in their own db’s. Any other database that needs employee information just links to it.In SQL, I don’t see an easy way to do that. In the Server Manager if you create a new view, you can’t even bring in an outside table like you would in building an Access query. Is the philosophy behind SQL different as in build one big database and put all your tables inside of it? Thanks |
|
|
colinm
Yak Posting Veteran
62 Posts |
Posted - 2002-11-19 : 09:36:46
|
You can use three part naming to access other databases.i.e.use northwindselect LastName, FirstName from employeesunionselect Lname, fname from pubs.dbo.employee |
 |
|
|
burbakei
Yak Posting Veteran
80 Posts |
Posted - 2002-11-19 : 09:40:24
|
| matters in SQL Server are different from Access. but you do not need to build a big database and put all your tables in it. if you want to see the data which is within Employees from Customers database you must qualify your table name like this:SELECT *FROM Employees.TableOwner.AnyTableif the owner of table is DBO you can omit table's owner like thisSELECT *FROM Employees..AnyTable |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-11-19 : 10:01:25
|
| What are the reasons for splitting tables into multiple databases?I can think of sever against that idea, but I continue to see organization after organization with 15+ production databases ...Jay White{0} |
|
|
|
colinm
Yak Posting Veteran
62 Posts |
Posted - 2002-11-19 : 10:15:13
|
| Don't mind if I hijack this thread :)I will have a situation where users will have access through a website to one or more databases to run web reports.I am trying to weigh up the pro's and con's of having all the information in one big database vs. separate. I will have application level defined security to grant access to their relevant datasets.Any ideas/experiences appreciated. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-19 : 10:36:35
|
| Here's some threads that covered pros and cons of consolidating and splitting DB's:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20623http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19108And I remember another one that had a really good number of responses, 20 or so, that covered a lot more, but I'm damned if I can find it now. If you want, try doing a forum search for "one database or multiple databases", and variations of that. ONE of them will be the thread I'm thinking of.If I find it in the meantime I'll update this reply with the link.<edit> Found it, actually, there were TWO of them:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11704http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=17008</edit>And as a general guideline, if tables are in ANY WAY related to each other, you should lean towards keeping them in one database only. There's no convenience in keeping them in separate databases, and only the most overwhelming security or performance demands should be considered as valid reasons for separating them.Edited by - robvolk on 11/19/2002 10:48:24 |
|
|
|
colinm
Yak Posting Veteran
62 Posts |
Posted - 2002-11-19 : 11:12:27
|
| Cheers,I'll have a look at these.I think I'll go down the single database route by the looks of things as I need a master security table to hold all user information. This user information is related to the data, so I will need RI. |
|
|
|
|
|
|
|
|
SQL Database Design