Database

Bad Robot wrote:

Would it be better to split the table into smaller tables?

Bad Robot wrote:

Consider a factory. The factory pollutes. In order to keep track of the pollution, the factory owner has to fill in some 20 values of different kinds. Normally you would place these values on the factory table, but since there are so many of them, you might be able to put the pollution data in another table called PollutionData. But somehow I can't get this to work properbly. I have the factory referencing the PollutionId in the PollutionData table.

Bad Robot wrote:

A cascade delete will only work the other way around!

Bad Robot wrote:

And I want the database server to produce an error if someone tries to delete pollution data referenced by a factory.

Bad Robot wrote:

As I see it - the only way to solve this is to put the pollution columns into the Factories table, but I have this kind "oh noes - this is wrong"-feeling - and that's just because there are so many columns.

Colin Angus Mackay wrote:

If you have 20 non-repeating columns, are they (as a block) optional? In otherwords: Can a factory exist without these values?

Colin Angus Mackay wrote:

From what you've said already, regardless of the details of how the data relates, it is obvious that the Factory is the parent. A factory pollutes, without the factory you don't need the pollution data. Therefore, the PollutionData should reference the Factory table. The PollutionData table should have a FactoryId column.

Bad Robot wrote:

If I give the PollutionData table a FactoryId this would give me the opportunity to create more than one row of PollutionData referencing the same factory.

Bad Robot wrote:

I have the factory referencing the PollutionId in the PollutionData table.

CREATE TABLE [Factory] (
    [FactoryID] [int] IDENTITY (1, 1) NOT NULL ,
    [FactoryName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK_Factory] PRIMARY KEY  CLUSTERED 
    (
        [FactoryID]
    )  ON [PRIMARY] 
) ON [PRIMARY]
GO

CREATE TABLE [Pollution] (
    [PollutionID] [int] IDENTITY (1, 1) NOT NULL ,
    [FactoryID] [int] NULL ,
    [PollutionDesc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK_Pollution] PRIMARY KEY  CLUSTERED 
    (
        [PollutionID]
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_Pollution_Factory] FOREIGN KEY 
    (
        [FactoryID]
    ) REFERENCES [Factory] (
        [FactoryID]
    ) ON DELETE CASCADE 
) ON [PRIMARY]
GO


insert into factory (FactoryName) values ('Foo1')
insert into Pollution (FactoryID,PollutionDesc) values (SCOPE_IDENTITY(),'Garbage')

insert into factory (FactoryName) values ('Foo2')
declare @nF1ID int
select @nF1ID = SCOPE_IDENTITY( )
insert into Pollution (FactoryID,PollutionDesc) values (@nF1ID,'Toxic Waste')
insert into Pollution (FactoryID,PollutionDesc) values (@nF1ID,'Sewage')

insert into factory (FactoryName) values ('Foo3')
insert into Pollution (FactoryID,PollutionDesc) values (SCOPE_IDENTITY( ),'Tar')

delete from factory where FactoryID = @nF1ID
Go

drop table pollution
drop table factory

CREATE TABLE [Factory] (
    [FactoryID] [int] IDENTITY (1, 1) NOT NULL ,
    [FactoryName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK_Factory] PRIMARY KEY  CLUSTERED 
    (
        [FactoryID]
    )  ON [PRIMARY] 
) ON [PRIMARY]
GO
CREATE TABLE [Pollution] (
    [PollutionID] [int] IDENTITY (1, 1) NOT NULL ,
    [FactoryID] [int] <font size="5">NOT </font>NULL ,
    [PollutionDesc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK_Pollution] PRIMARY KEY  CLUSTERED 
    (
        [PollutionID]
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_Pollution_Factory] FOREIGN KEY 
    (
        [FactoryID]
    ) REFERENCES [Factory] (
        [FactoryID]
    ) ON DELETE CASCADE 
) ON [PRIMARY]
GO

insert into factory (FactoryName) values ('Foo1')
insert into Pollution (FactoryID,PollutionDesc) values (SCOPE_IDENTITY(),'Garbage')

insert into factory (FactoryName) values ('Foo2')
declare @nF1ID int
select @nF1ID = SCOPE_IDENTITY( )
insert into Pollution (FactoryID,PollutionDesc) values (@nF1ID,'Toxic Waste')

insert into factory (FactoryName) values ('Foo3')
insert into Pollution (FactoryID,PollutionDesc) values (SCOPE_IDENTITY( ),'Tar')

delete from factory where FactoryID = @nF1ID
Go

Bad Robot wrote:

Primary Key in one table, but also primary key in another table

Bad Robot wrote:

UNIQUE constraint could be a way to go

Colin Angus Mackay wrote:

That won't fix that part of the problem. Making FactoryId the primary key or putting a unique constraint on it will.

CREATE TABLE [Pollution] (
    [PollutionID] [int] IDENTITY (1, 1) NOT NULL ,
    [FactoryID] [int] NOT NULL ,
    [PollutionDesc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK_Pollution] PRIMARY KEY  CLUSTERED 
    (
        [PollutionID]
    )  ON [PRIMARY] ,
    CONSTRAINT [IX_FactoryID] UNIQUE  NONCLUSTERED 
    (
        [FactoryID]
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_Pollution_Factory] FOREIGN KEY 
    (
        [FactoryID]
    ) REFERENCES [Factory] (
        [FactoryID]
    ) ON DELETE CASCADE 
) ON [PRIMARY]
GO

myDataSet[0].Tables[0].Row[i]