1:many with independent many

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tina - 29 Nov 2007 22:52 GMT

I have a 1:Many relationship. table A (one) and table b (many) but I want
to be able to have table b records exist independently. How do I build that
relationship with SQL Server?
Thanks,
T

Reply to this Message

David Portas - 29 Nov 2007 22:57 GMT

>I have a 1:Many relationship. table A (one) and table b (many) but I want
>to be able to have table b records exist independently. How do I build
>that relationship with SQL Server?
> Thanks,
> T

Please explain what you mean by "exist independently". Perhaps it would help
to show some example data but on the other hand maybe all you need is
another table.

Signature

David Portas

Reply to this Message

Tina - 29 Nov 2007 23:33 GMT

Its called an "independent relationship" : a child table record can exist
without the parent. Now I remember - in the child table the foreign key can
be nulls ok.
T

>>I have a 1:Many relationship. table A (one) and table b (many) but I want
>>to be able to have table b records exist independently. How do I build
[quoted text clipped - 5 lines]
> help to show some example data but on the other hand maybe all you need is
> another table.

Reply to this Message

David Portas - 30 Nov 2007 07:02 GMT

> Its called an "independent relationship" : a child table record can exist
> without the parent.

In that case, no foreign key would be required at all.

> Now I remember - in the child table the foreign key can
> be nulls ok.

True, but then it would be better to create another table in my
opinion. Two different types of relationship and two different
entities (one with the optional attribute and one without) are
implied. I don't see any obvious reason to force them into one table.

--
David Portas

Reply to this Message

Manfred Sorg - 30 Nov 2007 07:34 GMT

On 30 Nov., 08:02, David Portas
<REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote:

> implied. I don't see any obvious reason to force them into one table.

Hi David,

it's nearly usual. If you have grouping criteria in your customers
database and you have customers which aren't yet according to one
group you have two possible ways to go:
1) inserting a group "no group" or
2) allowing null
Both ways have their benefits. But in both cases foreign keys have
benefits in database design and in query performance.

Bye, Manfred

Reply to this Message

David Portas - 30 Nov 2007 09:38 GMT

> On 30 Nov., 08:02, David Portas
>
[quoted text clipped - 8 lines]
> 1) inserting a group "no group" or
> 2) allowing null

or:

3) creating a separate table

CREATE TABLE Customer
(CustomerID INT NOT NULL PRIMARY KEY,
CustomerName ...
... /* other columns */);

CREATE TABLE CustomerGroup
(CustomerID INT NOT NULL PRIMARY KEY
REFERENCES Customer (CustomerID),
CustomerGroupID INT NOT NULL
REFERENCES CustomerGroup (CustomerGroupID)
);

--
David Portas

Reply to this Message