Wednesday, March 7, 2012

database design questions

Hi,
I designed the database and there are two points I'm not sure that my design was correct:
1) In my search engine (in interface) i have an expression builder and user can save the expression he created. In this case don't chreate view in database, but I have a table named "Expressions" which saves expression name and expression. The reason I made it was: If I create view it doesn't make db to work faster because the select query in view runs every time when view was opened, but it makes my code more generic . But isn't it a design error?

2) I save 3 reserved fields in each table, and I don't know if I'll use them some day. The reason: adding a column to database is heavy operation. But isn't it a design error?

Thank you for adviceDear Yulian

Pre-creating the SQL expressions as views will boost up performance, instead of recompiling the SQL expression every time its executed the view is already compiled and execution time is therefore shortened. however, adding and removing objects (views in your case) to the database by multiple users has the potential to become an administrative disaster therefore I would recommend NOT to create views and keep doing what you already have done.

The question weather an operation is heavy or not is only important if you perform it frequently or its so heavy that executing it actually stops the server for a while. adding columns is neither of these and keeping extra columns is therefore not required, furthermore what names did you give these records? having records named "rec1" is going to make your life very difficult one day, dont do it.|||Dear Idba!
Thank you for you clear and reasonable answers. I have one more question:
in one table I have a field "comments", I estimate that it won't contain text that is too long, that's why the datatype varchar(8000) and not text. It gives me a possibility to use "DISTINCT" in queries, while I can't use "DISTINCT" in text fields. But isn't it a design error?|||Never use TEXT datatype if the character number for the field is less than 8000.|||Actually, you can get distinct values from TEXT/NTEXT/IMAGE fields.

No comments:

Post a Comment