Database:mysql
Scripting:php 5
well i am working on a system
in additional to all other features of it,it has
news
articles
(tables will have many textareas)
In theory, we can adjust both in same tables in general cms technique(single ‘contents’ table)
User can submit news and submit articles…
My estimation is around 400 news and 400 articles submitted every day at best scenario(wrost may go upto 1200 per day)
I know mysql can handle millions of data and lots depends on query,server and performance may not be problem at initial stage.
But i do have heard and felt that performance is affected by the numbers of records in database.Especially making searches,ordering,index update and so on.
So,for long term,i am in dilemma either i should make single content table and accomodate both news,articles with in single table or do i make separate table for both.
If i make one table,there will be reduction in codes,files and there will be single form,file administration for eg single form for entry.
If i break i will have greater flexibility on fields and tables will grow half only.
For eg,content table may have grown half million every month where as breaking each table will grow quater million now and during searches and ordering it wont have to deal with unnecessay rows.
So,if it avoid performance hit,then whats the problem in breaking the table(thinking of long term)
so,what should i do
please suggest
have single contents table or
separate news and articles articles
(they will have many sub categories)
I can’t say I’m a guru when it comes to database design, but generally each separate element should have its own table, and relationships should be made between tables.
So ideally, a table for articles, a table for categories, a table for users etc etc. Then you can link users together with articles in a many-to-many relationship using a look-up table, and articles to categories in a many-to-one relationship.
This gives you greater flexibility, and enables you to easily retrieve data. If you have one single table to hold all of this stuff I think it will give you more headaches in the long run.
Looking up massive database tables can be slow, but you can use indexes which will speed things up.
Yup that is my perceiption as well…
but i have found that it is thought as “OLD schooling” thought by modern programmers and gurus…
means they say
it the database handle the thing…dont worry
million row is not big deal to dabase and so on…
I agree with you that it is ideal to have separate table,but if performance hit is negligible then the extra tables will be just extra code,extra crud work,extra maintainance and so on…
so has pros and cons…
and still investigating
cms like wordpress,joomla all have single table…i agree there are drawbacks to it as well…
any way thanks for your thoughts
keep them coming
From what I see, there’s no reason to break up the tables. The tables (based on your limited description) seems to be normalized fine. The only time to really break up tables is if there is consistent data that is being repeated over and over and over again (user information, categorizing information, etc). This is called 3NF database normalization (http://en.wikipedia.org/wiki/Third_normal_form), and most times is sufficient for database performance. You can go further with your normalization, but unless you’re very good at it, the performance degrades rather than improves.
If you run into performance issues, then you need to look at your queries, indexes being applied (are there enough/too few/too many; are they appropriate?) and the criteria being passed.