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.

Database design problem

I have a problem when the foreign key in a table points to the primary
key in the same table. Here is the script to create the tables:
CREATE TABLE [Folder] (
[FolderID] [int] NOT NULL ,
[MasterFolderID] [int] NULL ,
CONSTRAINT [PK_Folder] PRIMARY KEY CLUSTERED
(
[FolderID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Folder_Folder] FOREIGN KEY
(
[MasterFolderID]
) REFERENCES [Folder] (
[FolderID]
)
) ON [PRIMARY]
GO
CREATE TABLE [File] (
[FileID] [int] NOT NULL ,
[FolderID] [int] NOT NULL ,
[FileSize] [int] NOT NULL ,
CONSTRAINT [PK_File] PRIMARY KEY CLUSTERED
(
[FileID]
) ON [PRIMARY] ,
CONSTRAINT [FK_File_Folder] FOREIGN KEY
(
[FolderID]
) REFERENCES [Folder] (
[FolderID]
)
) ON [PRIMARY]
GO
So I have a "Folder" table as the master and a "File" table as the
detail, but the Folder table also has a one to many relationship to
itself.
Then I have the following data in these two tables:
INSERT INTO [Folder]([FolderID])
VALUES(1)
INSERT INTO [Folder]([FolderID], [MasterFolderID])
VALUES(2,1)
INSERT INTO [File]([FileID], [FolderID], [FileSize])
VALUES(1, 1, 10)
INSERT INTO [File]([FileID], [FolderID], [FileSize])
VALUES(2, 2, 10)
The "File" table has a field called FileSize that needs to be added to
give you the size of each folder. Here is the sql statement I use for
that:
Select FolderID,
(Select Sum(FileSize) From [File] Where [File].FolderID =
Folder.FolderID) As Size
From Folder
The problem with the above statement is that it only adds sizes of the
files in the folder and not the sizes of the folders in a folder.
I hope someone understands my problem and would realy appreciate some
help.To make sure:
Folder 1
File 1a 10 kb
File 1b 10 kb
Folder 1.1
File 1.1a 10 kb
File 1.1b 10 kb
You want the following:
Folder 1 40kb
Folder 1.1 20kb
Right?
That is done via recursion. Unfortunately, there is no simple way to
accomplish this in T-SQL (at least not until SQL Server 2005). To accomplish
this, you will have to curse through the hierarchy and create the aggregates
.
My advice, esp. if this is a large app: Denormalize slightly to add the size
to the folder. Then create a routine that curses through and gets the total.
Finally, create a trigger that updates totals when a new record is added.
NOTE: This assumes that this is not an oft updated/inserted table. If it is,
you can still get totals for the files and recude the amount of recursion
work necessary to get the final tally for each directory (reduce by one "sum
"
aggregate).
--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Pierre" wrote:

> I have a problem when the foreign key in a table points to the primary
> key in the same table. Here is the script to create the tables:
> CREATE TABLE [Folder] (
> [FolderID] [int] NOT NULL ,
> [MasterFolderID] [int] NULL ,
> CONSTRAINT [PK_Folder] PRIMARY KEY CLUSTERED
> (
> [FolderID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Folder_Folder] FOREIGN KEY
> (
> [MasterFolderID]
> ) REFERENCES [Folder] (
> [FolderID]
> )
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [File] (
> [FileID] [int] NOT NULL ,
> [FolderID] [int] NOT NULL ,
> [FileSize] [int] NOT NULL ,
> CONSTRAINT [PK_File] PRIMARY KEY CLUSTERED
> (
> [FileID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_File_Folder] FOREIGN KEY
> (
> [FolderID]
> ) REFERENCES [Folder] (
> [FolderID]
> )
> ) ON [PRIMARY]
> GO
> So I have a "Folder" table as the master and a "File" table as the
> detail, but the Folder table also has a one to many relationship to
> itself.
> Then I have the following data in these two tables:
>
> INSERT INTO [Folder]([FolderID])
> VALUES(1)
> INSERT INTO [Folder]([FolderID], [MasterFolderID])
> VALUES(2,1)
> INSERT INTO [File]([FileID], [FolderID], [FileSize])
> VALUES(1, 1, 10)
> INSERT INTO [File]([FileID], [FolderID], [FileSize])
> VALUES(2, 2, 10)
>
> The "File" table has a field called FileSize that needs to be added to
> give you the size of each folder. Here is the sql statement I use for
> that:
> Select FolderID,
> (Select Sum(FileSize) From [File] Where [File].FolderID =
> Folder.FolderID) As Size
> From Folder
> The problem with the above statement is that it only adds sizes of the
> files in the folder and not the sizes of the folders in a folder.
> I hope someone understands my problem and would realy appreciate some
> help.
>