relationship question (conceptual) - beginner  
Author Message
Ekul





PostPosted: Thu Dec 07 12:37:36 CST 2006 Top

SQL Server Developer >> relationship question (conceptual) - beginner

Hi All.
I need make one relationship between 3 tables, but I don't know how to make
this.

Think that I have one table:

'clients'

and one cliente can be an 'person' or an 'company'.

How is the correct way to make this?

'persons' 'companys'
| |
\---------------/
|
clientes


I think in same thing like this, but I thig that is wrong:






CREATE TABLE person

(

CONSTRAINT person_id__person__pk

PRIMARY KEY CLUSTERED(person_id),

--------------------------------------------------------------------------


person_id BIGINT IDENTITY(1,1)

NOT NULL

-- ...

)

CREATE TABLE company

(

CONSTRAINT company_id__company__pk

PRIMARY KEY CLUSTERED(company_id),

-------------------------------------------------------------------------


company_id BIGINT IDENTITY(1,1)

NOT NULL

-- ...

)



CREATE TABLE clients

(

CONSTRAINT clients_id__clients__pk

PRIMARY KEY CLUSTERED(clients_id),

--------------------------------------------------------------------------

CONSTRAINT clients_id1__clientes__fk

FOREIGN KEY(clients_id) REFERENCES person(person_id),

CONSTRAINT clients_id2__clientes__fk

FOREIGN KEY(clients_id) REFERENCES company(company_id),

--------------------------------------------------------------------------

clients_id BIGINT IDENTITY(1,1)

NOT NULL

is_person BIT

NOT NULL

-- ...

)



THANKS

SQL Server47  
 
 
David





PostPosted: Thu Dec 07 12:37:36 CST 2006 Top

SQL Server Developer >> relationship question (conceptual) - beginner

> Hi All.
> I need make one relationship between 3 tables, but I don't know how to
> make this.
>
> Think that I have one table:
>
> 'clients'
>
> and one cliente can be an 'person' or an 'company'.
>
> How is the correct way to make this?
>
> 'persons' 'companys'
> | |
> \---------------/
> |
> clientes
>
>
> I think in same thing like this, but I thig that is wrong:
>
>
>
>
>
>
> CREATE TABLE person
>
> (
>
> CONSTRAINT person_id__person__pk
>
> PRIMARY KEY CLUSTERED(person_id),
>
> --------------------------------------------------------------------------
>
>
> person_id BIGINT IDENTITY(1,1)
>
> NOT NULL
>
> -- ...
>
> )
>
> CREATE TABLE company
>
> (
>
> CONSTRAINT company_id__company__pk
>
> PRIMARY KEY CLUSTERED(company_id),
>
> -------------------------------------------------------------------------
>
>
> company_id BIGINT IDENTITY(1,1)
>
> NOT NULL
>
> -- ...
>
> )
>
>
>
> CREATE TABLE clients
>
> (
>
> CONSTRAINT clients_id__clients__pk
>
> PRIMARY KEY CLUSTERED(clients_id),
>
> --------------------------------------------------------------------------
>
> CONSTRAINT clients_id1__clientes__fk
>
> FOREIGN KEY(clients_id) REFERENCES person(person_id),
>
> CONSTRAINT clients_id2__clientes__fk
>
> FOREIGN KEY(clients_id) REFERENCES company(company_id),
>
> --------------------------------------------------------------------------
>
> clients_id BIGINT IDENTITY(1,1)
>
> NOT NULL
>
> is_person BIT
>
> NOT NULL
>
> -- ...
>
> )
>
>
>
> THANKS
>
>

CREATE TABLE client
(client_id BIGINT NOT NULL
PRIMARY KEY);

CREATE TABLE person
(client_id BIGINT NOT NULL
PRIMARY KEY REFERENCES client (client_id));

CREATE TABLE company
(client_id BIGINT NOT NULL
PRIMARY KEY REFERENCES client (client_id));

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--



 
 
petery





PostPosted: Thu Dec 07 21:18:34 CST 2006 Top

SQL Server Developer >> relationship question (conceptual) - beginner Hello,

I agree with David's suggestion might be most efficient. However, if this
does not meet your requirement, you may also want to use trigger to keep
the reference integration of the table. For example, you may use code like
following:

create trigger clients_check on clients for insert as

begin




begin


end
else begin

end

begin
RAISERROR ('client does not exist', 16, 1)
ROLLBACK TRANSACTION
end
End

If you have any commments or feedback, please feel free to let's know.
Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 
 
petery





PostPosted: Tue Dec 12 03:08:45 CST 2006 Top

SQL Server Developer >> relationship question (conceptual) - beginner Hi,

I'm still interested in this issue. Did the suggestions help in resolving
the problem? If you have any comments or questions, please feel free to
let's know. We look forward to hearing from you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================