March 10, 2009 at 1:39 pm
Ok, guys: here is the issue; we are using SQL 2008
We have a table named SYS_BusinessObjects wich contains some information that is common to all tables in db and a table named SYS_Person
Sys_BusinessObjects
Oid uniqueidentifier PK default newid()
CreatedOn DateTime default getdate()
LastUpdatedOn DateTime default getdate()
SYS_Person
Oid unqueidentifier PK default newid()
Name varchar(50)
We want to create an updatable view named Person which contains all the SYS_Persons with the related CreatedOn info and we would like to be able to insert data through it without having to specify the Oid (let the default newid() from SYS_Person make the job).
Of course we would like to avoid using instead of, since this would mean that if SYS_Person har 200 cols we have to write all the 200 col names in the insetad of; in a similar way, if we add a field to Sys_BusinessObjecs we would have to update all the instead of definitions for all the tables in the Db.
Any smart idea ?
March 10, 2009 at 1:58 pm
Why do you want to update through the view instead of updating the base tables?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 10, 2009 at 2:26 pm
So long as your updates only hit one of them at a time, you'll be okay.
I don't think inserts will work. SQL can't know if there's theres a sequence to the inserts. For example, to add a person, it looks to me like you insert them into Sys_Person, and then insert the resulting ID into Sys_BusinessObjects. You'll have to tell SQL Server to do it in that sequence. Or are you generating the IDs outside the database? If so, you shouldn't have a default on the tables.
The design looks more like something that a serious OOP adict would build than something that's designed to capitalize on RDBMS features. It looks like an attempt at inheritance and all that. Is that the design plan? If so, you'll be better off with a database built to support that, like Cache or some such.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 10, 2009 at 4:26 pm
I think it's going to be much easier to drop the default for the Oid field in both tables and set it externally. Then the inserts to the two tables would look something like:
[font="Courier New"]declare @UID uniqueidentifier
begin tran
set @UID = NEWID()
insert into Sys_Person values (@UID, 'Name goes here')
insert into Sys_BusinessObjects (Oid) values (@UID)
commit tran[/font]
March 10, 2009 at 4:33 pm
[font="Verdana"]You could possibly make this work, even with updates or inserts to both tables, by using an instead of view. I haven't tried this myself though.
As an alternative, have you considered using gateways stored procedures (CRUD, I know...)? You could even pass in sets via XML to the procedures, so it need not be an RBAR design.
[/font]
March 10, 2009 at 9:15 pm
Giovanni (3/10/2009)
Ok, guys: here is the issue; we are using SQL 2008We have a table named SYS_BusinessObjects wich contains some information that is common to all tables in db and a table named SYS_Person
Sys_BusinessObjects
Oid uniqueidentifier PK default newid()
CreatedOn DateTime default getdate()
LastUpdatedOn DateTime default getdate()
SYS_Person
Oid unqueidentifier PK default newid()
Name varchar(50)
We want to create an updatable view named Person which contains all the SYS_Persons with the related CreatedOn info and we would like to be able to insert data through it without having to specify the Oid (let the default newid() from SYS_Person make the job).
Of course we would like to avoid using instead of, since this would mean that if SYS_Person har 200 cols we have to write all the 200 col names in the insetad of; in a similar way, if we add a field to Sys_BusinessObjecs we would have to update all the instead of definitions for all the tables in the Db.
Any smart idea ?
I'm pretty sure this is easy with an after trigger or two but, tell me, is there a column in the Sys_BusinessObjects table that identifies the object type? If so, please post what the object type entry should be for a person.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 11, 2009 at 1:35 am
Thank you all for your considerations.
I confirm the design is related to OO structure that we have to write in c#: mapping a class to the view instead of having a business logic to update the two classes simplyfies the thing a lot and keeps things neat and polish.
Nevertheless, even if we eliminate OO design. I think that from a database design point of view (an maintenance as well). if we have some information such as CreatedOn, LastUpdatedOn that is common to all tables in the database, it is worth placing them just in one place and not replicating them across all tables.
If the database is made up of 200 business tables that share the some common attributes (LastUpdatedOn, ..), putting these columns in each table requires to modify 200 tables (and keep them in sync) whenever a definition change or new shared attribute comes in..... this takes a lot of time and is prone to errors.
Besides maintenance, if you want the find all items modified yesterday to do change tracking you would have to query 200 tables instead of 1 indexed one.
I agree with the fact that that table can become really huge (we have one with 12 million records ...) but there can be optimizations on that and you will never go for a select * on that table....
It would be possibile to add an object/table type column as well drop auto newguid() on primary keys of the single tables (SYS_Person,...).
The issue here is if it youd be possibile to avoid using "instead of" or finding a smart (and performing) way to do it.
I make an example.
Should we go for "instead of", we should write something like this:
declare id uniqueidentifier
select id = newid()
insert into SYS_PERSONS (Oid,Col_1,...,....,....,Col_200) values (id, ..., col 200)
insert into SYS_BusinessObjects (Oid, ..., ...) values (id, .., ...)
Of course everything should go in a transaction,.....
Now as you understand, if each table has 200 columns and we have 500 tables it takes ages to write all the column names,....
Now we have the following choiches:
- Write a program that reads the schema and generates all the views for us: this guarantees speed and consinstency
- Write a StoredProcedure ivoced from the "instead of" that does the job at runtime (it can be t-sql or .NET procedure): this guarantees consistency, but has a performance overhead like al code generated on the fly (and the cost is for every insert, update, delete statement)... the procedure could use sql 2008 tabular procedure parameter and take in a full set of data
I was wondering if there is something smart: low/no performace overhad, consintency and manageability... I know I am looking for the moon....
March 11, 2009 at 6:27 am
Giovanni (3/11/2009)
I confirm the design is related to OO structure that we have to write in c#: mapping a class to the view instead of having a business logic to update the two classes simplyfies the thing a lot and keeps things neat and polish.
If I wrote a front end app with all my classes designed as per relational theory, people would call me stupid. Why is doing the opposite any better?
I think that from a database design point of view (an maintenance as well). if we have some information such as CreatedOn, LastUpdatedOn that is common to all tables in the database, it is worth placing them just in one place and not replicating them across all tables.
Absolutely not. The CreatedOn is an attribute of Person (for eg) and should be in the person table.
Besides maintenance, if you want the find all items modified yesterday to do change tracking you would have to query 200 tables instead of 1 indexed one.
So instead query 1 table, find out that 753 rows have changed and then have to go and look in each table to find the details of the row. How is that better than querying the tables directly? How would you do referential integrity to this 'last changed' table, seeing as it can refer to any one of the other tables in the database?
I agree with the fact that that table can become really huge (we have one with 12 million records ...) but there can be optimizations on that and you will never go for a select * on that table....
Small or large, SQL is a relational database not an object database. Therefore it works best with a relational data structure.
Now as you understand, if each table has 200 columns and we have 500 tables it takes ages to write all the column names,....
Management Studio - Object Explorer - Right click the table - Script Table As - Insert
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 11, 2009 at 7:22 am
Thank you Gail for your considerations.
Nevertheless, your approach does not help that much.
You say: add the attributes common to all tables to each table; then use "Script table as" (what's the point, I do not need a view at that point).
This is a "standard" approach and I agree with you I works fine and it is the best: the price I have to pay for this it to update 200 or n-hundres tables every time ad additional common attribute is going to be added/removed/changed: this means time and, at then end of the story, money and easiness to manage things and check quality.
If the the db designer forgets to add a new attribure in one of the 200 tables and the application gets modified in a base class that reads that attribute, you have a bug, a hidden bug.
I think that if we follow the purist approach that database must just be a database without taking in consideration what application will do with it and vice versa, we end up having a perfect database design and an unmanageable application (or vice versa).
Compromise, if reasonable, can get out a good solution (not perfect and pure, but good) at a low performance/design cost: everything is in evaluating the compromise and its "cost": but this is physolophy and everyone has its point of view (same is with UDTs, UDFs, ....). I just think you need to know how it works, the glitches and limitations and then decide based on the cost/benefits ratio...
Coming back to the real subject of my post, it seems to me that if I will be willing to go with the view approach the only suitable ways are the "instead of" (either generated or via stored procedure); if I go with your approach (add the colums to each single table), i could go for a storad procedure reading the schema and creating/updating/deleting the shared columns on all the tables.
Thank you for the suggestions
March 11, 2009 at 7:25 am
If the primary objection to doing this in triggers is "writing out all of the columns", here's a little trick:
In Management Studio, go to the view you want, expand the object tree for it, drag and drop the Columns object (folder icon) onto the connection window. Bang, full list of the columns, comma-separated and everything. No typing required, though you may want to add some line-breaks to it for readability. If the style you want is one column per row in the trigger definition, copy-and-paste into Word, search-and-replace comma-space with comma-paragraph. Takes about 10 seconds for any number of columns.
Another option is to select the column names from sys.columns, and add any formatting through string functions. For example:
select char(9) + name + ','
from sys.columns
where object_id = object_id(N'Numbers');
That'll give you one column name per row, with a tab-indent in front of each one, and a comma after it. You can easily change that to a comma before by changing the sequence in the Select. If you use a CTE and the row_number function, you can even make it so that the first one (preceding comma) or last one (trailing comma) doesn't have a comma, and you could even set up something that takes the object name as an input parameter and returns the list for you that way.
On the subject of being able to find out everything that was modified today (for example): What good does it do you to find everything modified today in one sweeping query, if you then have to work out what tables the actual objects are in, and then write one query per table to actually find any data about them?
Say, for example, object ID "C2442297-55AA-476D-A4CD-BD0BEC971039" was modified at 3 AM today. Is that a person? A car? An order? An employee? A customer? You have to determine that, and then build a query to find out what the details are. Or do you have some huge, dynamic query that takes an object ID and works out the joins for you?
Plus, even by having a "Date Modified" column, all you get is that something was changed. Do you know, somehow, what it was changed from and to? Does your code change the date modified column if last name "Smith" is changed to "Smith"? A trigger will consider it changed if that column is included in the update command, regardless of whether it was the same value before and after.
Or do you separately log the before-and-after values (or at least the before values, since you can extrapolate the after values from the current values in the table)?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 11, 2009 at 8:28 am
Hi GSquared,
thank you for the suggestion/trick: it seems a reasonable approach.
For what modifications are concerned, yes now application is logging changes in other tables and I knew the trigger "issue".
I was wondering if there could be another strategy from logging changes at db level in automatic way.
We did a test with trigger invoking a UDF/procedure, but that seems to put too burden on huge updates/inserts: we'd like to give record history (time view), but that seems fairly complex,especially when relationships are involved. Is there any new helpufult approach coming from SQL 2005/2008 ?
March 11, 2009 at 8:43 am
PMFJI,
In regards to what happens if the database designer forgets to add one of the "important" columns...
All of our tables have a "header portion", we invoked a requirement that all tables have a certain number of columns included, i.e. CreatedOn, CreatedBy, and so on. I then created a template in SSMS and passed that around to all the developers on the shop. Each developer knows to use that template or even just copy/paste from an existing table the header portion. For ease of use, i even put in those replaceable parameters.
For example...
/*
AUTHOR:
DATE:
PURPOSE:
To use this template just copy and paste what is here, change the table name
(leave the "dbo." in there), and then add your remaining fields.
*/
IF EXISTS(SELECT * from dbo.sysobjects WHERE id = object_id(N'[dbo].[ ]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[ ]
GO
CREATE TABLE dbo. (
dx int NOT NULL constraint PK_ PRIMARY KEY CLUSTERED,
isActive bit NOT NULL constraint DF_ _IsActive DEFAULT(1),
CreatedBy int NOT NULL,
CreatedOn datetime NOT NULL constraint DF_ _CreatedOn DEFAULT(GETDATE()),
UpdatedBy int,
UpdatedOn datetime,
-- Remaining fields defined below
IsMustPreSave BIT NOT NULL CONSTRAINT DF_ _IsMustPreSave DEFAULT (0)
)
ON [PRIMARY]
I can create a number of tables pretty fast with this template.
Hope the idea helps,
Brian
Think great, be great!
March 11, 2009 at 9:07 am
As far as enforcing structural requirements on tables, that's too easy. Before a table create/modify script goes from QA to production, check it. You can even create a "standards script" that will check sys.columns for you and make sure everything is being done right, if you don't want to rely on human-checking.
On audit/history logging, I wrote a couple of articles on the subject last year, and the discussion on them were at least as useful as the articles. Both articles are on this site.
http://www.sqlservercentral.com/articles/Auditing/63247/
http://www.sqlservercentral.com/articles/Auditing/63248/
The method I use the most these days is a trigger that inserts an XML representation of the "before" data into a single-table audit trail. Impact on transactions is tiny, data is easy enough to query and use.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply