How to I insert records based on one to many relationship?

1

Greetings again experts,

I have four tables, Elections ,Positions, CandidatePositions, Candidates.

The following is the DDL:

Elections - Table
ElectionId int PK, auto increment
ElectionName nvarchar(50)
StartDate datetime
ClosingDate datetime

Positions - Table
PositionId int PK auto increment
ElectionId int (FK to Elections table)
Position nvarchar(50)  (example positions, President, VP, Secretary, etc)

Candidates - Table
CandidateID int PK auto increment
CandidateName nvarchar(50)
CurrentOfficeHolder nvarchar(50) (If current office holder, then value is incumbent, otherwise, null)

CandidatePosition - Table
CandidatePositionId int PK auto increment
CandidateId int (FK to Candidates table)
PositionId int (FK to to Positions table

I think the design concept is solid.

The way, we have set up the web design is that there is a dropdown list of all the Election names that stored on our database.

Then, an admin managing the back office would select election name, say Presidential Election, from this dynamically populated dropdown list of all Election Names that are stored on the DB.

Depending on the Admin's selection, we would like the positions associated with that election name to be inserted into the database, along with the election ID.

For instance, if a user selects Presidential Election from the dropdown list, we would like to insert into Positions table President, Vice President under the Position column name of the Positions table.

Any ideas how to Format the insert statement?

Example if we have Elections table with following values:
ElectionID ElectionName
1 Presidential Election

An who is creating an E-Ballot,
inserts the positions of President, Vice President associated with Presidential Election, when we do a join between Elections table and Positions table, we would like to see:

Position Id                     Election ID                Position
1                                      1                              President
2                                      1                              Vice President

I guess my question is do I have to perform two separate inserts statements, one for President and another for Vice President or is there a way to just perform one insert statement to get both values?

I hope I didn't confuse you good people and many thanks in advance

3

hi

hope this helps you

you can try a datawarehouse design

all the fields in a single table
insert / update as you see fit

simple query gets you all the information

please let me know if you need me to show you an example

thanks
:stuck_out_tongue_winking_eye:

4

if there are mistakes in the below
Please excuse me

Correct and Njoy :wink:

-- Create the ElectionDetails data warehouse table
CREATE TABLE ElectionDetails (
    ElectionId INT,
    ElectionName NVARCHAR(50),
    StartDate DATETIME,
    ClosingDate DATETIME,
    PositionId INT,
    Position NVARCHAR(50),
    CandidateId INT,
    CandidateName NVARCHAR(50),
    CurrentOfficeHolder NVARCHAR(50)
);

-- Insert sample data
INSERT INTO ElectionDetails (
    ElectionId, ElectionName, StartDate, ClosingDate,
    PositionId, Position, CandidateId, CandidateName, CurrentOfficeHolder
) VALUES
    (1, 'Presidential Election', '2024-10-01 08:00:00', '2024-11-05 17:00:00', 1, 'President', 101, 'John Smith', 'Incumbent'),
    (1, 'Presidential Election', '2024-10-01 08:00:00', '2024-11-05 17:00:00', 1, 'President', 102, 'Alice Johnson', NULL),
    (1, 'Presidential Election', '2024-10-01 08:00:00', '2024-11-05 17:00:00', 2, 'Vice President', 103, 'Mark Davis', NULL),
    (1, 'Presidential Election', '2024-10-01 08:00:00', '2024-11-05 17:00:00', 2, 'Vice President', 104, 'Linda Garcia', 'Incumbent');

image
image958×144 57.8 KB

5

@harishgg1, thanks so much for your responses.

The first approach is my preferred approach.

The only issue is we don't want to the values hardcoded.

If that were to be the case, I would have handled it without coming to you guys for your help.

We are trying to figure out a way to select ElectionID, and we are presented with the options to insert President, Vice President and any other position associated with the election ID and when we click insert, these values are stored under the Position column name and will display just like your example.

6

If you are creating the Election insert first and then adding the other details to the child table, you can use @@Identity to get the ElectionID (if it is the key) from the Elections table in order to insert to the child table. If multiple users are entering data at the same time you probably should use Scope_Identity() instead to get the value as @@Identity will get the last created record ID and could be from someone else's insert if the timing is not spot on.

7

@boblarson, thanks for the suggestion but my number one issue though is how to insert multiple values under the same column name.

I gave an example of what we are actually using and that is a column name called Positions.

While I can easily get the ElectionID that is currently being selected by the user, and I can insert that into the Positions table, how do I insert President, Vice President as values for Position so that when we review data on Postions table, we get something like this:

Positions - Table
PositionID ElectionID Position
1 1 President
2 1 Vice President

Notice that on the Positions table, we have President (PositionID #1 and Vice President PositionID #2)

I guess my question is do I Insert electionID and President, save to the database and then insert electionID and Vice President) separately?

I hope my concern is explained better.

Thank you very much

8

Yes, since the data is different you would need to do an insert for each position. Unless it is stored elsewhere (another table) and then you could use a query to insert both. But if it is coming from a user interface then it really would be separate insert queries.