help me with a bit of a practice issue...

Ok.

I recently started developing a web site for a client using storefront.net and ms sql server.

the db schema of storefront.net has autonumbers as the PKs for the products table (even though the products table contains an additional field for product_number.)

So here's my dilemma if you care to read:

I typically develop local, deploy remote (after testing). I have a local SQL server, and then the remote SQL server.

When I'm developing for this project, I'll insert data such as products to the products table (sometimes several times while i'm working out routines to import data to the products table.) this has the effect of creating a unique ID for each product based upon SQL auto-incrementing INTs.

This StoreFront.net (SF.NET) has another table that is a lookup table. For each part number, it has a corresponding categoryID number.

Now, if i have product_ID 1234, and I set the category ID to say 10 and get it working on my local box, every thing is fine.

Here's where the problem comes in: When I use DTS to transfer the database during remote deployment, each product is inserted into the remote DBs products table and gets a NEW product id. Same with the categories.

This has the effect of breaking the relationships. (SF.NET has no ref integrity nor relationships defined in the db.) let's say my product_id 1234 gets put into the remote copy, it'll get a new product_ID (PK). let's say it's now 5775. now my category ID will also get a new value. so my data is now not related.

I don't know how to handle this situation. The unique IDs generated on my local sql will nearly almost always be different from those generated on the remote db.

How do i handle this situatoin is my question? advice, guys?why are you using the product_id in the relation...shouldn't you be relating the product_number to the categoryId... rather ? which will stay fixed... ?|||well, not to be a smart alec, but duh. that's what should be done.

problem is the team who wrote this SF.NET app don't do that. they use the productID autonumber and categoryID autonumber.

so for example, when you use their merchant tools to set a product into a category, behind the scenes they are using the autonumber id of the product and not the REAL product number. same with category ID.|||I personally prefer the way StoreFront has implemented a surrogate key. This is somewhat of a religious topic in that proponents of each method are pretty adamant that "their" way is right :-)

Anyway, with DTS there is an option to "Enable identity insert" on the Options tab. Turning this on should cure your problem.

Terri