Parent/Child relationships, relating to ADO and SQL Server

[John Hoge]

I'm working on a back end for an ecommerce system that has two tables
that both need data in a one to one or more relationship.

The product table must have at least one corresponding record in the
package table, but could have more as some products some in several
packages. Of course, each package needs to have a valid product_id. So
here's the chicken and egg problem - If I enfoce a foriegn key
relationship between pacakge and product, I won't be able to enter a
package without a corresponding item in the product table.

In this case, an entry of a product can be made without a
corresponding weight(which requires a valid product ID first). The
less-than-ideal system I have implemented is to enter products with an
ACTIVE flag set to FALSE. The middle-tier ASP logic prevents the
ACTIVE flag of a product being set to TRUE unless there is a
corresponding entry in the package table.

What I'd really like to do is input the parent and child records in
one operation, and lock down the database to make sure that there
could be, in no circumstances a product without a package table, or a
package entry without a valid product_id.

Thanks,

John

[Bob Barrows]

John Hoge wrote:

What type and version of database are you using?
This is always relevant!
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob Barrows]

John Hoge wrote:

> I'm working on a back end for an ecommerce system that has two tables
> that both need data in a one to one or more relationship.
>
> The product table must have at least one corresponding record in the
> package table, but could have more as some products some in several
> packages. Of course, each package needs to have a valid product_id. So
> here's the chicken and egg problem - If I enfoce a foriegn key
> relationship between pacakge and product, I won't be able to enter a
> package without a corresponding item in the product table.
>
> In this case, an entry of a product can be made without a
> corresponding weight(which requires a valid product ID first). The
> less-than-ideal system I have implemented is to enter products with an
> ACTIVE flag set to FALSE. The middle-tier ASP logic prevents the
> ACTIVE flag of a product being set to TRUE unless there is a
> corresponding entry in the package table.
>
> What I'd really like to do is input the parent and child records in
> one operation, and lock down the database to make sure that there
> could be, in no circumstances a product without a package table, or a
> package entry without a valid product_id.
>

Oh wait. It says "SQL Server" in the subject. Disregard the last post.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob Barrows]

John Hoge wrote:

> I'm working on a back end for an ecommerce system that has two tables
> that both need data in a one to one or more relationship.
>
> The product table must have at least one corresponding record in the
> package table, but could have more as some products some in several
> packages. Of course, each package needs to have a valid product_id. So
> here's the chicken and egg problem - If I enfoce a foriegn key
> relationship between pacakge and product, I won't be able to enter a
> package without a corresponding item in the product table.

I don't get it - this is a typical one-to-many relationship isn't it? What's
the difference between this and the archetypal header-detail relationship? A
header can have zero-to many related detail records? How is this different
from your scenario?

>
> In this case, an entry of a product can be made without a
> corresponding weight(which requires a valid product ID first).

What do you mean by "weight"?

Perhaps you should show us the table structures so we know what you are
talking about. Why not generate some CREATE TABLE scripts for your tables,
delete the irrelevant column definitions, and paste the scripts into your
reply to this?

>The
> less-than-ideal system I have implemented is to enter products with an
> ACTIVE flag set to FALSE. The middle-tier ASP logic prevents the
> ACTIVE flag of a product being set to TRUE unless there is a
> corresponding entry in the package table.

Ughh! This seems totally unnecessary and overly complex.

>
> What I'd really like to do is input the parent and child records in
> one operation,

You can and should do this using a stored procedure.

> and lock down the database to make sure that there
> could be, in no circumstances a product without a package table, or a
> package entry without a valid product_id.
>

Again, show us the table structures.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob Barrows]

Roland Hall wrote:

> "Bob Barrows" wrote in message
> news:uDefeDlJEHA.3628@TK2MSFTNGP12.phx.gbl...

>> John Hoge wrote:

>>> In this case, an entry of a product can be made without a
>>> corresponding weight(which requires a valid product ID first).

>>
>> What do you mean by "weight"?

>
> Hey Bob...
>
> I think he is referring to shipping weight which you have to have if
> there is a product involved that has to be shipped to the customer so
> you can perform a zone and rate lookup for the carrier being used.
>
> HTH...

So you're saying that you understand where this weight is stored ... ?
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Roland Hall]

"Bob Barrows" wrote in message news:uDefeDlJEHA.3628@TK2MSFTNGP12.phx.gbl...
: John Hoge wrote:
: > In this case, an entry of a product can be made without a
: > corresponding weight(which requires a valid product ID first).
:
: What do you mean by "weight"?

Hey Bob...

I think he is referring to shipping weight which you have to have if there
is a product involved that has to be shipped to the customer so you can
perform a zone and rate lookup for the carrier being used.

HTH...

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - [url]http://www.microsoft.com/technet/scriptcenter/[/url]
WSH 5.6 Documentation - [url]http://msdn.microsoft.com/downloads/list/webdev.asp[/url]
MSDN Library - [url]http://msdn.microsoft.com/library/default.asp[/url]

[John Hoge]

Bob,

The essential bits of the two tables are as follows:

Create table Product(
ID int not null primary key,
....other fields)

Create table Packages(
Product_id int not null references product(ID),
Weight int not null)

In SQL Server 7, my goal is to make sure that each product has one or
more package records at all times, and to do this totally at the
database level.

I could use a sproc to create the product and then the package record,
but that would not prevent the package from being deleted later. Not
that that is likely, but I'm looking to make this bulletproof. I could
use a delete trigger on the package table to stop package records from
being deleted when that would leave a product with no packages. That
would work, but I wouldn't be able to delete a product if I wanted to,
because the FK relationship prevents Product records being deleted if
Package records exist.

I know that SQL2K has cascading deletes that work with relationships,
but I'm a bit hesitant to add a few thousand hard earned bucks to Bill
Gates' fortune. Any way I can do this with good old SQL 7?

John

[Bob Barrows]

John Hoge wrote:

> Bob,
>
> The essential bits of the two tables are as follows:
>
> Create table Product(
> ID int not null primary key,

It's not IDENTITY? How are you generating the ID's. Are you using a key
generation table?

> ....other fields)
>
> Create table Packages(
> Product_id int not null references product(ID),
> Weight int not null)

I'm surprised there's no package_id, but you may have just left it out of
your description ...

>
> In SQL Server 7, my goal is to make sure that each product has one or
> more package records at all times, and to do this totally at the
> database level.
>
> I could use a sproc to create the product and then the package record,
> but that would not prevent the package from being deleted later. Not
> that that is likely, but I'm looking to make this bulletproof. I could
> use a delete trigger on the package table to stop package records from
> being deleted when that would leave a product with no packages. That
> would work, but I wouldn't be able to delete a product if I wanted to,
> because the FK relationship prevents Product records being deleted if
> Package records exist.
>

OK, that's clearer.

Have you considered using "soft" deletes? I.E., using a flag in the product
and package rows to indicate their active/inactive status? This would allow
you to use a Delete trigger to prevent deletions from the packages table,
and an Update trigger to prevent deactivating the last package for an active
product. On the product table, you would need an Update trigger to
deactivate the relevant package rows after deactivating the product row. A
monthly job could be used to disable the triggers and delete the inactive
records from each table (deleting the package records first to avoid the
need to disable the foreign key constraint).

If the above does not appeal to you, then I think the first step is to
remove all permissions from the tables themselves and only grant permissions
to use the stored procedures that will maintain the data in the tables. No
more dynamic sql or recordsets for data maintenance.

True, this will not prevent the sa or sysadmin account from mucking things
up but in actuality, nothing will.

You will need a stored procedure for adding a product/package combination.

Another for adding packages for existing products (although this could be
combined with the first: if you pass a product_id of -1, say, you could
cause it to add a new product, using the new ID to create a package row - if
you pass a product_id > 0, then create only the package row)

Another for updating the product information as well as one for updating the
package info

The stored procedure that will be used to delete packages will need to
disable the foreign key constraint before deleting the last package for a
product so the procuct can be deleted as well.

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Roland Hall]

"Bob Barrows" wrote in message news:e33VEtmJEHA.2880@TK2MSFTNGP10.phx.gbl...
: Roland Hall wrote:
: > "Bob Barrows" wrote in message
: > news:uDefeDlJEHA.3628@TK2MSFTNGP12.phx.gbl...
: >> John Hoge wrote:
: >>> In this case, an entry of a product can be made without a
: >>> corresponding weight(which requires a valid product ID first).
: >>
: >> What do you mean by "weight"?
: >
: > Hey Bob...
: >
: > I think he is referring to shipping weight which you have to have if
: > there is a product involved that has to be shipped to the customer so
: > you can perform a zone and rate lookup for the carrier being used.
: >
: > HTH...
:
: So you're saying that you understand where this weight is stored ... ?

Nope, just what weight means.

[Bob Barrows [MVP]]

Roland Hall wrote:

> "Bob Barrows" wrote in message

>>>> What do you mean by "weight"?
>>>
>>> Hey Bob...
>>>
>>> I think he is referring to shipping weight which you have to have if
>>> there is a product involved that has to be shipped to the customer
>>> so you can perform a zone and rate lookup for the carrier being
>>> used.
>>>
>>> HTH...

>>
>> So you're saying ... you understand where this weight is stored ...?

>
> Nope, just what weight means.

OK - that's reasonable.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[John Hoge]

Bob,

A "Soft Delete" is basically what I have now through the middle tier
code, but your solution is definitely cleaner. I agree that it is
better to use database code to ensure database organization.

Thanks for your help.

Thanks