Question on Implementing a PARTY Entity

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Question on Implementing a PARTY Entity

Author

Topic

bcox
Starting Member

6 Posts

Posted - 2004-09-10 : 16:49:01

I am writing a complex Multi-Tier Web application using ASP.NET, C#, SQL Server. I have a presentation layer (ASPX files), a Business Layer (C# objects) an abstract data layer (C# Objects) that calls SQL Server Stored Procedures to obtain data. I want to implement the PARTY entiy concept in my database schema and I want to know the Best Practices way to do it and protect it.

The PARTY Entity is used to Abstact common relationships between disimalar Entities like ORGANIZATION and PERSON. So a PERSON IS A PARTY, and an ORGANIZATION IS A PARTY for example.

To do this I need to have the following key structure in the tables:

PARTY.PARTY_ID PK
PERSON.PARTY_ID PK
ORGANIZAION.PARTY_ID PK

To add new PERSON or ORGANIZATION records I have to first obtain an auto-generated ID from the PARTY table and use it for the Key in the PERSON or ORGANIZATION table.

I want to make sure the INSERT mechanism is Bomb-Proofed (easy access) for both backend direct access to the PERSON and ORGANIZATION tables and also to allow INSERTS to be wrapped by objects that call Stored Proceedures.

It seems that I need to use a tranaction to ensure that I get a unique ID from the PARTY table first and then insert it into the ORGANIZATION or PERSON table. Am I protected from concurancy issues since I'm operating from an disconnectd Web Applciation? Is that all there is to it, or is there something I'm missing?

What is the best way to accomplish this type of thing for Web applicaitons? I can also create transactions in ADO.NET. Is it better to do it there or in the database?

I'm confused about how best to do this...

Thanks...

Bruce D. Cox, A.I.A.
Workplace |FMStrategies| Director, Facilities Technology
Little Diversified Architectural Consulting
5815 Westpark Drive| Charlotte, NC 28217
D:704.561.7418| M:704.604.0077 | F:704.561.7425
www.littleonline.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-10 : 17:26:56

well datasets have a cool feature of doing the insert keys automatically. if you add a relation between your tables in the dataset it will use referential integrity to handle correct id's in parent-child relationship. no actual need for transactions. you can implement them though

on concurency take a look a this recent topic:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=39669

Go with the flow & have fun! Else fight the flow

bcox
Starting Member

6 Posts

Posted - 2004-09-11 : 08:32:14

Thanks Spirit1.

Do you know of a good code example for the dataset approach?

What I'm realy curious about is if I could implement this in the database and simply use an object to call a stored procedure.

I would have a method call something simple like AddPerson(firstname, lastname) that would in turn call a stored procedure in the database that would create the PARTYID, capture the PARTYID, use it with the passed in parameters to create the PERSON.PARTYID record and return the PERSON.PARTYID to the calling object.

I'm not great with SQL Server. But could you do it this way, and if so what are the downsides if any to the following?

1. Create a parameter based stored procedure that somehow wraps a SQL Server Transaction.
2. Simply call the stored procedure to insert a new PERSON and Return its PARTYID as a key?

This approach would make the object side of the picture simpler it seems and would encapsulate the login in a single stored procedure possibly. Would this work? Is it even possible? Is is a good idea, or a bad idea?

One note is that in the future we will probably need to connect this application to Oracle as well as SQL Server so that may have a bearing on the best solution...

Thanks for your help..

Bruce D. Cox, A.I.A.
Workplace |FMStrategies| Director, Facilities Technology
Little Diversified Architectural Consulting
5815 Westpark Drive| Charlotte, NC 28217
D:704.561.7418| M:704.604.0077 | F:704.561.7425
www.littleonline.com

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-11 : 13:39:43

Oracle's handling of IDENTITY columns differ from Sql Server ( at least in v8 that I have used ).
They have a concept of a Sequence that is assigned to a table, You might want to check out the implications (if any) beforehand as you are considering oracle for the app as well.
I would wrap it all in a stored procedures that handles the insert into both the PARTY and the PARTYSUBENTITY, just as you outlined in 1. & 2.
One proc for each subentity.

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-11 : 14:28:56

it might be a good idea to write two db acces layers (or one with dual functionality) because C# has SqlClient namespace that is optimised for SQL. Oracle on the other hand is not so supported in that manner. If performance is a big thing in your app, it may be good to do it this way.

for examples msdn is ok. search google.

i usually do it this way.


SqlDataAdapter da = new SqlDataAdapter();
			
// same thing for InsertCommand, UpdateCommand and DeleteCommand
da.SelectCommand = new SqlCommand();
da.SelectCommand.Connection = new SqlConnection("your connection string");
da.SelectCommand.CommandText = "spSelectData"; // sproc
da.SelectCommand.CommandType = CommandType.StoredProcedure;
						
DataSet ds = new DataSet();
da.Fill(ds);
ds.Tables[0].TableName = "ParentTable";
ds.Tables[1].TableName = "ChildTable";
ds.Relations.Add("relation1", ds.Tables[0].Columns[0], ds.Tables[1].Columns[0], true);

DataRow newParentRow = ds.Tables[0].NewRow();
DataRow newChildRow = ds.Tables[1].NewRow();
		
newChildRow.SetParentRow(newParentRow, ds.Relations[0]);
// update dataSet
da.Update(ds);

Go with the flow & have fun! Else fight the flow

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-09-11 : 17:00:46

This is a job for views. Create views for each "sub" party that reflect the rules you outlined, and then simply insert into the view.

AFAIK both Oracle and SQL Server have "INSTEAD OF" functonaility on Views.

Check this out for an example..
[url]http://weblogs.sqlteam.com/davidm/archive/2003/11/28/623.aspx[/url]

DavidM

"Always pre-heat the oven"

bcox
Starting Member

6 Posts

Posted - 2004-09-12 : 08:12:53

Thanks guys... The solution suggested by byrmol looks very interesting. This is more of what I was looking for. I was not aware of the "INSTEAD OF" trick.

This might actually work for me. The artical you referenced addresses the problem of Bulk Loading as well witch is also one of my issues. When someone trys INSERT records directly from the backend I need to protect the process.

I need to do a bit more research on the "VIEW" idea, but it looks good.

Thanks Guys for your help...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources