Do you use custom schemas?

Question

Do you use custom schemas?

bkubicek

SSChampion

Points: 10948
More actions
November 14, 2018 at 9:20 pm

#409311

Comments posted to this topic are about the item Do you use custom schemas?

Viewing 15 posts - 1 through 15 (of 39 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 1

Heh... I find custom schemas to be incredibly annoying but not because anything you said about them isn't true. They ARE incredibly useful for all the reasons you stated.

The reason why I find them incredibly annoying is because, just as with other object names, many people don't actually have a plan and so you end up with some pretty stupid usage of custom schemas. And, no, you don't need a thousand different schemas... that's what different object names are for.

I will say that I have a very strong dislike for schema names that are more than 4 characters or so. There are useful exceptions but they shouldn't be used like table names (for example), which some folks seem to do/prefer.

Of course, that's just my opinion and preferences. If I'm doing work in someone else's shop and regardless of what my opinion and preferences are, I'll do it their way.

The bottom line is just like anything else... you can destroy an excellent tool by the way you name things. Have a plan and a standard.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DinoRS SSCrazy Points: 2683 More actions · Answer 2

I like to use Custom Schemas for segregation of applications or application functions. One thing I like is using an [Error] Schema in DWH Scenarios, there I have things of interest like certain tables might need correction when lookups fail, you want to know which rows failed so you have a [Error]LKP_TblName table in there, of course the naming schema might look different if the DWH serves multiple smaller applications.

It makes Filegroup Partitioning much easier, too if you have sensible ETL Schemas for each step of the process which helps you avoid file contention when data from one stage is being loaded or processed into the next stage.
Some might argue I look way too much at details but I like details like these, they make my designs quite fly with the right hardware beneath.

John Corkett SSCrazy Points: 2273 More actions · Answer 3

Completely agree with everything you say here.
I have lots of pet hates and one of them is the use of dbo schema for everything. One culprit I have encountered is the use of ORM systems by developers. Whilst the ones I have encountered do permit the use of schemas most developers seem to take the easy way out and just let the ORM take care of creating everything in dbo.

John Corkett

roger.plowman SSChampion Points: 10265 More actions · Answer 4

I don't use schemas for a few reasons. First, why would you ever put multiple application data in the same database? Brr. Besides, that's what four part names are for if you ever need them. 😀

Second, there are tables that should logically span multiple schemas, so which schema should they go into? If you're using schemas for security reasons (which is really the purpose of security groups so why be redundant?) then you'd have to grant access to an entire second schema to get to the one table needed by members of another schema--which defeats the purpose of schema security!

Third, there's no reason you can't use SPs exclusively with the dbo schema, again, that's what security groups are for.

I'm a BIG proponent of vanilla design--use the bare minimum number of features you can get away with, especially avoiding redundant features. The more features you involve, the more things that can go wrong.

Sure, if you MUST HAVE some feature, by all means use it. But if it's redundant then don't.

KISS for the win!

druid84 Old Hand Points: 311 More actions · Answer 5

I am new in the world of DBAs. Is there any documentation/books you would suggest I read to get more accustomed with custom schemas?
Thanks!

DinoRS SSCrazy Points: 2683 More actions · Answer 6

Well one reason for having multiple Applications within one DB could be sharing the same base data for different Applications.

Of course you could import the data 20 times to 20 different DBs which is most likely according to your statement what you would do and I admire anyone being allowed to waste resources woefully. If you have Sales data and different Sales departments which each use a subset of the available data, go ahead with your approach and let us know what your customer thinks about your design strategies. I guess your most basic design aswell includes one DB File that can grow to 16 TB before you realize you've reached a limit, alternatively you have never been close something like that, take your pick of "most basic vanilla design" sincerely.

bkubicek SSChampion Points: 10948 More actions · Answer 7

DinoRS - Thursday, November 15, 2018 1:00 AM
I like to use Custom Schemas for segregation of applications or application functions. One thing I like is using an [Error] Schema in DWH Scenarios, there I have things of interest like certain tables might need correction when lookups fail, you want to know which rows failed so you have a [Error]LKP_TblName table in there, of course the naming schema might look different if the DWH serves multiple smaller applications.
It makes Filegroup Partitioning much easier, too if you have sensible ETL Schemas for each step of the process which helps you avoid file contention when data from one stage is being loaded or processed into the next stage.
Some might argue I look way too much at details but I like details like these, they make my designs quite fly with the right hardware beneath.

I hadn't thought of using an Error schema before. I will keep that in mind for future projects. Thanks for sharing.

Ben

Y.B. SSChampion Points: 11649 More actions · Answer 8

I don't use schemas everywhere for the sake of just using them but I DO use them and they are fantastic! I have a datawarehouse that contains data from several different locations. Who can have access to all this data can be very messy. This is where schemas come in, I create few logical schemas with their own database role that has appropriate permissions to that schema (the schemas in this scenario only contain views and procs). I then add AD groups to the correct role and done. Setup is probably the worst part but after that managment is so easy.

EDIT: Another time I use schemas is during data load for table switching. It's easy to keep the names of the table the same. I use load schema that is swaped in to the final table when the load and other processing is complete.

SELECT quote FROM brain WHERE original = 1
0 rows returned

roger.plowman SSChampion Points: 10265 More actions · Answer 9

DinoRS - Thursday, November 15, 2018 6:25 AM
Well one reason for having multiple Applications within one DB could be sharing the same base data for different Applications.
Of course you could import the data 20 times to 20 different DBs which is most likely according to your statement what you would do and I admire anyone being allowed to waste resources woefully. If you have Sales data and different Sales departments which each use a subset of the available data, go ahead with your approach and let us know what your customer thinks about your design strategies. I guess your most basic design aswell includes one DB File that can grow to 16 TB before you realize you've reached a limit, alternatively you have never been close something like that, take your pick of "most basic vanilla design" sincerely.

Um, no?

In that scenario you'd probably separate the sales departments with a simple field in the relevant table (invoices, maybe?), then in the SP either supply the department you wanted or (more likely) allow the appropriate users to see all departments.

Applications are front-ends, independent of databases. If they use the same data they use the same database. Or am I misinterpreting your point?

Note I did say use the minimum needed features. Simple does not mean brainless. Elegant design is both simple and efficient at the same time.

Dalkeith Hall of Fame Points: 3688 More actions · Answer 10

I prefer to have separate applications with separate databases but I could see why you might be forced to having multiple applications in a single database. That assumes you know what the boundary of your application actually is. I certainly see a lot of databases recreating things like address lists.

cloudydatablog.net

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 11

Use them all the time now. With SQL I design primarily OLAP databases. For the designs over which I have complete control, the schemas for the tables identify the source database. The schemas for the views identify the supporting cubes. For much other work I do the schemas for the tables identify the cube or cube process to which the table belongs. Very useful for preserving a table name that has the same structure and purpose but supports a different process. There are few if any dbo objects in these databases.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 12

roger.plowman - Thursday, November 15, 2018 6:50 AM
DinoRS - Thursday, November 15, 2018 6:25 AM
Well one reason for having multiple Applications within one DB could be sharing the same base data for different Applications.
Of course you could import the data 20 times to 20 different DBs which is most likely according to your statement what you would do and I admire anyone being allowed to waste resources woefully. If you have Sales data and different Sales departments which each use a subset of the available data, go ahead with your approach and let us know what your customer thinks about your design strategies. I guess your most basic design aswell includes one DB File that can grow to 16 TB before you realize you've reached a limit, alternatively you have never been close something like that, take your pick of "most basic vanilla design" sincerely.
Um, no?
In that scenario you'd probably separate the sales departments with a simple field in the relevant table (invoices, maybe?), then in the SP either supply the department you wanted or (more likely) allow the appropriate users to see all departments.
Applications are front-ends, independent of databases. If they use the same data they use the same database. Or am I misinterpreting your point?
Note I did say use the minimum needed features. Simple does not mean brainless. Elegant design is both simple and efficient at the same time.

I believe Dino had a little DBA humor/sarcasm going on there and isn't actually recommending 20 databases. It's a bit of a "yeah... try that and lemme know how it works for you" lesson/example.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kiwood Ten Centuries Points: 1074 More actions · Answer 13

John Corkett - Thursday, November 15, 2018 1:04 AM
Completely agree with everything you say here.
I have lots of pet hates and one of them is the use of dbo schema for everything. One culprit I have encountered is the use of ORM systems by developers. Whilst the ones I have encountered do permit the use of schemas most developers seem to take the easy way out and just let the ORM take care of creating everything in dbo.
John Corkett

I am primarily a developer and find this highly annoying myself. My view of things is that it would seem incredibly useful to use schemas which contain uneatable views for the ORM. First, the typical (lazy) way ends up with huge data context that touches everything at once. Then as developers continue down the path they try ever more convoluted means of dealing with this and pull vast amounts of data to update a few columns of data. It simply isn't hard to write a view which can then update data. And it seems logical to divide a system of appreciable size into smaller chunks.

My one complaint is "PriceListObj" leaves me hoping that Obj is a placeholder for something more meaningful. Object should simply be banned from names because everything is an object.And much of the time there is no real reason to specify the object's type.

roger.plowman SSChampion Points: 10265 More actions · Answer 14

Jeff Moden - Thursday, November 15, 2018 7:38 AM
roger.plowman - Thursday, November 15, 2018 6:50 AM
DinoRS - Thursday, November 15, 2018 6:25 AM
Well one reason for having multiple Applications within one DB could be sharing the same base data for different Applications.
Of course you could import the data 20 times to 20 different DBs which is most likely according to your statement what you would do and I admire anyone being allowed to waste resources woefully. If you have Sales data and different Sales departments which each use a subset of the available data, go ahead with your approach and let us know what your customer thinks about your design strategies. I guess your most basic design aswell includes one DB File that can grow to 16 TB before you realize you've reached a limit, alternatively you have never been close something like that, take your pick of "most basic vanilla design" sincerely.
Um, no?
In that scenario you'd probably separate the sales departments with a simple field in the relevant table (invoices, maybe?), then in the SP either supply the department you wanted or (more likely) allow the appropriate users to see all departments.
Applications are front-ends, independent of databases. If they use the same data they use the same database. Or am I misinterpreting your point?
Note I did say use the minimum needed features. Simple does not mean brainless. Elegant design is both simple and efficient at the same time.
I believe Dino had a little DBA humor/sarcasm going on there and isn't actually recommending 20 databases. It's a bit of a "yeah... try that and lemme know how it works for you" lesson/example.

Ah, caffeine hadn't kicked in yet.