overhead for multiple tables?

[bad_gui]

I'm new to php and mysql.  I am modifying a project to maintain journal articles in a searchable database.

The original project had a flat file structure and one of the columns was "category" which contained the

arbitrary keywords the authors provided.  In order to make things more consistent I switched to checkboxes

for the user to select which fixed categories to associate with the paper.

 

My question is:

 

for a db of about 1,000 rows, should I keep the flat file (easier for me to code) or add a table

of 8 categories and papers can be assigned to multiple categories (difficult for me to code).

 

I would like to give users the option of browsing a category.  Would this be slow with a flat file?

[dbo]

You want to use a DB... and why is it harder for you to code using it? IMO it's far easier to query a database than to write custom parsing code for the flat files...

[bad_gui]

Since I'm modifying someone else's code, the parsing stuff is there I just need to convert checkbox

selections to text in a "category" column inserted into the existing flatfile.

 

The flatfile looks like this:

 

file_id  int(11) 

file tinytext

authors text

title text I

journal tinytext

date1 smallint(5)

date2 date

abstract text

 

The only columns that seem worth converting to their own tables are journal and date1 (year of publication).  All of the

others will have mostly unique data.  I would need to rework lots of code. 

[dbo]

Ah I was confused on what you were asking. Talking about flatfiles and then columns and stuff I thought you wanted to know if it was better to use a DB. Anyways...

 

So what are you asking exactly? Do you need help coding this? It doesn't sound like a design question.

[bad_gui]

Short answer:  how much work do I want to put into this?

 

Currently data is written to a flatfile but what happens when user tries to search 1,000 rows?

Does this take miliseconds or a minute or two.

 

If the user tries to search the abstract field which will contain a paragraph of text for each row,

this is a lot of work but this field can't be split into another table.  The only columns that make

sense to convert to their own tables are journal, date1 and category.

 

Since I'm a novice and I'm modifying someone else's code this could be a lot of effort.

 

Adding a column to the existing flatfile is easy but will I regret it when there are 1,000 rows to search?

 

 

[dbo]

Again, DB is the best solution. It's much better at finding data than opening flat files.

 

Is it going to be a problem with 1000 rows? Probably not. It'd really have to be getting slammed to notice a big impact, but as it grows performance is definitely an issue, as it's not been designed as a scalable solution.

 

So the answer is, it really depends on the situation. I don't know how important this feature is, who your user base is etc. If you're looking for a temporary fix, continuing with the flatfile system might work... if you're thinking longterm you need a DB.

[activeserver]

Short answer:  how much work do I want to put into this?

Since I'm a novice and I'm modifying someone else's code this could be a lot of effort.

Maybe you could look at it as an opportunity to learn and add to your skillset? If I were in your place I would take the time to implement a db solution, dump about 10,000 dummy records, then run on both solutions and then write an article at a php site explaining the whole speed and optimization thing and try to make some money out of the article as well (er.. no guarantees... )

Currently data is written to a flatfile but what happens when user tries to search 1,000 rows?

Does this take miliseconds or a minute or two.

If the user tries to search the abstract field which will contain a paragraph of text for each row,

this is a lot of work but this field can't be split into another table.  The only columns that make

sense to convert to their own tables are journal, date1 and category.

Adding a column to the existing flatfile is easy but will I regret it when there are 1,000 rows to search?

But, if you have very little time and the data wont scale up to 10 times, maybe the app will work with flat file. The more important thing is how many concurrent users will use your app. If it's more than say 5-10 per second then you should definitely go to a db solution. If it's a small user base like totally 5-10 users ever, then flat-file is fine.

OTOH If you're learning and if it's part of your studies or work, don't count the money or time.

[dbo]

OTOH If you're learning and if it's part of your studies or work, don't count the money or time.

 

Hehe, I'm sorry my fiend... but real life doesn't usually allow such luxuries. I agreed with the rest of your post though