what structure should I choose?

raffael3d

let's assume I have a several topics in a database. So far I created for them one table and added the colum "category", so if I submit an entry I can choose via a "select" to which category a topic belongs.
then when the data is displayed I can make "where category=english" and only show the content of that category.
now my question is: if the databse becomes really big is it more intelligent to have for each category a table or is this way faster?
which solution is faster?

neh

by convention people will alwyas choose to use the foreign key route (ie a field which you index and filter against) .. if you're a stickler for the rules of normalisation then you do this .. but tbh it depends on how the db is going to be used .. if its going to mainly used to record information frequently (whats known as an OLTP (or OnLine Transaction Processing system) then keeping things normalised (eg using lots of foregin key pointers to reference data thats often repeated (in this case a long string for a category)) is important to keep write times to a minimum .. on the other hand if you have a db for reporting or in other words it mainly displays rather than records data (whats often known as a a DSS db (Decision Support System) then the other extreme is more important. So you'd DE-normalise your db to keep the number of join operations to a minimum and so speed up the selects .. ..

If I were you I'd use a table to hold categories and a single table for all articles .. this way you can always add more categories with simple inserts rather than having to do A LOT of work building new tables and then also rewriting lots of extra code to deal with these new tables every time .. if its just an INT value and the column is indexed it will not give veyr much of a performance hit even on veyr large tables ..

TodManPlaa

Hi
I'd suggest two tables:

table one:
category
categoryID|categoryName
1|fiction
2|non-fiction
3|sci-fi

etc etc

table two"
story
storyID|categoryID|storyName|etc etc
1|1|some book|
2|1|some other book|
3|2|Learn PHP|
4|3|To Mars and back|

etc etc

the two tables are related via categoryID

This way its easy to add and change categories etc (ie far more scalable) and the difference in load on the db would be next to nothing

Hope this helps