Efficient ways to retrieve specific rows...

[IchBin]

Re: Efficient ways to retrieve specific rows...

Look into the database terminology for "Normalize a relational database"

--
Thanks in Advance...
IchBin
_______________ _______________ _______________

'Black holes are where God divided by zero.'
-Steven Wright, comedian (1955- )
"Daniel Tonks" <dtonks@sunstor mADD-DOT-COM> wrote in message
news:41145c16$0 $8084$a1866201@ authen.newsread er.visi.com...[color=blue]
> "Norman Peelman" <npeelman@cfl.r r.com> wrote in message
> news:EbXQc.3955 $4s6.2739@torna do.tampabay.rr. com...[color=green]
> >
> > That really isn't the answer to his question. The problem is he is that[/color]
> he[color=green]
> > has not committed to take the leap from flat file to database. The very[/color]
> way[color=green]
> > in which he is retrieving the information is what is holding him back. I
> > believe there are two options:
> >
> > 1) go full database (would require writing scripts to convert flat files[/color]
> to[color=green]
> > database structure)
> > 2) just download forum software and modify to fit his needs.
> >
> > The first is a pain, the later, much easier.[/color]
>
> It's not so much that I haven't committed as I don't want to bite off more
> than I can chew at once. Thus converting in stages.
>
> I have no problem inputting existing flat files to the database - the
> scripts to do that are pretty easy - the bigger problem is coming up with[/color]
an[color=blue]
> efficient data structure that does what I need. Which will take a bit of
> thought (like how many tables for the forums and indices - or should I use
> completely separate databases - etc).
>
> And I don't want to resort to pre-written forum software yet. I just[/color]
prefer[color=blue]
> having a really unique look and feel (it may be possible to modify some of
> the prewritten forum software out there as much as I'd like, but frankly I
> haven't seen a good example of that so I'll just stick to my own stuff...
> which I enjoy doing at any rate).
>
> - Daniel
>
>[/color]

[Jeff North]

Re: Efficient ways to retrieve specific rows...

On Sat, 7 Aug 2004 05:18:12 -0400, in mailing.databas e.mysql "Daniel
Tonks" <dtonks@sunstor mADD-DOT-COM> wrote:
[color=blue]
>| "Jeff North" <jnorth@yourpan tsbigpond.net.a u> wrote in message
>| news:ud19h09b4g 1a2905171rcugdp vbkm6c11q@4ax.c om...
>| > On Sat, 7 Aug 2004 00:56:52 -0400, in mailing.databas e.mysql "Daniel
>| > Tonks" <dtonks@sunstor mADD-DOT-COM> wrote:
>| >
>| > >| > Or do you mean flat-table (non-normalised table) that resides in the
>| > >| > database?
>| > >|
>| > >| Sorry, I was just paraphrasing. Yes, I have to read in each line from
>| the
>| > >| text file and split the line into the variables I need. Hey, the forum
>| was
>| > >| originally structured back in 1998 (expanded many times since),
>| databases
>| > >| wern't exactly on my mind at the time. :-)
>| >
>| > Then create your tables and use the database post haste. Text files
>| > can not be indexed and therefore a sequential search of the data is
>| > require. There is no getting away from this.
>|
>| Oh, I know that... my current search engine sequentially reads through over
>| 400,000 messages (73,000+ files) as it is now... although I've come up with
>| ways to make it more efficient over the years. I recently built myself a
>| dedicated server (which is why I finally have MySQL available) that has
>| oodles of power and actually manages to work though that horrible searching
>| task quite quickly.
>|
>| All in due time though, getting the account system done is turning into
>| enough work (although I'm merging in a whole output design overhaul as
>| well).
>|
>|
>| > >| > With this type of setup you can retrieve data by message or user.
>| > >|
>| > >| I think you've done something like this before!
>| >
>| > Only a couple of thousand times :-)
>| > It is also how I used to teach students to design relational
>| > databases.
>|
>| Ah, that explains it. :-)
>|
>| <SNIP>
>|
>| > Use the People, Tings, Events (PTE) methodology to design you
>| > database. A bit of time with pen and paper and thought will help you
>| > alot.
>| >
>| > Something I didn't mention in the previous post. You might want to add
>| > different categories/areas that people can post to i.e. General
>| > Discussion, Weather, Politics etc. Using the PTE methodology can you
>| > see how this table could be added later without disrupting the current
>| > design?
>|
>| Yeah, I was figuring that out (currently have 21 forums). However couldn't
>| it just be part of the "Things" table? Add a "ForumID" field and assign a
>| forum ID to each message header when it's added, then just specify one when
>| retrieving headers.[/color]

Spot on :-)
[color=blue]
>| > Also the previous design only allowed for a flat list (unlike a
>| > tree-like structure). Obviously this is way over your head at the
>| > moment but once you understand databases then you should be able to
>| > plug in this feature without too much trouble (provided your database
>| > design is sound to start with).
>|
>| The current system isn't threaded, so I'm content to stick with that.
>|
>|
>| > >| Thanks very much for the ideas - you have no idea how appreciated they
>| are.
>| >
>| > If it helps, here is my web site that I setup for my students. I
>| > haven't updated in years and it uses MSAccess as the database.
>| > http://www.users.bigpond.net.au/jefnorth/access.htm
>| > The Tables area might be very helpful to you.
>| >
>| > No probs.
>|
>| Thanks, I'll take a look at it. One of my books had a chapter on smart
>| database design which helped me improve the efficiency of the current
>| accounts table (which stores quite a few different items).
>|
>| - Daniel
>|[/color]