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 |
|
Lavos
Posting Yak Master
200 Posts |
 Posted - 2003-03-26 : 17:25:59
|
| Hello, we're currently planning a rather largish application suite that will likely share a large database among the programs with local disconnected databases on the clients so that a station can work disconnected from the central server if needed. Of course, there are going to be at least a couple parts of the suite that won't need this disconnected functionality, and are likely going to be encapsulated in a web application instead of a fat client like the other products. I was playing with multiple database schema's in my head about how to divide everything up. How the disconnected data works isn't in my jurisdiction, but I was interested in maintaining logical blocks of the database in seperate sections since I'll likely have a hand in the overall database design. I haven't really read much about dividing up a large database into different segments that are interrelated. For instance, I couldn't say whether a foreign key could exist across databases or not. I was curious about the experience of others in this area. Whether it was worth the hassle of dividing things up into different "Schemas" within a database, or among different databases.----------------------"O Theos mou! Echo ten labrida en te mou kephale!"So Long and Thanks for the Links. |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2003-03-26 : 22:18:51
|
| BOL doesn't mention it explicitly, but it seems like foreign keys cannot span databases. I am deriving this info. from the fact that foreign keys are stored in the sysforeignkeys table that exists in every database, and it stores in the RKeyId column the object id of the table the key references. Object id's are not unique across databases. To duplicate the functionality of a foreign key (in a sense), you could use triggers, but they are slower, more prone to problems, and other than constraints, fire after the insert/update/delete rather than before, so you must issue a rollback tran.Sarah Berger MCSD |
 |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-03-26 : 22:30:00
|
| Sarah is right, you can not have a cross database foriegn key. Triggers would work for you though.I would tend towards one database for one application. Having to enfore referential integrity means to me that the tables are closely related enough to warrant being together.It will also make maintainence easier, as well as not having to store multiple sets of connection information thoughout the application.Damian |
|
|
|
|
|
|
|
|
Dividing things between databases/schemas?