FK Problem

Good Morning everyone,
I stuck :( Need your help please.
Im creating a table called Bills, where it should have a FK saying to who
this bills belongs. I cant do it, because i have two different tables,
person and company (in the following code company and person only have the
PK and the 'name' column, but there are some columns remaining).
How can i make table bills with fk to a company and a person?
Thanks,
Bruno N
CREATE TABLE [Company] (
[CompanyID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
(
[CompanyID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Person] (
[PersonID] [int] NOT NULL ,
[Name] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[PersonID]
) ON [PRIMARY]
) ON [PRIMARY]
GOBruno,
Without knowing more about your schema, problems of this type are typically
solved by using a sub/super type relationship. i.e. : you have a client
table and then both a person and a company table. The bills table would
contain a FK to the client table and the person and company tables would
contain a FK to the client table.
The idea is that the client table contains details that are common to both
person and company and the person/company tables contain specific
information to people/companies.
Regards,
Rod Colledge
"Bruno N" <nylren@.hotmail.com> wrote in message
news:%23ihyUpNRFHA.1096@.tk2msftngp13.phx.gbl...
> Good Morning everyone,
> I stuck :( Need your help please.
> Im creating a table called Bills, where it should have a FK saying to who
> this bills belongs. I cant do it, because i have two different tables,
> person and company (in the following code company and person only have the
> PK and the 'name' column, but there are some columns remaining).
> How can i make table bills with fk to a company and a person?
> Thanks,
> Bruno N
>
> CREATE TABLE [Company] (
> [CompanyID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
> (
> [CompanyID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [Person] (
> [PersonID] [int] NOT NULL ,
> [Name] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
> (
> [PersonID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
>|||If I understand correctly the person table should have companyid column
as a person belongs to company.
Are bills will be created for person or company, if its created for
person use personID in bills table and if its for company use companyID
in bills table.
You need to be more descriptive get solutions for this kind of problems.|||You have a table for creditors, some of whom are corporate and some of
whom are individuals. Also, stop using IDENTITY and that "Magical
Universal" NVARCHAR (50) data type before you collect a ton of garbage
data in your tables.|||I'm not one of the experts in this forum, but maybe I can suggest something.
I believe you would need another table called Clients with at least these
columns.
clientNumber (you must have client numbers right?)
client type (would be either company or person)
...
The Bill table would have a foreign key reference (clientNumber) to the
Client table.
The Companies and Persons tables would stay and have a foreign key reference
to the Clients table.
You could then build a query to list bills for persons and/or companies
"Bruno N" <nylren@.hotmail.com> wrote in message
news:%23ihyUpNRFHA.1096@.tk2msftngp13.phx.gbl...
> Good Morning everyone,
> I stuck :( Need your help please.
> Im creating a table called Bills, where it should have a FK saying to who
> this bills belongs. I cant do it, because i have two different tables,
> person and company (in the following code company and person only have the
> PK and the 'name' column, but there are some columns remaining).
> How can i make table bills with fk to a company and a person?
> Thanks,
> Bruno N
>
> CREATE TABLE [Company] (
> [CompanyID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
> (
> [CompanyID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [Person] (
> [PersonID] [int] NOT NULL ,
> [Name] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
> (
> [PersonID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
>|||Ah, the multiple parent problem. I've come across this many times. There are
couple of ways of solving it. One (I believe) relates to what Mr. Celko was
suggesting. You create a master table of Id's (called Creditors) to rule the
m
all and make Companies and Persons child to this table. So something like:
Create Table Creditors
(
CreditorId Int Not Null Primary Key Clustered
)
Create Table Persons
(
PersonId Int
, FirstName VarChar(50)
, LastName VarChar(50)
, Constraint FK_Creditors_Persons Foreign Key PersonId References Creditors
(CreditorId)
)
Create Table Companies
(
CompanyId Int
, Name VarChar(50)
, Constraint FK_Creditors_Companies Foreign Key CompanyId References
Creditors (CreditorId)
)
Create Table Bills
(
BillId Int
, CreditorId Int
, Constraint FK_Creditors_Bills Foreign Key CreditorId References Creditors
(CreditorId)
)
The upside to this solution is that it prevents you from deleting a Person o
r
Company if they have bills associated with them. One downside is that you ha
ve a
table with nothing but Ids and no other property. You can't really add a Nam
e
column for Creditors because for a Person this may change and is an amalgama
tion
of Person data. To do that, you'd have to write triggers to handle the
synchronization and it makes for a right ole mess. Another downside is that
everytime you wish to add a Person or Company, you have to add a Creditor fi
rst.
One possible solution would be to look at the data you are storing for Perso
ns
and Companies. If the only data you care about for a company is the name and
likewise for a Person (although broken into parts), you might combine the tw
o
tables into a single table called Contacts (or Creditors) like so:
Create Table Contacts
(
ContactId
, FirstName VarChar(50)
, MiddleName VarChar(50)
, LastName VarChar(50)
, CompanyName VarChar(50)
)
Then you would simply link Bills to Contacts. This has it owns fleas. One
problem you might run into for example, is if a Bill is attributed to a Comp
any
as opposed to a Person at a Company. If that is the case, you would need to
be
careful to choose the row that has Null name values but has a CompanyName va
lue.
It's not perfect, but it does make some design problems easier to solve.
HTH,
Thomas|||On Tue, 19 Apr 2005 09:17:12 -0300, Bruno N wrote:

>Good Morning everyone,
>I stuck :( Need your help please.
>Im creating a table called Bills, where it should have a FK saying to who
>this bills belongs. I cant do it, because i have two different tables,
>person and company (in the following code company and person only have the
>PK and the 'name' column, but there are some columns remaining).
>How can i make table bills with fk to a company and a person?
>Thanks,
>Bruno N
Hi Bruno,
As a diversion fro the other posts, here's an alternate approach to
this:
CREATE TABLE Bills
(......
,PersonID int DEFAULT NULL
,CompanyID int DEFAULT NULL
,CHECK ((PersonID IS NULL AND CompanyID IS NOT NULL)
OR (PersonID IS NOT NULL AND CompanyID IS NULL))
,FOREIGN KEY (PersonID) REFERENCES Persons
,FOREIGN KEY (CompanyID) REFERENCES Companies
,...
)
If the only thing thaat persons and companies have in common is that
they can get billed, and they are otherwise playing completely different
roles in your database, then this would be my preferred solution. Trying
to introduce a supertype over two subtypes that are (in the context of
your system) not actually related at all is a kludge, that will start to
bite you sooner or later.
Of course, if the persons and companies do have less differences and
more in common, then introducing a supertype would be a valid approach.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)