CODEWITHSUNDEEP
sql-serversql-server-2008database-schema
Vaccano
Vaccano

Reputation: 82437

Way to control the defintion of a table duplicated in several schemas

We are architecting a new database that will make heavy use of schemas to separate logical parts of our database.

And example could be employee and client. We will have a schema for each and the web services that connect to one will not be allowed in the other.

Where we are hitting problems/concerns is where the data appears very similar between the two schemas. For example both employees and clients have addresses.

We could do something like common.Address. But the mandate to keep the services data access separate is fairly strong.

So it is looking like we will go with employee.Address and client.Address.

However, it would be nice if there was a way to enforce a global Address table definition. Something to prevent the definition of these two Address tables from drifting apart during development. (Note: there will actually be more than two.)

Is there anything like that in SQL Server. Some kind of Table "type" or "class" that can be "instantiated" into different schemas. (I am not hopeful here, but I thought I would ask.)

Upvotes: 0

Views: 62

Answers (2)

KM.
KM.

Reputation: 103637

I think your best bet is a DDL trigger, where you can cause a failure when altering any of your "common" tables.

something like:

CREATE TRIGGER [Dont_Change_CommonTables]
ON DATABASE
FOR DDL_TABLE_EVENTS,GRANT_DATABASE
AS
DECLARE @EventData      xml
DECLARE @Message        varchar(1000)
SET @EventData=EVENTDATA()

IF (@EventData.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(50)')='TABLE'
    AND @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(50)') IN ('Address'
                                                                              ,'etc...'
                                                                              --place your table list here
                                                                              )
   )
BEGIN
    ROLLBACK
    SET @Message='Error! you can not make changes to '+ISNULL(LOWER(@EventData.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(50)')),'')+': '+ISNULL(@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(50)'),'')
    RAISERROR(@Message,16,1)
    RETURN
END
GO

Upvotes: 1

gbn
gbn

Reputation: 432431

Thoughts, rather then a hard answer...

We have

  • a common Data schema
  • Views, Procs etc in a schema per client
  • An internal "Helper" schema for shared code

Would this work for you?

My other thought is a database per client. It easier to permission per database, than per schema, especially for direct DB access, support or power user types

Upvotes: 2

Related Questions