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