I spent five minutes on it and went no further.
You're not forcing referential integrity in the database.
The relationship between product.productID and productDetail.productID
was able to let me enforce referential integrity (a checkbox in the
relationship window)
The relationship between OtherDetail.PD_ID and ProductDetail.ProductID
did not let me enforce referential integrity.
I went no further. I suggest you tweak your database design. I suspect
that you do not want a relationship between OtherDetail.PD_ID and
ProductDetail.productID, but probably between OtherDetail.PD_ID and
product.productID. (but I'm not entirely sure).
That is where I would put my efforts.
As a general commentary on db design, I prefer to name foreign keys
identically to the primary key that they are referencing. So,
product.ProductID and productDetail.productID is good. However,
productDetail.productID and PackageDetail.Pork_ID is bad. I can't look at
the PackageDetail table and immediately know that Pork_ID references back
to the productDetail table.
packagedetail.PorkID also seems to be related to kit.kitID. It seems
flawed to have a single field referencing back simultaneously to two
different tables.
Also (I did not look at the ColdFusion code) it looks like the
ProductDetail field has an 'OtherYN' field. Is this is a boolean field
that you use in your code to check whether or not the to query the
'otherdetail' table? That seems like a flawed implementation to me.
I'm not going to go any further. ;) With all due respects, spend that
$50 on a relational database book. I'm currently in the process of trying
to convince a client to allow me to re-build his whole site from
scratch. It seems to use some of the similar practices that you are
defining here.
Instead of a standard 'product' table, there is a product table and a
specialproducts table. Every time the shopping cart system has to bring up
a product's info from the database, it checks to see whether or not the
product is a special product or not. If it is, it performs one query, if
not, it performs a second. We have two queries instead of one, which is
double the coding time and double the debugging time. Every time there is
a problem (of which there are a lot) it takes longer to fix. I have not
been able to discover a difference between the two tables yet. Yes, the
field names are different, but the data in them seems to be the
same. (I.E. serialnumber, productdescription, priceID, active)
Before the products are added to the shopping cart (I.E. we can't check
a the specialYN field until that info is in the db) specialproductIDs are
prepended with an S, and every time we reference a product, we perform
string processing on productID to distinguish which type of product we are
referencing.
Maybe I should move over to CF-COMMUNITY venting list? But, I suggest
you re-evaluate your database structure, modify the code accordingly, and
move from there.
(Five minutes of evaluation, 25 of bitching... not a bad start to the day)
Jeffry Houser | mailto:[EMAIL PROTECTED]
AIM: Reboog711 | ICQ: 5246969 | Phone: 860-229-2781
--
Instant ColdFusion 5.0 | ISBN: 0-07-213238-8
Due out June 2001
--
DotComIt, LLC
database driven web data using ColdFusion, Lotus Notes/Domino
--
Half of the Alternative Folk Duo called Far Cry Fly
http://www.farcryfly.com | http://www.mp3.com/FarCryFly
--
If it's the thought that counts you can always count on me
I think about you all the time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
