By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,810 Members | 783 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,810 IT Pros & Developers. It's quick & easy.

Designing a database within a database... design question storing data...

P: n/a
I have a system that basically stores a database within a database (I'm
sure lots have you have done this before in some form or another).

At the end of the day, I'm storing the actual data generically in a
column of type nvarchar(4000), but I want to add support for unlimited
text. I want to do this in a smart fashion. Right now I am leaning
towards putting 2 nullable Value fields:

ValueLong ntext nullable
ValueShort nvarchar(4000) nullable

and dynamically storing the info in one or the other depending on the
size. ASP.NET does this exact very thing in it's Session State model;
look at the ASPStateTempSessions table. This table has both a
SessionItemShort of type varbinary (7000) and a SessionItemLong of type
Image.

My question is, is it better to user varbinary (7000) and Image? I'm
thinking maybe I should go down this path, simply because ASP.NET does,
but I don't really know why. Does anyone know what would be the benifit
of using varbinary and Image datatypes? If it's just to allow saving of
binary data, then I don't really need that right now (and I don't think
ASP.NET does either). Are there any other reasons?

thanks,
dave

Jul 23 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
>I have a system that basically stores a database within a database (I'm
sure lots have you have done this before in some form or another).


Please explain. What form is the data you are storing? If it isn't
represented relationally then why use SQL Server?

--
David Portas
SQL Server MVP
--
Jul 23 '05 #2

P: n/a
Dave (ch********@yahoo.com) writes:
My question is, is it better to user varbinary (7000) and Image? I'm
thinking maybe I should go down this path, simply because ASP.NET does,
but I don't really know why. Does anyone know what would be the benifit
of using varbinary and Image datatypes? If it's just to allow saving of
binary data, then I don't really need that right now (and I don't think
ASP.NET does either). Are there any other reasons?


Depends on the data you are storing. Since you talk about a "database with
a database", my initial reaction was you would use image, since I assumed
that the database is a binary file, complete with indexes, integer numbers,
and whatever.

But if the "database" is represented in text, for instance an XML document,
then there is no reason to use binary datatypes.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3

P: n/a
I'm sorry, I need to elaborate. When I say database within a database,
I don't meen storing the actual database in a binary column or storing
XML in a column, instead I mean defining the structure of data within a
set of tables.

Instead of a concrete table such as Member with 3 columns: MemberID
int, FirstName varchar(25), LastName varchar(25), it is defined as an
abstract table that's defined across a series of tables. One row of any
of my abstract table actually lives (potentially) in several rows of a
sort of "Value" table. This "Value" table contains one column
(Varchar(4000)) that actually stores the value of the data item.

In our system we have over 15 abstract objects (Member being one of
them), so I know people will begin to question the architecture, but
that is not my point here... We do this for many reasons

1) We must store history on all changes (we write medical software)
2) We must encrypt the data and this allows a generic way to do this
(just flip a bit)
3) Our application will soon allow it's users to create user-defined
table and this is set up perfectly for that since it would only require
DML to achieve this (not DDL)
4) Speed isn't that important, right now our product has 10 users max.
Even if it became an issue we could solve this easily...

thanks,
dave

Jul 23 '05 #4

P: n/a
Dave (ch********@yahoo.com) writes:
I'm sorry, I need to elaborate. When I say database within a database,
I don't meen storing the actual database in a binary column or storing
XML in a column, instead I mean defining the structure of data within a
set of tables.

Instead of a concrete table such as Member with 3 columns: MemberID
int, FirstName varchar(25), LastName varchar(25), it is defined as an
abstract table that's defined across a series of tables. One row of any
of my abstract table actually lives (potentially) in several rows of a
sort of "Value" table. This "Value" table contains one column
(Varchar(4000)) that actually stores the value of the data item.

In our system we have over 15 abstract objects (Member being one of
them), so I know people will begin to question the architecture, but
that is not my point here... We do this for many reasons

1) We must store history on all changes (we write medical software)
2) We must encrypt the data and this allows a generic way to do this
(just flip a bit)
3) Our application will soon allow it's users to create user-defined
table and this is set up perfectly for that since it would only require
DML to achieve this (not DDL)
4) Speed isn't that important, right now our product has 10 users max.
Even if it became an issue we could solve this easily...


Thanks for the elaboration, but I am not sure that this really provided
any more actual useful information to answer the question. "The database
within in a database", is thuse some sort of object that cannot be described
in a single table - nothing strange with that Order + OrderDetails is a
classic example.

But if I remove the veil about databases within database, and just take
the core question of yours: what datatype should use to save text data,
the answer is (n)varchar or (n)text, depening on your need to support
Unicode and the size limits of the data.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #5

P: n/a
Let me completely rephrase my approach...

If you've ever installed the sql data model for ASP.NET that resides in
sql server, you'll notice that Microsoft has a table called
ASPStateTempSessions. There are two columns that hold the encrypted
session data of the user. These two columns are:

varbinary(7000)
Image

and they are each nullable. Depending on the size of the Session data,
one or the other column is used since Blob columns (such as Image,
Text, etc...) are inefficient. Using the Session in ASP.NET you'll
notice that it consists of strings only, so why did Microsoft decide to
use these types? Is there some effieciency thing? Or were they planning
on simply supporting possible binary data in the future.

-dave

Jul 23 '05 #6

P: n/a
Dave (ch********@yahoo.com) writes:
If you've ever installed the sql data model for ASP.NET that resides in
sql server, you'll notice that Microsoft has a table called
ASPStateTempSessions. There are two columns that hold the encrypted
session data of the user. These two columns are:

varbinary(7000)
Image

and they are each nullable. Depending on the size of the Session data,
one or the other column is used since Blob columns (such as Image,
Text, etc...) are inefficient. Using the Session in ASP.NET you'll
notice that it consists of strings only, so why did Microsoft decide to
use these types? Is there some effieciency thing? Or were they planning
on simply supporting possible binary data in the future.


Sorry, I have zero knowledge about ASP .Net, so I cannot answer any
question about its design.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #7

P: n/a
Been Working on the AspState database, some information indicates that
the transfer of the string data is being done as a binary stream for
efficiency, thus requiring a binary db datatype to store it.

*** Sent via Developersdex http://www.developersdex.com ***
Jul 23 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.