Kristen — Tue, 24 Sep 2002 05:44:48 +0000

thanks - I'll check it out.

Busy — Tue, 24 Sep 2002 05:40:52 +0000

you can actually download search scripts (free) from hotscripts.com I got some the other day but havent had time to try them yet. Some search flat files, some databases, some index, some dont ...

sorry can't be more help

Kristen — Tue, 24 Sep 2002 05:31:15 +0000

forgot to mention - the database has been changed since the original specifications and now most of the important search fields are within the same table. Might make things easier.

Kristen — Mon, 23 Sep 2002 04:23:21 +0000

Me again.

It's coming down to the crunch for this assignment and I'm willing to pay one of you guys for some serious help. I've had a death in the family and am just not up to figuring it all out and at this stage study is taking a backseat.

Basically, I need the database created. I have the exact specifications for it, as we have it running in oracle as well and will just be putting that same db into mySQL. So all that needs to be done there is take the tables.sql and create them in mySQL. Then there is the search feature. Basically I'd like just a simple form where the user can input their search object (e.g. John) and then choose from a drop-down menu the field to search (e.g. sender). Then the result will display all emails with John in the sender field within the db. If possible!

please email if you can help.

Mark Hensler — Mon, 09 Sep 2002 04:28:00 +0000

You can try some of the examples in this thread. I have no idea how your DB looks, so I can't say exactly what you need.

Kristen — Mon, 09 Sep 2002 04:20:43 +0000

Heh, it's actually a student Oracle account at my university - so we didn't buy it!

Okay so I need to just specify the fields in the search itself?

Mark Hensler — Mon, 09 Sep 2002 04:18:01 +0000

Oracle? Wow. Would love to get me a copy of that... just a tad bit too expensive though.

mySQL currently doens't have foreign key support. All you can do is index them. You'll specify which fields to join on in your queries. I believe that foreign key integrity is on the features list for future versions.

Kristen — Mon, 09 Sep 2002 01:19:39 +0000

The database was written for oracle by a someone else, and yes, it is normalized.

I was given the oracle sql syntax and have to change it to work in mySQL. I haven't the first clue how to do this, but I did manage to create the appropriate tables and their rows etc. I don't know how to link up the foreign keys etc and can't work it out from the mysql.com information. Basically I just have 5 separate tables at this point, so any help in getting them to work together would be greatly appreciated!

Also, I'm not sure how to get the dropdown working to submit a request.

I hate asking for so much help but I'm really in a jam with this - I need to have it done by next week and have a lot of other things going on right now as well (my cat is very sick) and am just having trouble working it out on my own.

any help would be greatly appreciated!
-Kristen.

ROB — Thu, 05 Sep 2002 06:59:03 +0000

Quote: Originally posted by zollet
Correct me if I'm wrong here, but what I don't like about a normalized database is the fact that... Well, let’s say you have 100 artists and 1000 songs. If you do a query, it will compare the 100 artists to the 1000 songs which is about 100,000 comparisons where a non-normalized database would have given you the same result by going through only 1000 rows.

let the software developers worry about optimized implementation, and know they do a damned good job.

i'm by no means an expert, but what i would imagine happens is something as follows:
- database parses the query and determines you're joining the tables 'artists' and 'songs' presumably on 'artistid'.
- artists.artistid is a primary key and songs.artistid is indexed, or at better be otherwise you have some reading to do
- database iterates through the 'artists' table matching any criteria specified in the where clause, using indexes if possible
- when a match in the 'artists' table is found, the 'songs' database is used searched for join criteria, returning valid results and caching non-valid ones in memory
- since non-valid results are in memory there are no more disk seeks necessary in the 'songs' table after the first iteration.
and, if you properly index your tables the database will know exactly where to seek.

dont doubt that optimization techniques are far beyond my knowledge or understanding, just know normalization IS good, and expected if youre getting paid to do this.

There are cases where normalization really is not a good choice. I'll use VBulletin for an example, although horribly implemented and completely ineffective they had the right idea. VB has a user table and a thread table, with primary/foreign keys being user.userid/thread.userid. user (obviously) has a username field, but, so does thread (or maybe it's called 'author'). anyways, i presume this was to avoid a join when displaying a list of threads and their authors. considering threads is probably the most hit table in a vb forum it was a good idea, but they join anyway for other frivilous crap. anyways, the point is there is an in-between, although there are purists who would argue with me.

Even though there are exceptions, those are just that, exceptions to the rule. IF YOU EVER WANT TO MAKE A LIVING DEVELOPING APPLICATIONS LEARN HOW TO, WHY TO, WHEN TO, AND WHAT TO NORMALIZE, AND DO IT!!! My god, i've downloaded freeware scripts before that made me want to barf, crap like this (a fictional example):

TABLE CARS
name
password
email
make
model
year
color

TABLE MUSIC
name
password
email
likesrock
likescountry
likesbritneyspears
likesbluesclues
likesamericanidol
bandname1
bandname2
bandname3
singer1
singer2
singer3
favoriteguysinger
favoritegirlsinger
favoriteguydrummer
...

and to think the same people who wrote databases like this wrote the install scripts that asked for the root password to my database (note: never ever give your root password to mysql (or *nix or ANYTHING for that matter) to any program. it's not meant for that.)

anyways, 'normalization' isn't just a fad that's hip today and gone tomorrow. people dont do it because it's fun. people do it because that's the right way to do it. i could try to further explain exactly why that's the correct way, but i'd only be repeating thousands of documents at your local Google store.

Cheers

Mark Hensler — Thu, 05 Sep 2002 05:32:54 +0000

Well, ideally you would have a primary key with each table and you would have indexed the foreign keys. This makes searching much easier.

I haven't tested this, but it seems logical that it's easier to search an indexed numeric (int) field rather than a text (varchar) field (indexed or not).

Quote: Originally posted by zollet
Yeah, but I don't see how that is harder to update..

UPDATE table SET artist_name = 'Ozzy' WHERE artist_name LIKE 'Black Sabbath';

Not necessarily easier, but faster. A normalized DB would only change one record. A non-normalized DB would change many records.