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 direction on SSB replacing existing technology

Hello, I have an existing suite of applications in my company where remote laptop users enter data into a local database instance. There are several different applications with several different databases (some users run application A and C, others run A and B and D, etc). All of this data needs to make its way into both their local databases, and to the front office database. In addition, some of the data goes out of the front office to other end users (supervisors get data from people under them).

Currently, we get the data back and forth by creating text files full of SQL statements that are run on either the end user servers or the main server, and we zip and ftp these SQL Script files back and forth.

I am looking into the benefits of replacing this process with a Service Broker application, but I'm still too much of a newbie to understand how this might work. What should my messages contain? Should they contain the same SQL statements that are currently in the script files, and then a SSB process would grab the SQL and run it? Or should the message types be more specific to each application?

any pointers to articles that go beyond "here's what SSB is" would be appreciated.

matt tagliaferri

Why aren't you considering SQL Server Replication?|||I've used replication for other tasks - I've found it to be a bit difficult to manage and troubleshoot. Ansynchonous messaging seems to most closely match our existing method of sending the data around (scripts via ftp).

matt tag
|||

Implementing replication on top of Service Broker will require a lot of effort in terms of building the plumbing to read/write data, marshalling/unmarshalling, managing schemas, monitoring, configuration, etc. I'm not sure if it actually helps reduce the problem.

Service Broker is useful when you truely want to build loosely-coupled apps. That is apps that are not bound w.r.t. availability, time, schemas, administrative control, identities, scalability, etc. But it is more general purpose than replication and therefore requires more effort.

Need Advise on SQL Express with Advanced Services

Hi Guys,

Need some advice on the free SQL Express with Advanced Services provided.

I plan to develop a small departmental multi-user applications with 5 to 6 simultaneous users using the free SQL Express with Advanced Services with VB.net and stored procedures on a local area network.

Is it a good choice to use this free SQL Express with Advanced Services or is it more advisable to purchase a standard version ?

Appreciate if someone could help me to address this issue?

One more thing, can i install the free SQL Express with Advanced Services on a normal desktop with Windows XP SP 2 and utilize it as a server or do i need a proper server with Windows 2003?

Thanks and Regards,

Jansen

If you have a small app and you're sure you'll only have 5 or 6 people generating a small load, then you should be OK with the Express version. I'd still recommend that it go on a server OS, primarilly because of the way security and networking are handled.

You also have some other limitations on Express such as database size and so forth, but you certainly could start your app there and grow into a larger server. All you have to do is backup and restore the database to the Standard Edition, and change your connection strings in your app.

Buck

|||

You should be fine with that, but you need to consider the space limitations as well. You can always grow the app into a Standard Edition. I'd also recommend that you always use a Server OS.

Buck

Need Advise on SQL Express with Advanced Services

Hi Guys,

Need some advice on the free SQL Express with Advanced Services provided.

I plan to develop a small departmental multi-user applications with 5 to 6 simultaneous users using the free SQL Express with Advanced Services with VB.net and stored procedures on a local area network.

Is it a good choice to use this free SQL Express with Advanced Services or is it more advisable to purchase a standard version ?

Appreciate if someone could help me to address this issue?

One more thing, can i install the free SQL Express with Advanced Services on a normal desktop with Windows XP SP 2 and utilize it as a server or do i need a proper server with Windows 2003.

Thanks and Regards,

Jansen

Express is well suited to the task you are discussing and yes it should install on a std windows desktop.