To mySQL or not?

That is the question...

[Purrl]

I hope I'm not drudging up an old topic, and tried to search through WW for the answers and found this thread, ( [webmasterworld.com...] ) but I'd still like ask if since August'01 opinions have changed?

I am really familiar working with flat file databases, and have only created a few SQL db's (more for testing than anything). However, a new project I am working on has got me seriously considering to use mySQL? Scalability of course is my major concern, and although flat file db's have worked for me pretty good, I'm concerned if this new project ends up growing to a point where a flat file solution would break.

A few questions for those who are more knowledgable than I am on this topic... I hope you don't mind.

• What are your opinions on the two for larger databases?
  
• Scenerio if I choose to use mySQL: I want to create a directory that has only a few categories, Cat1, Cat2, Cat3.. What is the best work around for creating the tables? Do you recommend one big table, or breaking them up into their own category named tables.. does this matter in mySQL? Sorry if that's a stupid question, but I do this often for flat files so the load is not so great in each category when a query/search is made.
  
• I use Fcntl alot to lock my flat files.. in mySQL, is it just as important to use the LOCK [mysql.com]ing features?

I realize coding has a lot to do with the efficiency of our programs, and also the way we set-up our databases flat or for mySQL. I do my best to think ahead before I code so, I'm looking at the worst case scenario, and I'm struggling to decide which one to use when your DB becomes it's own little monster. How efficient is mySQL anyway for huge amounts of information? I'm thinking it's better than flat files for sure, but reading the thread I entered above, seems some disagree. I'd really love to hear some pros and cons about that :)

[moltar]

For structure I'd recomend several tables but link them by indexes. For example:

table "category":

1. id
   
2. name
   

table "content":

1. id
   
2. category_id
   
3. name
   
4. path
   
5. content
   

then you will be able to join them by IDs when you call them.

[SeanW]

The nice thing about using a database is that you don't have to worry about locking and concurrency, the database handles it for you. The bad thing is that you now have a database to maintain, which in the case of MySQL, isn't really a lot of work.

In terms of your category question, you have to determine if one entry can have multiple categories. If it is a 1:1 mapping, the post above is what you want. If it is a 1:N mapping, you'll need a third table to link the items to the multiple categories.

Search the web for "third normal form", you'll find information on how to design a table.

Sean

[Purrl]

Thank you for your replies.. however I think I worded my second question wrong.. sorry 'bout that. I believe I'm pretty good at structuring my data files, and linking one to another if necessary etc.. my question was leaning more towards asking if placing all your "let's say" category listings under one table would be okay.. or would this produce significant load on the server?

I ask because I'm used to linking my text files together and placing let's say my info for CatA in cata.txt and CatB in catb.txt so that each file does not accumlate so much (insignificant) data that it becomes unstable. I've produced some pretty big files this way, and my code still spits out my data in record speed. However in mySQL is this type of "thinking" necessary?

I've seen examples where some just place all their "data info" into one main table.. and I guess that's where my question orginates from.. it's a totally different way than I'm used to thinking in terms of "structure", because I'm always thinking, how to best avoid overloading my text files :)

> The nice thing about using a database is that you don't have to worry about locking and concurrency

So, unlike text files, with mySQL you don't think that I should worry too much about using the LOCK feature? I just learned of LOCKing in mySQL and was not using it at all in my testing, and since I lock so often.. I thought Oh oh ..

Thanks very much! I believe I'm going to go with mySQL as my database this time around.

[SeanW]

Unless you're doing something that might incurr deadlock, you can safely INSERT, UPDATE, and DELETE at will.

SQL servers build indexes (well, you tell them to) to cut down on seek times within a large table. In a relational database, like data items go in the same table, ie if you have three categories, they all go in the same table with an identifier to specify the category.

A word of caution:

I, like you, started out in flat files before moving into databases. I learned the hard way that a database is *not* a flat file, ie the techniques you use to design the flat files are different from databases. Bad table layout also makes the project harder, so it's worth it to do a bit of reading.

Sean

[Purrl]

Thanks Sean.. your tips help my new method of thinking. I've been trying to read up on everything I can about mySQL databases and it seems how I use unique identifiers with my text files will also work in my Tables (judging from the examples), so I'm crossing my fingers I won't have a problem there. It's just strange for me not to break everything apart to save space, but perhaps I'll find this method even easier than text files itself. There's some pretty fancy features with manipulating the database that you can write in one line than opposed to 5 or more lines for a text file. I'm going to read some more, test some more, until I know for sure I have it right. Sometimes I hate the way I code, I have this terrible habit of going over everything way too many times, and always asking myself " but what if " or " come on, you can make it smaller/faster " ... ugh!

Thanks again.