avoiding storing identical information

[aa]

In a Products database I have a field "How_to_Use" which is of memo type and
is quite lengthy. Some products have the same How_to_Use value.
This database is used, among other purposes, to build a product Data Sheet,
which includes How_to_Use information.

For the moment I have to store How_to_Use data (some being identical)
against every product. So, if How_to_Use is modified, modification needs to
be done for every product using this How_to_Use data.

Is it possible instead of storing How_to_Use in the database, to store a
reference to this info stored elsewhere (say, in a text file) and get it
inserted into a resulting Datasheet asp file?

I considered moving How_to_Use to a separate table, but this involves
modifying the code logic.

 

[Chris Barber]

Use the database (that's what it's designed for).

Each 'How To Use' record has a unique ID - store this ID against the product
so you can have the same 'How To Use' ID for multiple products.

Chris.

In a Products database I have a field "How_to_Use" which is of memo type and
is quite lengthy. Some products have the same How_to_Use value.
This database is used, among other purposes, to build a product Data Sheet,
which includes How_to_Use information.

For the moment I have to store How_to_Use data (some being identical)
against every product. So, if How_to_Use is modified, modification needs to
be done for every product using this How_to_Use data.

Is it possible instead of storing How_to_Use in the database, to store a
reference to this info stored elsewhere (say, in a text file) and get it
inserted into a resulting Datasheet asp file?

I considered moving How_to_Use to a separate table, but this involves
modifying the code logic.

 

[aa]

Thanks Chris, but this is exactly what I've been referring to as moving
How_to_Use to a separate table - is it not?

There are some reasons which makes this undesirable (though possible) for
this particular application.

 

[xxxxx]

You need to create a many to many relationship between a "How_to_use"
TABLE and your product table, then you can run a sql query and select
with joins between three tables:

products(keyrod_id) <--->
products_how_to_use(keysrod_id/how_to_use_id) <--->
How_to_use(key:how_to_use_id)

The string above will have breaks in some newsreaders, I guess... just
look at the string all in one line of text...

Now you can just change the memo field in "how_to_use" table one time,
while that same record can be related to many products in the "middle"
table, which is basically and "index" relating the products to the
"how to use" memo field.

 

[xxxxx]

A string stored is a string stored.

The "how to" text is either going to be "queried" from the database or
you'll have to get it from the text in a file somewhere... and you'll
have to relate that "how to" text to the product with it's "id_" in
some way...

You'll have to write some script that extracts the current "how to's"
from the products table and insert the "prod_id/how_to_id" into a new
table... or maybe into some sort of delimited text file that you'll
read into a dictionary object in the script?

You'll have to massage the data into a new table with some ADO and VB
or VBScript ...

I'd build the new table and just change the query...

select distinct "how_to_use"
put result set into new table

---step2---

select * from products
select * from how_to_use
do while prod_select not EOF
do while how_to_use not EOF
if prod_select("how_to") = how_to_use("description")
then
sql ="insert prod_id, howtouse_id into
prod_how_to_use_table"
loop
loop

DONE.

I usually don't take the time to help this much... I think I had too
much coffee this morning.

 

[Chris Barber]

It may be undesirable but it's the only mechanism that you'll be able to
reliably use irrespective of how you implement it.

Your previous post:

<snip>
Is it possible instead of storing How_to_Use in the database, to store a
reference to this info stored elsewhere (say, in a text file) and get it
inserted into a resulting Datasheet asp file?
</snip>

talked about storing the lookup outside the database. Whilst this may be
feasible it certainly won't be efficient or practical and since you are
already using a database, you may as well just take that little step and add
the ref to the How To into the products table.

Chris.

Thanks Chris, but this is exactly what I've been referring to as moving
How_to_Use to a separate table - is it not?

There are some reasons which makes this undesirable (though possible) for
this particular application.

 

[Jeff Cochran]

In a Products database I have a field "How_to_Use" which is of memo type and
is quite lengthy. Some products have the same How_to_Use value.

A perfect case for normalization then.

This database is used, among other purposes, to build a product Data Sheet,
which includes How_to_Use information.

For the moment I have to store How_to_Use data (some being identical)
against every product. So, if How_to_Use is modified, modification needs to
be done for every product using this How_to_Use data.

Not if normalized. That means a separate table for the HowTo part,
and an ID to key it to the product.

Is it possible instead of storing How_to_Use in the database, to store a
reference to this info stored elsewhere (say, in a text file) and get it
inserted into a resulting Datasheet asp file?

Sure. Use the FileSystemObject and you can open and read a text file
into your template page. It's a great way to reduce the size of the
database for static content.

I considered moving How_to_Use to a separate table, but this involves
modifying the code logic.

Yep. Bummer there.

Jeff

 

[Jeff Cochran]

Thanks Chris, but this is exactly what I've been referring to as moving
How_to_Use to a separate table - is it not?

There are some reasons which makes this undesirable (though possible) for
this particular application.

That's a decision you need to make then, the tradeoffs of moving to
text files or a separate table versus the advantages of easier
maintenance and reducing errors in your database.

Jeff

 

[aa]

Thank you for your opinions