468,458 Members | 1,912 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,458 developers. It's quick & easy.

Retrieving child objects, and when

Hi everyone, sorry if this post gets really long, i just wanna make
sure i fully explain what i am trying to do. I am new to OOP and .net
2.0, so if this is obvious, i am sorry.

I have wrote a data access layer, which contists of separate
servicebases for each object within my database.

CompanyDataService (contains CRUD methods)
ContactDataService (contains CRUD methods)

Q1, when i retrieve data for each object (company, contacts etc) i
return a dataset, should i return a datatable??

Q2, in the contact table i have a foreign key called CompanyID, as
contacts are linked to a company. If i was to retrieve a contact from
the database using my retrieve function within my ContactDataService,
how would i obtain the CompanyName that the contact is linked to?? Do
i put a call to the company retrieve method, within my contact retrieve
method??

Q3, if i create the business object for both company and contacts,
should i write the contact object as containing a company object, or
just a companyID field???

i.e.
Contact
ID
Name
CompanyID

or

Contact
ID
Name
Company (i can then access Company ID by typing Contact.Company.ID)

Note, that companies are separate objects and do not have to be linked
to any contacts in the database.

I would be greatful for a lil help, this question has been bugging me
for days. If you want to know more, please let me know.

Sep 27 '06 #1
11 1714
Hi,

<snip>
I have wrote a data access layer, which contists of separate
servicebases for each object within my database.

CompanyDataService (contains CRUD methods)
ContactDataService (contains CRUD methods)

Q1, when i retrieve data for each object (company, contacts etc) i
return a dataset, should i return a datatable??
Return a DataSet if you need to reference multiple, related DataTables
Return a DataTable if you only need to work with one set of data (which may or may not have associations in its parent DataSet)
Return a DataRow if you are only representing one entity (which may or may not have associations in its parent DataSet)

You'll probably want to create a strong-typed DataSet that contains at least a Company table and a Contact table with the
appropriate association defined.

The ContactDataService, for instance, could either return the data to a business object or it can act as a business object factory,
in which case it would return a business object or an array of business objects for each method instead of returning the underlying
data. I prefer using the service to retrieve data and have a business object, e.g. Contact, make the calls to the
ContactDataService when necessary. By seperating the data access layer from the business layer you'll find that the application
scales better, and is: easier to maintain, modify, debug (and understand IMO).
Q2, in the contact table i have a foreign key called CompanyID, as
contacts are linked to a company. If i was to retrieve a contact from
the database using my retrieve function within my ContactDataService,
how would i obtain the CompanyName that the contact is linked to?? Do
i put a call to the company retrieve method, within my contact retrieve
method??
(I addressed this somewhat in a response to a related post of yours in this newsgroup yesterday. If you have any questions about my
response I'd be glad to answer them for you. Just post a reply to the other thread.)

If you always require the company name along with the contact information then you should add a CompanyName field to your
strong-typed, Contact DataTable and to your Contact business object. In the ContactDataServer method that fills and returns a
Contact object, such as "GetContactByID", you have several options to query the database. IMO, your best option is to create a
stored procedure that selects from a View (SQL Server allows you to create Views of data) that aggregates all of the data that you
need for each contact and filters it by the specified ContactID. The result set, because of the View, will include the CompanyName
field (accomplished through a JOIN in the View's SELECT statement).

If you only need the company name some of the time, then you could have two Views of the data, two stored procedures (or one stored
procedure with a BIT parameter, such as "SummaryDataOnly"), and two methods on your ContactDataServer object such as,
"GetContactByID" and "GetContactSummaryByID", the latter only returning information from the Contact table in the database. I
actually wouldn't even recommend doing this unless there is some reason that it's required and would recommend instead that you
always retrieve CompanyName with contact information if you need it even once in your application. Don't retrieve CompanyName with
Contact information every time if the process proves to have a substantial impact on performance, or creates some sort of security
risk.
Q3, if i create the business object for both company and contacts,
should i write the contact object as containing a company object, or
just a companyID field???
(I believe that I answered this already in a related thread of yours in this newsgroup, yesterday. Again, if you have an questions
about my response just post a reply on that thread and I'll be glad to answer them.)

<snip>

HTH

--
Dave Sexton
Sep 27 '06 #2
Dave,

This has made things much clearer, thank you. I will give what you
said ago now and see how it goes.

I think i will go for the option of retrieving the companyName
everytime, when retrieving a contact record.

Thanks again

Sep 27 '06 #3
Glad to help :)

--
Dave Sexton

"Nemisis" <da*********@hotmail.comwrote in message news:11**********************@d34g2000cwd.googlegr oups.com...
Dave,

This has made things much clearer, thank you. I will give what you
said ago now and see how it goes.

I think i will go for the option of retrieving the companyName
everytime, when retrieving a contact record.

Thanks again

Sep 27 '06 #4
Dave,

Thanks again, i have converted my datalayer to return the data from the
database, using datarows and datatables instead of datasets.

On the same subject, u maybe able to help with the following.

In my project i have other objects that are linked to companys and
contacts, as well as the company - contact (one to many)relationship.
When i delete a company i would also like to delete all the child
objects as well. So if a call CompanyFactory.Delete(ID), that function
will inturn delete all contact records, and any other related data, as
well as the data linked to the child items being deleted.

Is this a good idea?

In order to do this, when in the company delete method i would have to
find all the contacts and then call there delete method, which in turn,
that contact delete method would have to call all its children object
delete methods? Am i looking at this the wrong way?

Sep 27 '06 #5
Hi,

<snip>
In my project i have other objects that are linked to companys and
contacts, as well as the company - contact (one to many)relationship.
When i delete a company i would also like to delete all the child
objects as well. So if a call CompanyFactory.Delete(ID), that function
will inturn delete all contact records, and any other related data, as
well as the data linked to the child items being deleted.

Is this a good idea?
Sure is. If you didn't automate the process you'd have to code for all posibilities. A single change to the data model might cause
a lot of work on your part trying to ensure the referential integrity of your data when parent rows are updated or deleted.
In order to do this, when in the company delete method i would have to
find all the contacts and then call there delete method, which in turn,
that contact delete method would have to call all its children object
delete methods? Am i looking at this the wrong way?
Well, you haven't mentioned what type of RDBMS you are using. Assuming that you're using SQL Server, you can configure the
relationships between tables to use cascading updates and deletes. Review the SQL Server docs for more info. This won't require
any modifications to your code, but you must be aware of this functionality when you code the data layer. If deleting a parent
automatically deletes a child you'll want to make sure that you don't try to update or delete the child after deleting the parent.
You can set the foreign key constraints in your DataSet to use cascading updates and deletes as well.

HTH

--
Dave Sexton
Sep 27 '06 #6
Well, you haven't mentioned what type of RDBMS you are using. Assuming that you're using SQL Server, you can configure the
relationships between tables to use cascading updates and deletes. Review the SQL Server docs for more info. This won't require
any modifications to your code, but you must be aware of this functionality when you code the data layer. If deleting a parent
automatically deletes a child you'll want to make sure that you don't try to update or delete the child after deleting the parent.
You can set the foreign key constraints in your DataSet to use cascading updates and deletes as well.
I have got an SQL database as my backend and have looked at using
cascade deletes, but have some problems with some tables and circular
references, so dont think this is a possibility, so i was looking at
doing this in code.

Sep 27 '06 #7
Well, you haven't mentioned what type of RDBMS you are using. Assuming that you're using SQL Server, you can configure the
relationships between tables to use cascading updates and deletes. Review the SQL Server docs for more info. This won't require
any modifications to your code, but you must be aware of this functionality when you code the data layer. If deleting a parent
automatically deletes a child you'll want to make sure that you don't try to update or delete the child after deleting the parent.
You can set the foreign key constraints in your DataSet to use cascading updates and deletes as well.
I have got an SQL database as my backend and have looked at using
cascade deletes, but have some problems with some tables and circular
references or something, so that isnt a possibility, so i was looking
at doing this in code.

Sep 27 '06 #8
Hi,

You have to analyze the relationships and figure out the appropriate cascade paths. Look closer and you'll probably find a solution
that meets your needs. Don't just try to cascade everything, everywhere.

--
Dave Sexton

"Nemisis" <da*********@hotmail.comwrote in message news:11**********************@i3g2000cwc.googlegro ups.com...
>Well, you haven't mentioned what type of RDBMS you are using. Assuming that you're using SQL Server, you can configure the
relationships between tables to use cascading updates and deletes. Review the SQL Server docs for more info. This won't require
any modifications to your code, but you must be aware of this functionality when you code the data layer. If deleting a parent
automatically deletes a child you'll want to make sure that you don't try to update or delete the child after deleting the
parent.
You can set the foreign key constraints in your DataSet to use cascading updates and deletes as well.

I have got an SQL database as my backend and have looked at using
cascade deletes, but have some problems with some tables and circular
references, so dont think this is a possibility, so i was looking at
doing this in code.

Sep 27 '06 #9
Dave Sexton wrote:
Hi,

You have to analyze the relationships and figure out the appropriate cascade paths. Look closer and you'll probably find a solution
that meets your needs. Don't just try to cascade everything, everywhere.
Dave,
The reason the cascade deletes etc dont work, is because i have foreign
key of a company in one table, and also 2 foreign keys in the same
table for contacts. In our application, we know that the 2 contacts
can only be contacts within the company selected, but you cant specify
this in SQL, that is why it wont let us do the cascade deletes.

You said not to try and cascade everything? Why? I thought this was
the point? I would still like to delete everything via the business
layer, but if not i suppose i will have to look at possible triggers in
the database as a last resort.

Sep 28 '06 #10

Dave Sexton wrote:
Hi,

You have to analyze the relationships and figure out the appropriate cascade paths. Look closer and you'll probably find a solution
that meets your needs. Don't just try to cascade everything, everywhere.
Dave,

I got it wrong, the reason we cant use cascade deletes is because in
some of our tables, we have a foreign key for CompanyID and multiple
ContactID foreign keys. In our application we know, that the contactID
will only ever be contacts within the CompanyID selected, but SQL
doesnt like this, and throws an error when trying to do a cascade
delete.

If i wanted to perform the deletions in code, is it best to put this
into the DAL or BLL?? For some reason i would say the BLL, but i am
unsure if this is correct. If i did do it in the DAL, then i would
have to reference other DataServices from within another DataService,
is this ok?

An example would be, if i was in the CompanyDataService, would it be ok
to make a call to the ContactDataService method DeleteByCompanyID??
Doing this i could perform a cascade delete, would this be advisable??

Sep 28 '06 #11
Hi,

I hope I didn't go over the top with this explanation :) On the other side, let me know if anything is unclear.
I got it wrong, the reason we cant use cascade deletes is because in
some of our tables, we have a foreign key for CompanyID and multiple
ContactID foreign keys. In our application we know, that the contactID
will only ever be contacts within the CompanyID selected, but SQL
doesnt like this, and throws an error when trying to do a cascade
delete.
What I meant by saying, "Don't just try to cascade everything, everywhere" is that developers tend to create a database that can
cascade as much as possible across every table because they feel that it will save them time later. I think that it just ends up
taking more time later to fix it :)

In other words, you probably don't need every possible cascade path in your database. If you are coding into your program the
ability for users, through the GUI or some other mechanism, to delete contacts then cascade from the Contacts table. If you need to
delete companies then cascade from the Company table.

If you absolutely need the ability to delete contacts and companies, independantly, then you'll have to analyze all of the possible
cascade paths:

The tables you've mentioned, but left nameless so I'll call one of them TableX, creates a many-to-many relationship and that always
seems to be the source of grief when trying to setup cascade paths. When you went through the tables and setup cascade paths you
must have created a cascade path from the Company table and the Contact table to TableX. At first this seems ok because it's
logical that if you delete either parent record that you'll want to delete the related records in TableX otherwise the referential
integrity of your data will be comprimised.

You've mentioned that you are also relating contacts to companies by a foreign key in contacts (IIRC from previous posts), which is
part of the circular refrence. Assuming that you have a foreign key in the contacts table named, "CompanyID", and that you have two
foreign keys in TableX named, "ContactID" and "CompanyID", then the following is true:

1. If you delete a record from Companies then you'll want all records in TableX to be deleted where TableX.CompanyID equals the
deleted Companies.CompanyID.
2. If you delete a record from Companies then you'll want all records from Contacts to be deleted where Contacts.CompanyID equals
the deleted Companies.CompanyID.
3. If you delete a record from Contacts then you'll want all records in TableX to be deleted where TableX.ContactID equals the
deleted Contacts.ContactID.

In this scenerio there is one choice that is better than the other in terms of the cascade paths that you can use. At first glance
you'd probably want to setup a cascade path on Companies to save yourself some coding since it acts as a primary key to more foreign
references than contacts, in this example. However, there are problems with that choice which I'll explain in a bit. A better
choice would be to CASCADE on TableX from Contacts and leave the foreign key reference of TableX.Company as NO ACTION. Now, when
you delete a contact from the database it will automatically delete all related records in TableX (solves #3).

However, if you try to delete a Company you'll get an error from SQL Server saying that the delete statement conflicts with foreign
key constraints in the Contacts table and TableX (although the error will probably only cite one table).

You can set the foreign key in Contacts to CASCADE on delete so that when you delete a Company it will automatically delete all
related contacts (solves #2). Unfortunately, you can't setup a cascading delete path on TableX to the Company table because that
would cause a circular reference since you already setup a cascading delete path on TableX from Contacts and Contacts references the
Company table. You can only have one path to TableX or the other. To solve #1 I recommend using an INSTEAD OF DELETE trigger on
the Companies table to maintain the referential integrity of your database. In the trigger delete all related records from TableX
and then delete the record in the Company table:

-- INSTEAD OF DELETE trigger on Company table:
DELETE FROM TableX WHERE CompanyID IN (SELECT CompanyID FROM deleted)
DELETE FROM Company WHERE CompanyID IN (SELECT CompanyID FROM deleted)

So why can't you cascade deletes from the Company table to TableX instead of from Contacts? Well, if you try to setup an INSTEAD OF
DELETE trigger on the Contacts table you'd find out that SQL Server won't let you because Contacts contains a foreign key reference
with on delete CASCADE from the Company table (used to solve #2). If you say, "well then I'll just remove that cascade path", you
actually end up creating more work for yourself. Here's what the trigger would look like:

-- INSTEAD OF DELETE trigger on Contacts table:
DELETE FROM TableX WHERE ContactID IN (SELECT ContactID FROM deleted)
DELETE FROM Contacts WHERE ContactID IN (SELECT ContactID FROM deleted)

But now, when you try to delete a record from the Company table SQL Server won't automatically delete all related records from the
Contacts table, since you removed cascading deletes in order to add the trigger, so you get an error saying that the delete
statement conflicts with the foreign key constraint in the Contacts table. Solution? Create a trigger on the Company table!

Hopefully it's clear that you can simply eliminate any need for a trigger on the Contacts table by handling cascading deletes from
the Company table to TableX through an INSTEAD OF DELETE trigger on the Company table, since either cascade path to TableX that you
choose will require a trigger on the Company table. (Albeit the body of the Company trigger will differ depending on the cascade
path you choose to TableX, but the trigger itself is still required by both)
Another scenerio that you've probably seen is the following: Let's say that, for instance, you have a Users table that has related
rows in other tables by an "UpdateUserID" column, for example, that keeps track of the last user that performs an update on any
given row. You might be tempted to create DELETE cascade paths from your Users table to every other related table, however it
wouldn't make sense to even delete users in the first place because their references will still be required to maintain the
historical records and relationships within the database. For instance, if you have a Company record with an UpdateUserID of 1 and
delete the User that has a UserID of 1, you wouldn't want the Company to be deleted as well but if you delete the user you'll have
to delete all related companies to preserve referential integrity. This is why it's better IMO to just add a bit flag to the Users
table and disable them if they are no longer needed by the application. It's important not to define cascade paths from the user
table so that SQL Server will raise an error if you try to delete a user that is being referenced by the Company table and so that
you don't inadvertantly delete required records.

I hope now you see that you must analyze all cascade paths in your database and find the ones that are appropriate for your
application. It's not necessarily all or none. Define the cascade paths that you will absolutely need so that you won't have to
explicitly enforce those relationships in code, and supplement the paths that SQL Server won't handle due to circular references by
creating INSTEAD OF triggers.
If i wanted to perform the deletions in code, is it best to put this
into the DAL or BLL?? For some reason i would say the BLL, but i am
unsure if this is correct. If i did do it in the DAL, then i would
have to reference other DataServices from within another DataService,
is this ok?
IMO it's best to enforce cascading in the RDBMS.
An example would be, if i was in the CompanyDataService, would it be ok
to make a call to the ContactDataService method DeleteByCompanyID??
Doing this i could perform a cascade delete, would this be advisable??
I wouldn't recommend it because it does break encapsulation, but that is just one perspective. Another perspective is that it would
be encapsulating the functionality required to delete all related records and it just so happens that it must rely on another
service to do its job. I don't like this perspective, however, because it seems to ignore the complexities that might arise if you
tried to enforce the relationships in your services that are already enforced by your RDBMS. I would say, let the database cascade
deletes and in a service just focus on deleting from the table of which that particular service is "aware". Your strong-typed
DataSets can also help to enforce table relationships in your services without having to explicitly code them.

--
Dave Sexton
Sep 28 '06 #12

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Mike S | last post: by
2 posts views Thread by Robert Strickland | last post: by
1 post views Thread by Glenn T. Kitchen | last post: by
5 posts views Thread by PAUL | last post: by
2 posts views Thread by Nemisis | last post: by
3 posts views Thread by Adam Right | last post: by
3 posts views Thread by Maximiliano | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kmladenovski | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.