What is better extra query or extra column in database?

Question

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.

Thanks

Answer 1

Do you mean calculating a value in the your query versus storing a calculated value?

This depends on how often it will be updated, how big the data will be, how often it is needed. There may be no theoretical best answer, you will need to test and profile.

Answer 2

Doing an additional query will always take more time.

Adding an extra column in DB will not have any significant impact, even if you should have thousands of rows.

Ergo, add extra column and save DB trafic :)

Answer 3

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.

Answer 4

It depends on amount of redundency you will ad to table by adding a column. With proper indexing and design joins work better so no need to afraid of normalizing if required.

Answer 5

Use the second table. It will not require you to issue two queries. Instead, you will issue a single query JOINing the two tables together or, better yet, create a VIEW that does the JOIN for you:

SELECT usertable.col1, usertable.col2 superusertable.superuserid
    FROM usertable LEFT OUTER JOIN superusertable 
    ON usertable.userid = superusertable.userid

This allows you to maintain proper normalized structure, helps you in certain queries (like figuring out who is a super_user), and allows the database to optimize the search issues.