1 to Many Sql insert

Question

I'm having problems figuring out how to insert a row into my database. I have an experiment table which has the following columns

CREATE TABLE [dbo].[Experiment] (
    [ExperimentId] INT           IDENTITY (1, 1) NOT NULL,
    [Experiment]   VARCHAR (100) NOT NULL,
    [Account]      VARCHAR (100) NOT NULL,
    [Profile]      VARCHAR (100) NOT NULL,
    [Property]     VARCHAR (100) NOT NULL,
    [BucketId]     INT           NULL,
    CONSTRAINT [PK_Experiment] PRIMARY KEY CLUSTERED ([ExperimentId] ASC),
    CONSTRAINT [FK_Experiment_Bucket] FOREIGN KEY (BucketId) REFERENCES Bucket (BucketId)
);

I also have a Bucket table

CREATE TABLE [dbo].[Bucket] (
    [BucketId]   INT      IDENTITY (1, 1) NOT NULL,
    [BucketName] CHAR (1) NOT NULL,
    CONSTRAINT [PK_BucketId] PRIMARY KEY CLUSTERED ([BucketId] ASC)
);

An experiment can have many buckets (A-Z), however, I'm not really sure how to insert the data. I'm assuming I have to first insert the bucket information

INSERT INTO Bucket (BucketName) VALUES (@BucketName)

then I would have to insert the data in the experiment table. But how would I get the bucket id from the bucket table when populating the experiment table? Would I just need to do a select statement? Any help would be appreciated.

Answer 1

An experiment can have many buckets (A-Z)

Taking that into consideration, your foreign key is not correct. You would like to have 1 experiment with N buckets. In this example, you are having 1 experiment with 1 bucket.

In order to fix this, you'd need to modify Experiment, and remove the BucketId column. In addition to that, you should add ExperimentId column to Bucket.

And of course, the foreign key constraint.

Once this is done, you will be able to have many buckets per experiment.

-

In order to add rows, you'd have to create the experiment first, and then as many buckets as you want.

Hope this helps.

Answer 2

If an experiment only has one bucket

Declare @bucketID Int;
INSERT INTO [Bucket] (BucketName) VALUES (@BucketName);
set @bucketID = (select SCOPE_IDENTITY());
insert into [Experiment] (bucketID) values (@bucketID);

But this has real problems as it creates a new bucket for each value without checking if the value exists.
What is the actual problem statement? If this is what you want then just put BucketName in Experiment with no separate table or FK.

If you want an ID and A (as in one) BucketName then:
For this should declare a unique constraint on BucketName

Declare @bucketID Int;
set @bucketID = (select bucketID from bucket where BucketName = @BucketName);
if @bucketID = null;
begin
  INSERT INTO [Bucket] (BucketName) VALUES (@BucketName);
  set @bucketID = (select SCOPE_IDENTITY());      
end
insert into [Experiment] (bucketID) values (@bucketID);

Below assumes a single experiment can have multiple buckets

In the current form it is not 3NF
You would have the problem of repeating data in Experiment and unique [ExperimentId] for each row.

You need a third table for the one to many relationship.

You need a 3rd table ExperimentBucket with a composite PK and FK reference to each table and remove BucketID from Experiment

CREATE TABLE [dbo].[ExperimentBucket] (
[BucketId]   INT,
[ExperimentId] INT 
CONSTRAINT [PK_BucketId] PRIMARY KEY CLUSTERED ([BucketId] ASC, [ExperimentId] ASC)
CONSTRAINT [FK_ExperimentBucket_Experiment] FOREIGN KEY (ExperimentId)
REFERENCES Experiment(ExperimentId)
CONSTRAINT [FK_ExperimentBucket_Bucket] FOREIGN KEY (BucketId)
REFERENCES Bucket(BucketId)
);

Answer 3

To begin, you have what we call a One-to-many foreign key relationship here: one experiment can have many buckets. In this case the way to set up the keys is that you have a foreign key in Bucket table which refers to the experiment ID. Since the experiment can have more than one bucket, there will be one row in the experiment table that corresponds to many rows in the bucket table.

CREATE TABLE [dbo].[Experiment] (
[ExperimentId] INT           IDENTITY (1, 1) NOT NULL,
[Experiment]   VARCHAR (100) NOT NULL,
[Account]      VARCHAR (100) NOT NULL,
[Profile]      VARCHAR (100) NOT NULL,
[Property]     VARCHAR (100) NOT NULL,
// No bucket id[BucketId]     INT           NULL,
CONSTRAINT [PK_Experiment] PRIMARY KEY CLUSTERED ([ExperimentId] ASC),
);

CREATE TABLE [dbo].[Bucket] (
[BucketId]   INT      IDENTITY (1, 1) NOT NULL,
[BucketName] CHAR (1) NOT NULL,
[ExperimentId] INT 
CONSTRAINT [PK_BucketId] PRIMARY KEY CLUSTERED ([BucketId] ASC)
CONSTRAINT [FK_ExperimentBucket] FOREIGN KEY (ExperimentId)
REFERENCES Experiment(ExperimentId)
);

Once you have that, then you will just Insert the Experiment

INSERT INTO Experiment VALUES (your experiment data)

and then find out what experiment ID you ended up with

SELECT @experimentid = top 1 ExperimentID from Experiments order by ExperimentID

and then insert the buckets with the Bucket ID

INSERT INTO Buckets VALUES (your data with @experimentid)

Answer 4

Yes, you will do a sub-select to get the BucketId from the Bucket table while inserting to Experiment.

INSERT
...
(SELECT TOP 1 BucketId FROM Bucket WHERE BucketName=@BucketName)
...