Relational dataset strategy?

  • Thread starter Thread starter David Veeneman
  • Start date Start date
D

David Veeneman

I'm trying to figure out how best to solve the following problem: I have a
database with two tables, Projects and Steps, that I want to read into a
dataset. Projects contain Steps, so I will create a data relation between
the two tables. Here's the twist: When I fill the dataset, I don't want to
retrieve all Projects. I only want the Projects that are owned by a
particular person, those that have a particular OwnerID value.

I can fill both tables into my dataset with no filtering, then create the
data relation I need. But if I filter the Projects table on its OwnerID
field when I read it from the database, then the Steps table will contain
Steps that don't correspond to Projects, which will cause a referential
integrity error (I think). So, that leaves me with a couple of options:

-- First, I could load the dataset without filtering, set the data relation,
then create a data view that filters the dataset on the Project table's
OwnerID field.

-- Or, I could add an OwnerID field to the Steps table in the database, so
that I can filter both tables on the OwnerID field when I read them from the
database.

Here is my question: Is one of these approaches clearly better than the
other? Or is there a third approach that I haven't thought of, which works
better than either of these options?

Thanks in advance for your help.

David Veeneman
Foresight Systems
 
David said:
Or, I could add an OwnerID field to the Steps table in the
database, so that I can filter both tables on the OwnerID field when I
read them from the database.
Click to expand...

Why? That would be very un-relational and un-normalized. :)
Or is there a third approach that I haven't thought of,
which works better than either of these options?
Click to expand...

I'd imagine your Steps already have the ProjectID as a foreign key. You can
very simply join your steps to Projects and filter results on
Projects.OwnerID. There's no need to duplicate anything.

Your query would look roughly like:

SELECT Steps.ID, Steps.Column1, Steps.Column2, Steps.AndSoForth
FROM Steps
JOIN Projects ON Projects.ID = Steps.ProjectID
WHERE Projects.OwnerID = @MyOwnerID

All you need to do is make sure you use the same criteria here as you did
for selecting from the parent table.
 
Back
Top