Prev: MSSQL 2005, TSQL, Keywords and variables, Case sensitive or not?
Next: Replicating from SQL 2000 to SQL 2008
From: wackyphill on 18 Mar 2010 08:28
> One thing to consider is that if there are two people in the same role
> for the same property, one of them may be the main responsible, which
> could call for a "ismainreponsible" column, together with a unique
> filtered index(*) on "(role, property) where is mainresponsible = 1".
>
> But this may not at all be the case. I only mention this, because when
> I have encountered such relation there has often been an "isdefault" or
> "ismain" flag.
>
> (*) Filtered indexes is a new feature in SQL 2008, and for SQL 2005 you
> would have to code the same condition in some different (and more
> kludgy) way.


I like the idea Erland. I wish I could use 2008 for this but must use
2005 for now. There are a few types where yes, there is a primary
(MainResponsible). What I have been doing is modeling that as a
Foreign Key field of the Property table. So for example there is
technically only 1 real Property manager so there is a
PrimaryManagerID field in the property table.

However there are situations where other people need to act as
property manager for that property in order to provide coverage, etc.
So that's how this Many2Many table came into being. Do you think this
is a bad thing to do?

The problem with the way I'm doing it is I need to decide if the
Many2Many table should include the primary person as well as the non-
primary people or just the non-primary people.

What do you think?
From: Erland Sommarskog on 18 Mar 2010 18:48
wackyphill(a)yahoo.com (wackyphill(a)gmail.com) writes:
> I like the idea Erland. I wish I could use 2008 for this but must use
> 2005 for now. There are a few types where yes, there is a primary
> (MainResponsible). What I have been doing is modeling that as a
> Foreign Key field of the Property table. So for example there is
> technically only 1 real Property manager so there is a
> PrimaryManagerID field in the property table.
>
> However there are situations where other people need to act as
> property manager for that property in order to provide coverage, etc.
> So that's how this Many2Many table came into being. Do you think this
> is a bad thing to do?

No, it sounds right.

> The problem with the way I'm doing it is I need to decide if the
> Many2Many table should include the primary person as well as the non-
> primary people or just the non-primary people.
>
> What do you think?

I think you should have everyone there. Then you can set up an indexed
view over the primary onces. The point here is that the indexed view
can serve to enforce that there can only be one main for each role.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Michelle Terry on 18 Mar 2010 20:28

> I think you should have everyone there. Then you can set up an indexed
> view over the primary onces. The point here is that the indexed view
> can serve to enforce that there can only be one main for each role.

OK, I understand.

Thanks so much for sharing your expertise!
From: wackyphill on 18 Mar 2010 20:30

> I think you should have everyone there. Then you can set up an indexed
> view over the primary onces. The point here is that the indexed view
> can serve to enforce that there can only be one main for each role.


OK, I understand.

Thanks so much for sharing your expertise!




First  |  Prev  | 
Pages: 1 2
Prev: MSSQL 2005, TSQL, Keywords and variables, Case sensitive or not?
Next: Replicating from SQL 2000 to SQL 2008