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 another Opinion (Database Design)

I need some other opinions on whether or not this is considered a proper database design structure.

Here is the relationship...We have PEOPLE, that each can belong to a COMPANY.

PERSON_TABLE
Person_ID
Company_ID

COMPANY_TABLE
Company_ID

Then each person can trust other people of other companies, but can only trust 1 person per company.

My question is this. In order to maintain a constraint of 1 person per company, is it considered OK to add a the redundant column Company_ID to the PERSON_TRUSTED_TABLE(and then creating a composite primary key on the Person_ID/Company_ID) instead of just adding a trigger to the PERSON_TRUSTED_TABLE to uphold the constraint.

PERSON_TRUSTED_TABLE
Person_ID
Trusted_Company_ID
Trusted_Person_ID

I would appreciate anyone's opinion. Thanks so much!

There is a third option although it's not much different from your PK idea. I think the major questions is in how many other tables do you reference the person_truested_table? You need a PK in your table anyway, so if you don't create a composite PK out of (person_id, trusted_company_id), what would you do to create a PK?

1.) would you create a PK column?

In this case you could create a unique key on (person_id, trusted_company_id), which has the same effect as the PK and you don't need a trigger. This would be my personal preference if you reference this table by many others, simply because you have a 1 column PK.

2.) would you create the PK out of (person_id, truested_person_id)?

Well this in not much different from the (person_id, trusted_company_id) idea, so i would add the trusted_company_id column and create the PK on those as you suggested.

What i would not do at all for this problem is to create a trigger.|||

As I understand your issue, it seems that it is necessary to allow all any and all [Person] to trust any and all [Company] -constrained that any one person can ONLY trust one person from any one company.

If that seems about right, then a constraining composite key (PK or UI) in the [Person_Trusted] table is what is needed. That composite key 'should' be on [Person_ID] and [Trusted_Company_ID].

One person, Bill, ID: 25, is allowed to trust only one person for company Acme Industries, ID: 1001. It doens't matter which Acme Industries employee Bill has trusted, once he has a trust relationship with Acme Industires another cannot be allowed unless he first removes the existing relationship. Or he can just UPDATE the [Person_Trusted] table to reflect the replacement 'trusted' person.

As a side note: You don't need the archaic denotation of [_TABLE] on the table names. It will be obvious from the context that [Person], or [Company], or whatever is in fact, a table. And it is wasted keystrokes to have to continually type something that does not provide any value.

|||I understand what you are saying about the _TABLE thing. I just put it in the post to be more descriptive.

I get your point, but do you realize that you can get to the Trusted_Company_ID from the Trusted_Person_ID. So really, by putting the Trusted_Company_ID on the PERSON_TRUSTED_TABLE, it would be redundant data.

Instead of putting the Trusted_Company_ID on the PERSON_TRUSTED_TABLE, you could just create a trigger on that table that gets the Company_ID from the Trusted_Person_ID, and upholds the constraint. Therefore no redundant data.

After this explanation, do you still think that adding the redundant column Trusted_Company_ID to the PERSON_TRUSTED_TABLE is the correct implementation?

Thank you so much for the input. It is very helpful.|||

In my opinion, this is one of those worthy exceptions to 'normalized' data.

Yes, it is a 'little' redundent. However, anytime you manage the Trusted_Person, you will most likely have the Trusted_Company information at hand -so it is not a 'big deal'. Consider establishing [CASCADE UPDATES] and/or [CASCADE DELETES] as appropriate.

My consideration is that using a TRIGGER is a relatively heavy handed approach, adding significant effort to the server to manage what should be a 'simple' CONSTRAINT.

The effect you are seeking is that for any one Person there can be only one Company trusted (it doens't matter who the Trusted_Person is for that Company). Having to traverse through the Trusted_Person-Company to determine if there is already a record for that Person-Company could be unnecessary effort.