Custom Schemas

  • JeffPrenevost

    SSC Journeyman

    Points: 77

    What a timely topic! Yes, in my work for the services arm of a software publisher, doing customer implementation work, we had the need for a better system to help with data validation for the results of ETL runs (mostly). In a simplified version, the system we designed uses a set of three schemas to supplement the main target schema. In these, we create sets of identically named objects, on the basis of which we can automate comparisons of actual vs. expected results, to give us fully automated regression testing of ETL results of each run at the detailed row-and-column level. It's a long story, but the availability of schemas as another layer in SQL Server makes this pretty straightforward. (Much harder in Oracle, where you don't have the schema layer per se.)

  • ronaldkunenborg

    SSC Enthusiast

    Points: 156

    In our architecture, we use instances to separate DEV/TEST/QA/PROD, databases to separate logical architecture layers, and schemas as source system namespaces in the staging layer, and as customer namespaces in the other layers. This enables us to maintain the names of source and customer no matter what they choose. If we need to integrate several sources for a customer, we create a new namespace and resolve the conflicts there.

    This also allows us to do our security on the schema-level, instead of table/view/proc level, which is much easier.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716284

    Comments posted to this topic are about the item Custom Schemas

  • Gary Varga

    SSC Guru

    Points: 82166

    Previously I have been on teams (so sometimes mine but sometimes colleagues' ideas and decisions) that have used schemas to separate out functionality based on modules (e.g. order processing vs. customer management), security requirements and anticipated clients (e.g. access by different systems such as a bespoke GUI as opposed to integration with a 3rd party system).

    I think that there is plenty of value of schemas and, in general, have been disappointed with the number of DBAs who have dismissed them out of hand WITHOUT providing a reason i.e. a development team suggests using schemas to the gatekeepers of the databases who disallow it whilst refusing to explain why. They may have good reasons and we would have loved to know. I know that this is not all DBAs. Possibly not most DBAs. Just most DBAs in my experience to date.

    (I need to work with you guys more ;-))

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • numisi

    Ten Centuries

    Points: 1242

    I use Schemas for tables in my current projects as a way of logic separation. Similar to the way .Net uses Namespaces. We have schemas for tables for Application Configuration, Business Configuration, Client Configuration, History Tables, and several Business Logic areas.

    Our applications have a standard version, but also specific objects by Customer for their specific features. Other very useful usage for schemas in this case is to group specific tables and programmability objects in Customer schema. We use .Net ADO connections and the database user the applications use to connect has the Customer specific schema as "Default Schema". This way, for example, if there is a specific stored procedure for this Customer, it will be called and not the standard stored procedure (dbo).

    For me schemas are a must.

  • ConnieOI

    SSC-Addicted

    Points: 431

    I haven't ever used schemas before. In retrospect, the company-specific objects in a 3rd party application would have been better placed in a separate schema. I inherited the current setup, and it's really to late to change that situation. But in future I'll definitely keep this in mind.

  • numisi

    Ten Centuries

    Points: 1242

    It isn't to late to change. 🙂

    For the SQL Server database with SSDT in Visual Studio it's even easier:

    - Create new Schema;

    - In the specific objects, Refactor -> Move to Schema;

    - Generate database upgrade script.

    If applications have code with the Schema defined, then it becomes more complicated to evolve.

  • joeroshan

    SSChampion

    Points: 10377

    Great topic to discuss,

    I have played all kinds of roles for SQL developer, DBA and data modeler and I have always found schema very useful for logically separating the objects for modelling and development and maintenance.

    I do not see any reason for a DBA to dislike schema because if cleverly designed it has additional benefits like managing permissions at schema level,

    script out/ migrate/ move across file groups etc all are easier if similar objects are grouped into logical schema.

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • david.dilworth

    Right there with Babe

    Points: 780

    We use schemas to help separate different logical areas within our database. These separations help to define the different business functions we're mirroring. We also find it helps with security and permissions, which we try to grant at the schema level. So certain users can only see certain tables - it all depends on their role within the business. It's not perfect, but it has helped guide our designs and thought process when we add new stuff into our databases.

    Here's some example schemas that we use:

    [audit]

    [clean]

    [logging]

    [pricing]

    [readonly]

    [reference]

    [service]

    Hopefully those examples should be self-explanatory. If not, then we've not picked good enough names!

  • Andy Leonard

    SSCrazy Eights

    Points: 9905

    I use a custom schema (named custom) when integrating SSIS Framework functionality and patterns into the SSISDB database (SSIS) Catalog. I get separation from the third party - Microsoft, in this case - and I can maintain referential integrity.

    :{>

    Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

  • Joel Ewald

    SSCertifiable

    Points: 6010

    We typically use schemas to separate application object / process areas

    APP

    CONFIG

    STAGE

    MDM

    DW

    REPORT (app specific)

    TEST

    We also use a CUSTOM schema for customer's to add their own objects, such as views so that they are isolated from our deployment process

  • Bill Talada

    SSChampion

    Points: 11956

    Schemas are simply containers like folders in the NTFS file system. I don't see any drawbacks but I see lots of organizational advantages to using them. Most OLTP apps will have hierarchical configurations so corporate determines billing settings, departments determine feature sets, and users determine look and feel. Having three schemas will make sense since security will be distinct for each level of configurations.

    Schemas also make sense when application areas are very modular. For example, having a schema for document work flow and storage would allow you to test, upgrade, and share that module with other projects more easily.

  • david.gugg

    SSCertifiable

    Points: 5687

    On the other hand, schemas can be very harmful if your DBA doesn't know what he/she is doing. I worked for a company where a previous DBA thought it'd be a good idea to provide each customer of our SaaS software with its own schema. Image how incredibly hard it is to work with 100 different versions of each table, each in its own schema.


    [font="Tahoma"]Personal blog relating fishing to database administration:[/font]

    [font="Comic Sans MS"]https://davegugg.wordpress.com[/url]/[/font]

  • barry.mcconnell

    Default port

    Points: 1447

    I have separate instances for Prod/Dev etc. due to differences in underlying hardware, installed tools, etc. I use databases and schemas in my instance to separate functionality and as security boundaries. For example, my data warehouse uses Data Vault methodology and I have separate databases for staging and data vault and then schemas within them for load staging, staging history, raw dv and business dv.

    I also have a separate schema for a data access buss for developers to pull data from the DW and/or Master Data. There are multiple schemas for user data access that contain views organized around topic areas (e.g. finance, hr, accounting). AD groups control access to the schemas and then I use database roles to further refine access to those views (e.g. views with PII or sensitive data).

    There are no facts, only interpretations.
    Friedrich Nietzsche

  • BrianAltmann

    Ten Centuries

    Points: 1327

    Great topic!

    In our Data warehouse projects we use schemas for two purposes:

    First (as already mentioned by others) to separate logical areas and at the same time simplify security administration (STAGE, DW, STAR, EXPORT)

    We also use schemas to separate standard version managed DB objects from temporary objects needed by developers.

    Having a small number of developers it is easy to give each their own schema for this purpose.

    If a new instance of a database has to be created it will only include the managed objects.

    Brian

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

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