Small problem

[Versteijn]

Hello

I have a case where Partners are some kind of Super-Users and are
stored in a SQL Server database. Best is IMO to put both in the same
table:

table Customers:
CustomerID[pr.key]
[blabla]
PartnerID

But of course I have to reference the partnerid from another table and
I want SQL Server to maintain the integrity rules. I could split
Customers en Partners into different tables, but that would not be
wise i think.

Or I could just reference the CustomerID from the other table and
-know- that we are talking about a partner, but in that case it it
possible to reference a customer that is not a partner, and i want to
avoid that.

Any ideas?

Freek Versteijn

[David R Rawheiser]

Re: Small problem

The wonders of views ...

Create a view called Partners and have it select only the partners from
Customer.

That way in your other sql you can reference the view and then not have to
make sure the critieria to only show partners is included.

Going further you may want to have you table called Person and have two
views - Customers and Partners - that select from the same table, but with
different criteria (and perhaps columns in the result set).

Make sure the SQL in the view is optimized (i.e. the columns refered to in
the where clause are properly indexed).

"Versteijn" <versteijn@538m ail.nl> wrote in message
news:4d19834f.0 307040410.54e3d e39@posting.goo gle.com...[color=blue]
> Hello
>
> I have a case where Partners are some kind of Super-Users and are
> stored in a SQL Server database. Best is IMO to put both in the same
> table:
>
> table Customers:
> CustomerID[pr.key]
> [blabla]
> PartnerID
>
> But of course I have to reference the partnerid from another table and
> I want SQL Server to maintain the integrity rules. I could split
> Customers en Partners into different tables, but that would not be
> wise i think.
>
> Or I could just reference the CustomerID from the other table and
> -know- that we are talking about a partner, but in that case it it
> possible to reference a customer that is not a partner, and i want to
> avoid that.
>
> Any ideas?
>
> Freek Versteijn[/color]

[Erland Sommarskog]

Re: Small problem

Versteijn (versteijn@538m ail.nl) writes:[color=blue]
> I have a case where Partners are some kind of Super-Users and are
> stored in a SQL Server database. Best is IMO to put both in the same
> table:
>
> table Customers:
> CustomerID[pr.key]
> [blabla]
> PartnerID
>
> But of course I have to reference the partnerid from another table and
> I want SQL Server to maintain the integrity rules. I could split
> Customers en Partners into different tables, but that would not be
> wise i think.
>
> Or I could just reference the CustomerID from the other table and
> -know- that we are talking about a partner, but in that case it it
> possible to reference a customer that is not a partner, and i want to
> avoid that.[/color]

You could have two tables. One main table with the Customers, including
partners. And then a subtable with the partners only. This permits
tables where only partners are allowed to have referential integrity to
that table only.




--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Versteijn]

Re: Small problem

"David R Rawheiser" <rawhide58@hotm ail.com> wrote in message news:<c4WdnWLf3 rI9X5WiXTWJiQ@c omcast.com>...[color=blue]
> Every index requires additional overhead when doing an insert or update on
> the indexed column, but that is the price for the later search and retrieval
> speed (you can't get something for nothing in this world).
>
> Hopefully the keys being searched on won't change very often (how often do
> you change someone from a partner to a customer?).
>
> If the application does in-place updates the performance overhead should be
> minimal.
> if the application does delete and insert to update that obviously would be
> more.
>
> Benchmark it to see for sure.[/color]

Ok, thank you, I will.

One more question I forgot to ask you. This all started with searching
for a way to reference a non-key field (the PartnerID) of Customers
from a second table. Can I now refer to the PartnerID of the created
view? How can I do that?

Sorry, I'm new to the advanced techniques of SQL and SQL Server, but
I'm sure I can use all this knowledge in the future too :)

Thank you in advance

Freek Versteijn

[Erland Sommarskog]

Re: Small problem

Versteijn (versteijn@538m ail.nl) writes:[color=blue]
> One more question I forgot to ask you. This all started with searching
> for a way to reference a non-key field (the PartnerID) of Customers
> from a second table. Can I now refer to the PartnerID of the created
> view? How can I do that?[/color]

If you mean by a FOREIGN KEY reference, I don't think you can. It could
be that this is possible if you index the view. I have no tested this,
but I would not expect it to work.

If you want a foreign-key constraint, then PartnerID must be the primary
key in its own table. As I said in another posting, this could be a
sub-table to the Customers table.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp