Yep, that's one thing I look at very closely when building a new DB any
time, I try to make it as efficient as possible and I also try to let SQL
server do as much of the work as I can. But then I also have the luxury of
having two separate boxes, one for SQL Server and one for CF so it's pretty
easy to break the two out and let one do it's part of the work and the other
the other.
As for the tables, I would have one table that would have all the book names
in it, or 66 records and then probably at least one more table that would
have the entire bible in it and I would relate the two with a FK field that
would be something like the book name or even a numeric ID would work...
-----Original Message-----
From: Dean H. Saxe [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 30, 2001 8:05 AM
To: SQL
Subject: RE: Identical Table structures..
Yeah, the current table structure would be a nightmare. Imagine this,
search for a single word in the text of all books. With your table
structure you have to do either one query with 66 joins or 66 queries. If
you add a single book identifier column (more appropriately, a table
containing all available books with a FK residing in the book text tables
to identify the book) one query will do the same job. Much easier.
-dhs
At 07:51 AM 5/30/01 -0400, you wrote:
>Ya, I agree with you and I read your post after I had already replied. You
>are correct, he would be doing MUCH, MUCH better to put all of this into
one
>table and have a native ID, and then another column with the book as a
>secondary ID and then do the search like you where saying... for small
>searches you might be able to make the example below work, but for entire
>bible searches your not going to...
>
>-----Original Message-----
>From: Daniel Lancelot [mailto:[EMAIL PROTECTED]]
>Sent: Wednesday, May 30, 2001 7:42 AM
>To: SQL
>Subject: RE: Identical Table structures..
>
>
>but then how would the tables be related, (IE wouldnt a cartessian join
>result), and 66 ID's would have to be checked to find the correct value.
>
>TONY: can you explain why the books are kept in separate tables???
>
>-----Original Message-----
>From: Bill Killillay [mailto:[EMAIL PROTECTED]]
>Sent: 30 May 2001 12:39
>To: SQL
>Subject: RE: Identical Table structures..
>
>
>In your search queries you need to assign a row alias...
>
>i.e.
>
>Select Genesis.book,
> Genesis.id,
> Exidus.book,
> Exidus.id,
> Etc...
> >From
>Where...
>
>Hope that helps
>
>-----Original Message-----
>From: Tony Hicks [mailto:[EMAIL PROTECTED]]
>Sent: Wednesday, May 30, 2001 3:47 AM
>To: SQL
>Subject: Identical Table structures..
>
>
>I come up with ingenious ways for CF to tell me the code won't work..
>
>I have a Bible Database... 66 books = 66 tables, no problem, right?
Wrong...
>
>Each table has an Identical structure
>
>ID, Chapter, Verse, Text
>
>Is there a way to query all 66 tables through a search? When I did I got an
>error because two or more fields had the same name... or can anyone think
of
>a quick fix? Making one change 66 times is alot of work!
>
>Tony Hicks
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
