Php – better extra query or extra column in database


What is better extra query or extra column in database for data that will be available very less time.

Example: In Case of sub user management either i add one extra column super_user_id in main users table and make enrty if users types are sub_user and the default column value is -1 or i create new table and manage sub user in that table.

But in case of login i have to search in two tables and this i have to make one more query.


Best Solution

There is no general answer; you'll have to be more specific. All I can provide are general principles.

All else being equal, you'll be better off with a well-normalized database without redundant information, for a number of reasons. But there are situations where redundant information could save your program a lot of time. One example is text formatted with Markdown: you need to store the original markup to allow for editing, but formatting the source every time you need the output may be extremely taxing on the system. Therefore, you might add a redundant column to store the formatted output and assume the additional responsibility of ensuring that that column is kept up-to-date.

All I know about your situation is that the postulated extra column would save a query. The only correct answer to that is that you should probably keep your table clean and minimal unless you know that the performance benefit of saving one query will make up for it. Remember, premature optimization is the root of all evil – you may find that your application runs more than fast enough anyways. If find while profiling that the extra query is a significant bottleneck, then you might consider adding the column.

Again, without more knowledge of your situation, it is impossible to provide a specific or concrete recommendation, but I hope that I've at least helped you to come to a decision.