Detect if rows have changed

I want to build some pseudo-update-trigger code that will tell me if the a
row in an Access database has changed. My overall strategy is do successive
imports into SQL Server, join the tables and compare the rows. Is there a
reliable way to determine if a row has changed? I'd like to design a join
where only changed rows are returned. The rows are up to 1000 characters. I
can see that the LEN function won't be reliable. Any help would be
appreciated.I'm only interested in checking if one column in the table has changed.|||Terri
Look at virtual tables within a trigger (deleted,inserted)
"Terri" <terri@.cybernets.com> wrote in message
news:d2ui0q$7al$1@.reader2.nmix.net...
> I want to build some pseudo-update-trigger code that will tell me if the a
> row in an Access database has changed. My overall strategy is do
successive
> imports into SQL Server, join the tables and compare the rows. Is there a
> reliable way to determine if a row has changed? I'd like to design a join
> where only changed rows are returned. The rows are up to 1000 characters.
I
> can see that the LEN function won't be reliable. Any help would be
> appreciated.
>|||I tried creating a trigger on a view based on a table in a linked server.
CREATE TRIGGER
TestTrigger
ON vwComplianceTest
FOR INSERT, UPDATE, DELETE
AS
RAISERROR (50009, 16, 10)
GO
I got an error "invalid object name". The view itself is working. Also tried
to name it [dbo].[vwComplianceTest]. Is it possible to create a trigger on
a view based on a table in a linked server? Is this what you were
suggesting?
Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eXz4jZmOFHA.2728@.TK2MSFTNGP15.phx.gbl...
> Terri
> Look at virtual tables within a trigger (deleted,inserted)
>|||On Wed, 6 Apr 2005 10:36:56 -0600, Terri wrote:

>I tried creating a trigger on a view based on a table in a linked server.
>CREATE TRIGGER
>TestTrigger
>ON vwComplianceTest
>FOR INSERT, UPDATE, DELETE
>AS
>RAISERROR (50009, 16, 10)
>GO
Hi Terri,
You can't define regular triggers on views, only INSTEAD OF triggers.
I don't know if the linked server messes that up. You'll have to test
it.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Design question regarding comma delimited data

Hi,

I'm trying to figure out if it's good design practice to have several pieces of data in a one column. I explain by example. Let's say you build a movie site. Each movie can belong to several categories. A movie can be Action, Adventure, Fantasy and Drama all at once. Assume a database table with all the movies and another table with all the categories. Now how would I associate one movie with several categories? Would it be OK if I add a Category field in the movie table and then add several categories in that, delimited by commas? Sort of like below:

movie_title | movie_rating | category_name
------------------------------
Pirates of the Carribean | PG-13 | Action,Adventure,Fantasy
Evil Dead | Unrated | Horror

and so on ...

I can then query the database with a LIKE query if I want to select all movies of a certain category. Personally, I don't like this approach to much, but I can't think of another way to achieve this. Well, there is one other, but I like that one even less. I could create another table that links each movie to a category, but his way each movie with several categories would get a new row. Using the table above, Pirates would get three rows in that table. One with Action, one with Adventure and one with Fantasy. Get my drift?

It all seems counter-intuitive. Thoughts?

Thanks :o)

You should go with the second approach (separate table where you store MovieId, CategoryId) unless you have a good reason to not do it so.

Think about the fact that search by category is not the only possible query you will need. How would you do implement the following using your first approach:Select the number of movies in each category? Using a separate table you can just group by category and do a count().

Keep in mind that normalization is almost always a good thing to do. If you don't know what that means you really have to take a book on database design and read about it.

|||

Create a child table that joins the two tables together

Your Movies Table
MovieID int identity PK
Title varchar
Rating varchar

Your Categories Table
CategoryID int identity PK
Name varchar

Your MovieCategories table
MovieID int
CategoryID int

So if you enter a movie in the Movies table
Title: Pirates of the Carribean
Rating: PG-13

It would get assigned an auto-incrementing ID (in this case 1)

You then populate your Category Table with things like
Action (gets assigned 1)
Adventure (gets assigned 2)
Drama (gets assigned 3)
Comedy (gets assigned 4)
Fantasy (gets assigned 5)

Then in your child table you assign the MovieID for Pirates with the CategoryID's that it belongs to
MovieID CategoryID
1 1
1 2
1 5

Then when you query you would join the necessary tables together to get what you are after.

|||

>>Now how would I associate one movie with several categories?

The method you described isvery typical ( a third table to link the other two).

Table: MovieCategoriesMovieID CatID1 11 32 23 13 23 3

This is the utilimate in flexibility but requires more work from you.

A quick and dirty way (not always bad) would be a boolean column for each category.

This assumes that categories are fixed and you will not be adding new categories

|||

The best will be to create table

Movie with MovieID and Movietittle

Ratings with RatingID and RatingName

Category CategoryID and categoryname

and two tables to link your information

MovieRatings with mrID, mrMovieID, mrRatingID

MOvieCategory with mcID, mcMovieID ,mcCategoryID

With this structure your queries will be much faster when with like statement and also it will save you same space in database

You can have multiple categories connected to movie in movie category table and also different ratings if you need.

Thanks

|||

Thanks for all the replies.

See, I used the, for lack of a better word, "third table" approach before. It's the only way I could think of doing what I wanted it to do. I just never liked that idea of having to create another table. But apparently this is pretty common, so I don't feel so bad about it anymore :o)

It's basically going to look like this:

Movies
movie_id movie_title movie_rating
-------------
1 Bad Movie PG
2 Cool Movie R
3 So So Movie PG-13
Categories
category_id category_name
----------
1 Action
2 Adventure
3 Comedy
4 Drama
Movies/Categories
movie_id category_id
--------
1 1
1 2
2 3
2 4
3 3

I guess that makes good sense, but it always seemed redundant.

Thanks :o)

design question about webservices

Hi,

I need to expose data in a SQL server 2005 database as a webservices. I have 2 options to choose. I can create an End point in sql or build a webservices in .NET.

So what are de advantages and disadvantages of those 2 approaches ?

Of course they want the webservices secure. I know that authentication is not a problem since we use windows integrated security for the intranet. But what will happen if we move to an extranet or the internet ?

But how do you enable authorization ? and Context security ? Is context security better done in .NET then in SQL ?

Thanks in advance.

Deciding which approach is better for your scenario will also depend on a couple key questions:

If the expected workload requires you to have some web farm like scalability, then you will need to go with ASP.NET If you need the ability to customize the WSDL generation and/or web method result structure then you need to go with ASP.NET|||

It is very clear.

Thank you very much.

CE

Design Question

I have been asked to build a database that tracks children and households. I have come up with the following sample and would like some input. The idea is that a child could have two parents and each parent could live a different locations (Divorced). So I need to link the mother and father records to the child or children.

My real question is how I could then query a list of client relations (household) that could then be used possibly for a directory. For example;

Jimmy Smith Student
Jane Smith Mother
Jack Smith Father

Thank you for any help.

tblClients (all parents and children are entered here)
Client_ID
etc...
tblRelations (there is a one-to-many relation on the tblClients) One client could have many related client records. The client_ID of related client records is entered into a related_ID field of the tbRelations table.
Rel_ID
Client_ID
Related_ID
Relationship (mother, father etc...)I'd set it up as the following tables...

tblClients
ClientID int identity
ClientName varchar (30)
tblRelationshipTypes
RelationshipTypeID int identity
RelationshipTypeName varchar(15)
TblRelationships
ChildID int (stores the ClientID of the child)
ParentID int (stores the ClientID of the parent)
RelationshipTypeID int

You could then create the following view

SELECT Child.ClientID as ChildID,
Child.ClientName AS ChildName,
Parents.ClientName AS ParentName,
tblRelationshipTypes.RelationshipTypeName
FROM tblClients Child,
tblClients Parents,
tblRelationships,
tblRelationshipTypes
WHERE Child.ClientID = tblRelationships.ChildID AND
Parents.ClientID = tblRelationships.ParentID AND
tblRelationshipTypes.RelationshipTypeID = tblRelationships.RelationshipTypeID

which would return

ChildID ChildName ParentName RelationshipTypeName
---- ---------- ---------- -------
1 Joey Mrs. Smith Mother
1 Joey Mr. Smith Father
2 Susie Ms. Wilson Aunt
3 Billy Ms. Jones Mother
3 Billy Mr. Steves Father

Design of Dimension table ID in a datamart - best practise

Best practise wanted!

In a datamart (DM) a fact row is linked to a dimension by a dimension ID (key). If a DM is build on top of a (enterprise) datawarehouse (EDW), and the dimension table has a corresponding EDW table with a unique ID (dummy key), will there then be any DM design conciderations for not using that EDW table key.

In other words when you design the DM, should the dimensions then have there own (new) ID's regardless of you allready have modelled (unique) ID in the EDW. I have these considerations in favour for using the EDW table ID keys i a dimension:

1. Staging the DM is easy, the EDW table staging is handling the correctness of the dimension ID's.

2. Backtracing concering DM quality checking, errors and so on, is simplyfied by using the same ID in both DM and EDW.

No. Use the same technical key in DM as in EDW.

If you have a product dimension that is used in several data marts, like sales or production planning, you would like to use the same key in order to consolidate information in these data marts. In SSAS 2000/2005 you can then build cubes with the same product dimension which also means less dimension design and maintenance.

HTH

Thomas Ivarsson

|||Thanks