Need expert's opinion on table schema...

Hello,
I have seen different types of table schemas employed in
applications and I would like to get the opinions of the
SQL experts...
First some definitions:
LU = Lookup
IP = Index Person
IA = Index Account
Background sample info:
Fred has two accounts number 9999 and 8888
Joe has one account 7777
I can represent these accounts in the following
table examples:
(probably lots more ways to do this, but I am interested
in these two architectures, but would be willing to
entertain other schemas)
Example 1:
Person LU Account
IP Name IP IA IA Num
1 Fred 1 1 1 9999
2 Joe 1 2 2 8888
2 3 3 7777
Example 2:
Person Account
IP Name IA IP Num
1 Fred 1 1 9999
2 Joe 2 1 8888
3 2 7777
My questions to the people who live and breath SQL,
What are the pros and cons to the above table schemas?
Please be specific and if necessary write me in a
separate email : Mike@.e-liss.org
Thanks
Mike"mike" <Mike@.e-liss.org> wrote in message
news:3ff801c3b129$dd3c06e0$a601280a@.phx.gbl...
> Hello,
> I have seen different types of table schemas employed in
> applications and I would like to get the opinions of the
> SQL experts...
> First some definitions:
> LU = Lookup
> IP = Index Person
> IA = Index Account
> Background sample info:
> Fred has two accounts number 9999 and 8888
> Joe has one account 7777
>
> I can represent these accounts in the following
> table examples:
> (probably lots more ways to do this, but I am interested
> in these two architectures, but would be willing to
> entertain other schemas)
>
> Example 1:
> Person LU Account
> IP Name IP IA IA Num
> 1 Fred 1 1 1 9999
> 2 Joe 1 2 2 8888
> 2 3 3 7777
>
> Example 2:
> Person Account
> IP Name IA IP Num
> 1 Fred 1 1 9999
> 2 Joe 2 1 8888
> 3 2 7777
>
Example 1 uses a linking table, and is a more general structure than Example
2. Using a linking table it is possible to model relationships 1-1 1-many
or many-many. Using a foregn key you can only model 1-1 or 1-many. From
just that, you should prefer Example 2. One of the guiding principles of
data modeling is to use the most specific model that meets your needs.
From a performance point of view, Example will be superior as well. Notice
that you can transform Example 1 into Example 2.
CREATE VIEW v_Account
as
select Account.IA, LU.IP, Account.Num
from Account join LU
on Account.IA = LU.IA
But to add or delete anaccount will require 2 operations instead of one.
Also queries will have to perform an additional and unnecessary join.
Or, think of it this way:
LU has a 1-1 relationship with Account. Whenever you see a datamodel with a
1-1 relationship, the related should be merged. There are exceptions, but
all rules have execptions.
David|||>--Original Message--
>"mike" <Mike@.e-liss.org> wrote in message
>news:3ff801c3b129$dd3c06e0$a601280a@.phx.gbl...
>> Hello,
>> I have seen different types of table schemas employed
in
>> applications and I would like to get the opinions of
the
>> SQL experts...
>> First some definitions:
>> LU = Lookup
>> IP = Index Person
>> IA = Index Account
>> Background sample info:
>> Fred has two accounts number 9999 and 8888
>> Joe has one account 7777
>>
>> I can represent these accounts in the following
>> table examples:
>> (probably lots more ways to do this, but I am
interested
>> in these two architectures, but would be willing to
>> entertain other schemas)
>>
>> Example 1:
>> Person LU Account
>> IP Name IP IA IA Num
>> 1 Fred 1 1 1 9999
>> 2 Joe 1 2 2 8888
>> 2 3 3 7777
>>
>> Example 2:
>> Person Account
>> IP Name IA IP Num
>> 1 Fred 1 1 9999
>> 2 Joe 2 1 8888
>> 3 2 7777
>>
>Example 1 uses a linking table, and is a more general
structure than Example
>2. Using a linking table it is possible to model
relationships 1-1 1-many
>or many-many. Using a foregn key you can only model 1-1
or 1-many. From
>just that, you should prefer Example 2. One of the
guiding principles of
>data modeling is to use the most specific model that
meets your needs.
>From a performance point of view, Example will be
superior as well. Notice
>that you can transform Example 1 into Example 2.
>CREATE VIEW v_Account
>as
>select Account.IA, LU.IP, Account.Num
>from Account join LU
> on Account.IA = LU.IA
>But to add or delete anaccount will require 2 operations
instead of one.
>Also queries will have to perform an additional and
unnecessary join.
>Or, think of it this way:
>LU has a 1-1 relationship with Account. Whenever you
see a datamodel with a
>1-1 relationship, the related should be merged. There
are exceptions, but
>all rules have execptions.
>David
>
>.
>
David,
Are there any significant draw backs to not using Ex 1 ?
If not, the what is the purpose of the LU table?
So Fred and Joe can share the same account ?
ie:
IP IA
1 1
2 1
Where in example 2 this is not possible ?
Thanks
Mike|||"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:043e01c3b134$23540330$a001280a@.phx.gbl...
> >--Original Message--
> >
> >"mike" <Mike@.e-liss.org> wrote in message
> >news:3ff801c3b129$dd3c06e0$a601280a@.phx.gbl...
> >> Hello,
> >>
. . .
> David,
> Are there any significant draw backs to not using Ex 1 ?
> If not, the what is the purpose of the LU table?
No.
> So Fred and Joe can share the same account ?
> ie:
> IP IA
> 1 1
> 2 1
> Where in example 2 this is not possible ?
Exactly right. It just allows modeling different kinds of relationships.
David

Need debugging help--stored procedure to move database objects

Hi all--I'm adapting a stored procedure to work on transferring SQL Server 2005 tables and other objects owned under the dbo schema on a database to another non-dbo schema. Given that, I'm trying to use an "if...else" to prevent dbo-owned stored procedures and tables like dtproperties from moving with the rest of the objects to the non-dbo schema. Here's the code:

if exists (select * from sys.objects where object_id = object_id(N'[dbo].[chObjOwner]') and OBJECTPROPERTY(object_id, N'IsProcedure') = 1)

drop procedure [dbo].[chObjOwner]

GO

SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON

GO

CREATE proc chObjOwner( @.usrName varchar(20), @.newUsrName varchar(50))

as

-- @.usrName is the current user

-- @.newUsrName is the new user

set nocount on

declare @.uid int -- UID of the user

declare @.objName varchar(50) -- Object name owned by user

declare @.currObjName varchar(50) -- Checks for existing object owned by new user

declare @.outStr varchar(256) -- SQL command with 'alter schema', 'alter authorization'

set @.uid = user_id(@.usrName)

declare chObjOwnerCur cursor static

for

select name from sys.objects where schema_id = @.uid

open chObjOwnerCur

if @.@.cursor_rows = 0

begin

print 'Error: No objects owned by ' + @.usrName

close chObjOwnerCur

deallocate chObjOwnerCur

return 1

end

fetch next from chObjOwnerCur into @.objName

while @.@.fetch_status = 0

begin

set @.currObjName = @.newUsrName + "." + @.objName

if (object_id(@.currObjName) > 0)

print 'WARNING *** ' + @.currObjName + ' already exists ***'

if @.currObjName in ('dt_addtosourcecontrol','dt_addtosourcecontrol_u','dt_adduserobject','dt_adduserobject_vcs','dt_checkinobject','dt_checkinobject_u','dt_checkoutobject','dt_checkoutobject_u','dt_displayoaerror','dt_displayoaerror_u','dt_droppropertiesbyid','dt_dropuserobjectbyid','dt_generateansiname','dt_getobjwithprop','dt_getobjwithprop_u','dt_getpropertiesbyid','dt_getpropertiesbyid_u','dt_getpropertiesbyid_vcs','dt_getpropertiesbyid_vcs_u','dt_isundersourcecontrol','dt_isundersourcecontrol_u','dt_removefromsourcecontrol','dt_setpropertybyid','dt_setpropertybyid_u','dt_validateloginparams','dt_validateloginparams_u','dt_vcsenabled','dt_verstamp006','dt_verstamp007','dt_whocheckedout','dt_whocheckedout_u','dtproperties')

begin

print 'WARNING *** ' + @.currObjName + ' is a system procedure'

end

else

begin

set @.outStr = "alter authorization on " + @.usrName + "." + @.objName + " to " + @.newUsrName

print @.outStr

set @.outStr = "alter schema " + @.newUsrName + " transfer " + @.usrName + "." + @.objName

print @.outStr

print 'go'

fetch next from chObjOwnerCur into @.objName

end

close chObjOwnerCur

deallocate chObjOwnerCur

set nocount off

return 0

GO

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON

GO

Here's the error I get:

Msg 102, Level 15, State 1, Procedure chObjOwner, Line 51

Incorrect syntax near '0'.

I know that the cursor is having a problem closing and deallocating, but am not sure why the error is happening at this point. Any ideas?

Thanks!

One of your 'begin' s has no 'end'.

need db scripting tool for SS 6.5

Hello dear subscribers,
I need a reliable schema scripting tool for my SS 6.5 DB. Should I just
use Enterprise Manager scripting option, or maybe some 3rd party
solution is better?
Many thanks.you can use the Enterprise tool itself its good....
if u want a scripting of the data then usee sqlinserts from lockwoodtech
--
Shaju Thomas
"msnews.microsoft.com" <osa@.stop-spamming.ch> wrote in message
news:uTw$dtqoDHA.2528@.TK2MSFTNGP10.phx.gbl...
> Hello dear subscribers,
> I need a reliable schema scripting tool for my SS 6.5 DB. Should I just
> use Enterprise Manager scripting option, or maybe some 3rd party
> solution is better?
> Many thanks.
>