The Lounge is rated PG. If you're about to post something you wouldn't want your
kid sister to read then don't post it. No flame wars, no abusive conduct, no programming
questions and please don't post ads.
I personally prefer multiple schemata over multiple DBs.
You mean you want a logical partitioning of schemata (you and I are probably the only two people that know that that is the correct plural for schema, I bet ) but physically in one DB?
That makes sense, which is what I added to the Interacx schema designer. However, I also thought that starting with SQL 2005, there was support for that, but I can't remember how it's done, because I never used it! But I remember reading something about it.
That's definitely a useful feature, and I'm glad you asked, because I realize this would be useful for maintaining some logical information when mapping an XSD to a DB--think of using the XSD namespace as the DB schema name.
But you are bit more enamored than the average bear with XML et al aren't you Marc?
And the funny thing is, years ago when I first learned about XML, I thought "geez, what idiot would use this verbose format for data?"
But so is Microsoft, finally. Still, everything/everyone seems to be using XML, for better or worse.
Funny thing is, a client wants me to write an interface to a web service for traffic accident reports. The data payload is sent as a text file, yet the response is in XML! Totally weird, except that the accident report has hundreds of fields, and I guess the guys writing the web service were too lazy to come up with an XSD for it.
And I also figure it probably feeds into an ancient COBOL app, as the text file is highly structured, specifying the exact length of each field.
I use it to map permissions. Users are in groups, so, barring exceptions of course, users are allowed specific actions on a schema. Tres cool, non?
That's exactly how we're using them here... logical groupings of objects within a database for permissions and for ease of finding things during maintenance. It replaces an earlier convention that our developers used to prefix object names with the logical grouping. It's definitely making my job a bit easier on the databases where we've used it.
Oh, yeah. Permissions were a pain on SQL Server before they introduced schemas, especially for stored procedures. Now, using a combination of schemas and roles, I can zip through all but the most granular permissions requests.
If multiple database are related , or for same application, then its better idea to use multiple schema for better grouping/seperation, otherwise multiple database are fine. like Microsoft Adventure Works Sample Database has done.