Saturday, February 25, 2012

Data manipulation with One-to-many-to-one relationship

Is it possible to INSERT, UPDATE, DELETE data in this type of relationship? If so, how in VS2005? Not having issues with SELECT, even without joins.

Thanks

Hi,

Try providing more information about what you are trying to do. Do you have tables set up in a DB already? What columns do you have in those tables, and what are the references that make a given row of data in each table unique (primary keys)?

If you don't have a schema yet, provide (in your own words), what facts you are trying to convey - such as 'A customer places an order. Order has line items. Each product has a product number.' etc... This will give viewers here a better understanding of what you need.

Good luck, in any event. BRN..

|||

Let's say for example:

Table 1 has Table1ID (PK) and Table1Data

Table 2 has Table2ID (PK), Table1ID (FK for Table1.Table1ID), Table3ID (FK for Table3.Table3ID)

Table 3 has Table3ID (PK), and Table3Data

Relationship is one-to-many for Table1.Table1ID to Table2.Table1ID and one-to-many for Table3.Tat.ble3ID to Table2.Table3ID, creating a many to many relationship for Table1 to Table 3 using Table 2 as an intersection table. I do not have any cascading.

I am able to query the database and gather the information, but need to be able to add, edit, and delete in a C# VS2005 project. Starting off small with something like this would enable me to conquer a bigger project that I would like to create. This is the first time I have had to work with data that required this type of relationship.

I'd prefer to see the SQL.

Is this enough information?

Many thanks

|||OK, let take these 3 tables for example:

create table Employee (EmplNumber int not null primary key,EmplName nvarchar(20) not null)

create table Department (DeptNumber int not null primary key,DeptName nvarchar(50) not null)

-- relationship between Employee and Department

create table DeptEmpl (DeptNumber int not null foreign key references Department(DeptNumber)
ON DELETE CASCADE ON UPDATE CASCADE,
EmplNumber int not null foreign key references Employee(EmplNumber)
ON DELETE CASCADE ON UPDATE CASCADE)

insert into Employee values (1, 'John')
insert into Employee values (2, 'Terry')
insert into Employee values (3, 'Andrew')

insert into Department values (1, 'Marketing')
insert into Department values (2, 'Sales')
insert into Department values (3, 'Accounting')

insert into DeptEmpl values (1, 1)
insert into DeptEmpl values (1, 3)
insert into DeptEmpl values (2, 1)
insert into DeptEmpl values (2, 2)
insert into DeptEmpl values (2, 3)
insert into DeptEmpl values (3, 1)

?
There should be no problem when you try to insert data-- the PK/FK contraint will maintain the reference integrity for you. The key point here is theON DELETE/UPDATE CASCADE, they will help to maintain reference integrity during DELETE/UPDATE, so no extra work need to be done manually. If you have other concerns, feel free to post themSmile|||

Thanks Iori_Jay

It is completely clear to me what you are doing in this example. How does one handle user input variables from a web application?

Let's say the user wants to insert a new Employee 'Edgar' that belongs to Department 'Sales'.

|||OK, typical steps for such INSERT should be:

1. Insert the information for the new employee 'Edgar' toEmployee table, and get theEmplNumber for the new inserted employee
2. Check theDepartment table to see whether there is a 'Sales' department, if not then add the 'Sales' department.
3. Insert a row toDeptEmpl table to represent the relationship between 'Edgar' and 'Sales'.

So a sample stored procedure looks like:

CREATE PROCEDURE usp_InsertNewEmployee @.EmpName nvarchar(20), @.DeptName nvarchar(50)
AS
IF((LEN(@.EmpName)=0) OR (LEN(@.DeptName)=0))
RAISERROR('Please input both Employee name and the department name which he(she) belongs to',16,1)
DECLARE @.newEmpNumber INT,@.DeptNumber INT
SELECT @.newEmpNumber=max(EmplNumber)+1 FROM Employee
SELECT @.DeptNumber=DeptNumber FROM Department WHERE DeptName=@.DeptName

INSERT INTO Employee SELECT @.newEmpNumber, @.EmpName
IF (@.DeptNumber IS NULL)
BEGIN
SELECT @.DeptNumber=max(DeptNumber)+1 FROM Department
INSERT INTO Department SELECT @.DeptNumber,@.DeptName
END
INSERT INTO DeptEmpl SELECT @.newEmpNumber,@.DeptNumber
go

EXEC usp_InsertNewEmployee 'Edgar','Sales'|||

Iori_jay,

Thanks so much for your help. I've learned many things from this post. I appreciate you sharing your talents.

ICE|8

|||It's my pleasure to help with this issueSmile Wish you a happy festival!

No comments:

Post a Comment