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]

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" <ve*******@538mail.nl> wrote in message
news:4d**************************@posting.google.c om...

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

[Erland Sommarskog]

Versteijn (ve*******@538mail.nl) writes:

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.

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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Versteijn]

"David R Rawheiser" <ra*******@hotmail.com> wrote in message news:<c4********************@comcast.com>...

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.

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]

Versteijn (ve*******@538mail.nl) writes:

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?

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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp