What is the best approach for storing hierarchical
data in a database? For example, if I need to store a tree menu system,
how would I do that allowing for the most normalization within the
database, using the least number of queries/resources when pulling the
data out, and using the least amount of overhead both in storage and
retrival?
-Chris
I've got a lot of XML like this (simplified):
Code Snippet
... 8 MORE
... 9 MORE TIMES
I need to get this into three existing SQL Server 2005 tables, each with identity columns for their primary keys:
Code Snippet
CREATE TABLE ELEMENT1 (
[ID] INT IDENTITY
)
CREATE TABLE ELEMENT2 (
[ID] INT IDENTITY,
[ELEMENT1_ID] INT
)
CREATE TABLE ELEMENT3 (
[ID] INT IDENTITY,
[ELEMENT2_ID] INT
)
With primary and foreign keys as you'd expect, and, of course, many more columns!
How would I get this into tables through SSIS, preferably in a high-performance manner (there may be several gigabytes of XML to load).
The issue, of course, is that in order to insert an ELEMENT2 row, I need the ID from the coresponding ELEMENT1, etc.
Any ideas or pointers to articles would be welcome.
Hi,I want to store the SQL Server Agent logs into a table. I was able toread SQL server errorlogs using sp_readerrorlog and store the data intoa table successfully.I want to do the same for SQL Agent logs. options?Thanks in advanceThyagu.
View 1 Replies View RelatedHi all,
I am trying to create a diagram for our database, during the creating, I create some of the relationships which were not there(basically our original database is not relational database, that's why I am doing it)
So sometimes I have to chage data type in order to create a relationship for the coloumns in different tables. i.e. change char(16) to varchar(7) (I checked the field that make sure all the data in this field is <= 7 characters)
But when I saved the diagram, there is an error message that state:
Errors were encountered during the save process. Some of your database objects are not saved on your diagram.
'agent' table saved successfully
'VisitUSA' table
- Unable to create relationship 'FK_VisitUSA_agent'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_VisitUSA_agent'. The conflict occurred in database 'CMC', table 'agent', column 'AgentCode'.
What does that mean? is it caused by some of the agentcode data in VisitUSA table which is not in agent table?
Thanks!
Betty
Hello there,I just want to ask if storing data in dbase is much better than storing it in the file system? Because for one, i am currenlty developing my thesis which uploads a blob.doc file to a web server (currently i'm using the localhost of ASP.NET) then retrieves it from the local hostAlso i want to know if im right at this, the localhost of ASP.NET is the same as the one of a natural web server on the net? Because i'm just thinking of uploading and downloading the files from a web server. Although our thesis defense didn't require us to really upload it on the net, we were advised to use a localhost on our PC's. I'll be just using my local server Is it ok to just use a web server for storing files than a database?
View 6 Replies View RelatedRecently I was in need of a hierarchical tree data. I learned about CTE and how they can be used to build hierarchical data with simple syntax. I used CTE and was through with the task. Later during free time, I tried to compare CTE approach with the traditional SQL 2K Table Function approach. It was surprising to see the query costs when I ran both the modes at one go...
Query Cost (relative to batch) : 0.49%
Query Text : Select * From fn_GetTree(8);
Query Cost (relative to batch) : 99.51%
Query Text : with treedata (id, parentid, status, prevStatus, lvl) as (select ...)
What does that indicate? Does it mean that the Table Function approach is much faster than CTE? I am sure that I was not making unwanted Joins in the CTE mode.
Can someone explain why that huge difference is there? And what the scenarios where CTE is better over Table Functions?
Following is my db table
student_id student_code student_parent_id student_name
1 11 0 a
2 111 1 b
3 1111 2 c
4 11111 3 d
How to generate following op?
student_id student_code student_parent_id student_name Hierarchy
1 11 0 a 11 - 111
2 111 1 b 11-111-1111
3 1111 2 c 11-111-1111-11111
4 11111 3 d 11111
Hi All,
I am attempting to create a Visual C++ application based on displaying financial charts and am using SQL Express to store Stock information such as the Exchanges the stocks are traded on, the indicessectors they belong to and the Closing prices for as long as I can download data for. I am not proficient in C++ nor SQL and am using this project to learn both languages as well as making myself rich beyond my wildest dreams.
I have "designed" a database with the following tables:
tblDate_ 1 column clmDate (Primary Key, smalldatetime, NOT NULL)
tblStockExchange_ 4 column clmStockExchangeID (PK, int, NOT NULL)
clmParentID (int, null)
clmStockExchange (nvarchar(50), NOT NULL)
clmMarkets_ (FK, nchar(20), NOT NULL)
tblMarkets_ 1 column clmMarkets (PK, nchar(20), NOT NULL)
tblIndices_ 1 column clmIndices (PK, nchar(50), NOT NULL)
tblSectors_ 1 column clmSectors (PK, nchar(50), NOT NULL)
tblMarkets_Sectors 3 columns clmMarkets_SectorsID(PK, int, NOT NULL)
clmMarkets_ (FK, nchar(20), NOT NULL)
clmSectors_ (FK, nchar(50), NOT NULL)
tblSecurities_ 4 columns clmEPIC (PK, nchar(10), NOT NULL)
clmSecurity_Type (nchar(5), NOT NULL)
clmSecurty_Name (nchar(50), NOT NULL)
clmSectors_ (FK, nchar(50), NOT NULL)
tblSecurities_Indices 3 columns clmSecurities_IndicesID (PK, int, NOT NULL)
clmEPIC_ (FK, nchar(10), NOT NULL)
clmIndices_ (FK, nchar(50), NOT NULL)
tblSecurities_Date_OHLCV 8 columns clmOHLCVID (PK, int, NOT NULL)
clmEPIC_ (FK, nchar(10), NOT NULL)
clmDate_ (FK, smalldatetime, NOT NULL)
clmOpen (float, NOT NULL)
clmHigh (float, NOT NULL)
clmLow (float, NOT NULL)
clmClose (float, NOT NULL)
clmVolume (float, NOT NULL)
Why so many tables? perhaps you should put some more in...
This was the only way I could work out how to store one-to-one and one-to-many relationships required for:
- Many closing prices for many stocks
- Stocks belonging to many indices
- Stocks belonging to only one sector
- Stocks belonging to only one market (MainMarket or AIM for LSE)
- Stocks belonging to only one Exchange (I am aware of dual listed stocks but one thing at a time)
Why nchar's and not nvarchar's?
Because I didn't realise the benefits of nvarchar's until recently. How can I change this a loose the extra spaces in the cells.
Why do some tables have IDs and others don't?
I decided to put ID columns in for tables that didn't have obvious Primary Keys - if someone could explain the advantages if ID columns I would be grateful.
To the SQL Professional's eye there will be some obvious things wrong with this design and your criticism is welcome. The database I have is achieving what I would like it to do; I can plot charts using the data but I have ran into problems when trying to create a TreeView control which is what I would like to use as a navigational tool in my application.
It would seem that pulling hierarchal data from a relational database, to pass to the TreeView control, is a tricky task to say the least. I have found many articles online which discuss how to do this (using an Adjacency List Model or Nested Set Model) but they define a fairly simple example at the beginning (based on fruit or electrical goods) but don't appear to talk about gathering data from an existing relational database or changing an existing relational database so that it is more suited to storing hierarchal information. I have Joe Celko's - Tree and Hierachies in SQL for Smarties but sadly this fine material is a little beyond me!
I would like the hierarchy to look like this:
StockExchange
Market
Sector
Stock
Indices
Sector
Stock
I have written three queries to get the StockExchangeMarketSectorStock information individually from each table but am struggling with ways to put all the rows together, add left and right values (Nested Set Model) then run queries against this to get individual nodes to pass to the TreeView control. Therefore is there something I need to add to the original design?
Any help would be greatly appreciated.
Hi
I'm using VB.NET,ADO.NET in ASP.NET .
Microsoft .Net framework 1.0
Windows 2000
Visual Studio IDE
and SQLServer.
I have to create to tables as
Table1 contains ID,Name
Table2 contains ID,Marks,Foreign Key ie Primary key of the table Table1.
Give me an information how to create these two tables in SQLServer (I know how to create a table but i don't know how to create a table which includes Foreign Key.)
Then using Dataset i want to display the records as Name,Marks which are stored in two tables.
I have studied that in ADO Join query and record set object is used but it gives a problems and it is not good when we want to transfer the data between two applications or pages but dataset solves all those problems.
Give me an information about it.
Kindly help me
Thanks in advance
Regards.
If I have a table that contains default values or a constant values do I have to relate it to my other tables that might need them. Thanks in advance.
===============
JSC0624
===============
Hi,I have a very simple question.In what cases are relational databases necessary?Are they really necessary in cases where only asingle type of query is to be performed based on one uniquefield or can we just put all fields together in a single databaseand just access them through that unique field?
View 6 Replies View RelatedHi guys,
What is a "Relational Database"?
Thanks,
Aldo.
Dear friendsI am conducting a survey on Relational Database usage and would likeyour help. The study is part of my MBA Dissertation.Could you kindly spare 5 minutes to take part in this survey?http://FreeOnlineSurveys.com/rendersurvey.asp?id=120816ThanksRajeev
View 11 Replies View RelatedI have a database has the following linklist structure. A, B, B1, B2 and B3 are records.
A is a group and the others are group members
***************************************
A.PointertoB
B.ForwardPointertoB1
B1.ForwardPointertoB2
B2.ForwardPointertoB3
B3.end
***************************************
I want to convert them to relational database structure so I need to find following pointers
B.PointertoA
B1.PointertoA
B2.PointertoA
Can I use sql to find the pointers? Thanks in advance.
Can this be done in ASP.Net, as it stands my database views in my ASP.Net application are just standard
Unlike the view in MS Access which shows the collapsable linked data below the data (from a different table)
Many thanks
Rich
I need to create a dimension with a hierchy derived from 3 other dimenions where one of the dimensions contains a recursive hierarchy.
eg:
In my RDBMS I have; a table "Entities" which contextualises geographic locations and (typical columns include and id and a description with values for: ID1=United Kingdon, ID2=France, ID3=Norway, ID4=Finland, ID5=Europe, ID6=Scandinavia, etc.), another table "Entity_Type" which contextualises the values held in "Entities" (Typical olumns; ID1=Continent, ID2=Country) and a table, "Geo_Code" which codifies the values from both "Entities" and "Entity_type". They act more or less like 2 dimensions ("Entities", Entity_Type) and a fact table ("GEO_Code").
The third table has a recursive hierarchy in that it references it's own ID again in column "Entity Reference".
The first table is Entities, the second is Entity_Type and the third is the one that references both.
id
description
01
England
02
Norway
03
Europe
04
Scandinavia
id
description
01
Continent
02
Country
id
entity
Entity_Type
entity Reference
01
03
01
NULL
02
04
01
NULL
03
02
02
02
04
01
02
01
From the last table "GEO_CODE" we can determine the following:
01. Europe is a continent
02. Scandinavia is a continent
03. Norway is a Country that is in Scandinavia (Entity Reference recursive referencing ID 02 of the same table where the Entity is 04).
04. England is a Country that is Europe (Entity Reference recursive referencing ID 01 of the same table where the Entity is 03).
How do I create a new dimension using Visual Studio in Analysis Services where the Hierarchy is reflected?
So far, I have managed to depict 2 Hierarchies: where a dimenion depicts Entity (Countinent/Country) as level 1 and the value of the corresponding entity (Europe, ENgland, Norway, Scandinavia) in level 2 but how do I get to the last bit where the Entity Reference references the location of the Country or Continent???
Sharepoint is a pretty darn dynamic service and that got me thinking of how databases are created.
Just wondering out loud, surely someone has thought of creating databases in such a manner, but I don't know if it's a thought that has been struck down.
It would look something like this:
Single Table
ID uniqueidentifier PK
ParentID uniqueidentifier FK to ID
Name nvarchar(MAX)
Value nvarchar(MAX)
In this manner, you would create your database "columns" as needed in the data-layer.
If that is too strict, (every datatype would be encoded to base64), you could create a value option for basically every data type. EG. nvarchar(max), nvarbinary(max).... and add another field that describes the data-type to be used for that "column"
ID uniqueidentifier PK
ParentID uniqueidentifier FK to ID
Name nvarchar(MAX)
DataType nvarchar(50) //constrained to allowable datatypes
MaxLen nvarchar(31) //ahh, what the hey, let's add this for sniggles.
ValueNvarchar nvarchar(MAX) nullable
ValueNvarbin nvarbinary(MAX) nullable
....
Now, to allow the "values" for those "columns", you may be able to still use the single-table approach, but it may be better to have an extra table for that (probably even a different partition and drive).
Things to consider, indexing.....
In development, the data-layer would handle the creation/reading of table columns.
The business-layer, which could be many for different parts of a company, would make it's requests to the DL. It may need a username, and the DL would either just create it, or suggest an already existing username column. The path to that username may not be where a particular biz element wants it, so they will ask the DL create another under a diff path.
The business-layer is probably the most important reason for wanting a single dynamic table.
In the end, the relation structure could be like:
Human //dir, no value, no parent (root)
FirstName //value - text
LastName //value - text
Parent1 //value - Me Id
Sibling1 //value - Me Id
SiblingN //value - Me Id
School //dir, no val
ParentId //value - Id (perhaps category would be Building)
Name //value - text
The sibling N would be an example of how a table may need to be dynamic. A positive of the single-table approach is no limitation on number of "columns". Another is the ease to move a hierarchical structure if needed. School may want to be University instead of just "school" and be placed as a child of "school".
Looking for any thoughts on the subject,
Nathan
Hi, all here,
As people say, Microsoft was the first major database vendor to include data mining features in a relational database. What dose this exactly mean? Thanks a lot for any guidance.
With best regards,
hello,
I am beginner for asp.net and sql server. I used Sql server manegement studio full version and I exported my aspnetdb which was created by VS2005 to my host sql server. I have a question:
relational tables are not relational no longer. I noticed that when I created database diagram. what is wrong by exporting?
thanks for your helps...
I have a system that basically stores a database within a database (I'msure 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 acolumn of type nvarchar(4000), but I want to add support for unlimitedtext. I want to do this in a smart fashion. Right now I am leaningtowards putting 2 nullable Value fields:ValueLong ntext nullableValueShort nvarchar(4000) nullableand dynamically storing the info in one or the other depending on thesize. ASP.NET does this exact very thing in it's Session State model;look at the ASPStateTempSessions table. This table has both aSessionItemShort of type varbinary (7000) and a SessionItemLong of typeImage.My question is, is it better to user varbinary (7000) and Image? I'mthinking 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 benifitof using varbinary and Image datatypes? If it's just to allow saving ofbinary data, then I don't really need that right now (and I don't thinkASP.NET does either). Are there any other reasons?thanks,dave
View 7 Replies View Relatedbest way to store questionnaire data in a database.Since different questionnairs have different questions and formats i.e dropdown, radio, checkboxes etc building such a database model becomes highly complex.
I've read that if data schema is complex and higly variable it may be better to use an xml document and store that in a databse. However I dont quite understand how you store xml to a database. Do you simply store the entire structure in something like a nvarchar column or is there some other way to store xml to a database.
If you store the entire structure to the databse then how do you query the content to generate reports.
example xml:
Code:
<survey>
<meta>
<id>sample</id>
</meta>
<questions>
<question id="1" type="singlechoice" page="1">
[code]...
Does anyone currently do this. I want to store an audit history for changes made in the database through our web application.
I would like some kind of history table structure. I would be interested to see if anyone else has done this and what your structure looks like.
ScAndal
I have a bit of a dilema, that maybe someone can give a reccomendation on. I have a vb app that will calculate a duration that a process runs in hours:minutes:seconds. My question is should I store this in the database as a date/time field or calculate total seconds and store it as an integer field? I will be using this field for basic summing calculations in the future. Thanks for any help.
View 1 Replies View RelatedHello-
Im currently storing an account id in a sql table. Is there any column data type that would presere the numbers but make it appear as a series of letters and nmumbers when someone looks at the database table?
If not can someone give me a strategy
thx
Hi,
can we keep a file like word file or html file in the sql server database?
if yes, then can we search any thing in these stored file?
regards
mihir
How do you store an image in a database? I know this is not the preferred method for storing images but would like to know anyways…thanks.
View 1 Replies View Relatedhello ... i have a project this semester and i had to study is storing images in a database ( using Microsoft Access ) is good or bad ? so any one could tell me the advantages and the disadvantages please ...
thanks
bye
An odd question from me, I know, but this time, I assure you a twist.
I have a group that wants to store images in either a database or a file share, in order to make a certain website able to run on a load balanced web farm. These images are around 1KB each in size, and have a lifespan of exactly one use (think of graphs). I went a-googling, and found no shortage of articles that say "don't do it, but here's how you can do it", but I did not find any real hard statements as to why to not do it. Needless to say, this is hurting the case for not putting these images in the database. I have found an article that says images over 8KB will have worse performance, but I can not use that factoid here. For the moment, I have the developers leaning toward the fileshare, because they will be writing transaction logs all day, which will be more work than the fileshare needs to do....I think. The best I can do, is cut the text/image datatype overhead, and have them create the table as varbinary(2000), but even then, I don't like the look of the idea.
Anyone out there have good articles/whitepapers that detail the differences between writing single-use images to a fileshare vs. a SQL database? <baiting=blatant>Failing that, do any of the Microsoft development team have an opinion?</baiting>
Alternatively, what is the general opinion of keeping session state information in a database (because I bet that will be my next battle). I see .NET includes a session state server, but not being a programmer, I can not create an opbjective test.
Hy, could someone help me in this:In design mode, i want to put pictures in database. I made column namedpics, and its type as image. How can I put pictures or some address of thesepictures in that column, or i maybe need to put pictures in Add_Data folderand make reference from there, or what I need to do?could someone explain me that process of putting at least one picture indatabase, I'm using Microsoft SQL Server 2005thanks everyone
View 4 Replies View Relatedhi,I would like to store windows passwords and usernames in database.Please tell me where to start?What database can I use?Can I use free microsoft database?ThanksBart
View 6 Replies View Related
Hey I currently have a foreach loop container working which scans a folder, loops through the files in this folder and then moves them to a new folder.
At the same time I also do an SQL insert into a table logging the details of the transfer.
What I would like to do next is to store the actual PDF in binary in my DB (varbinary format). How would I go about this ?
i use a teltonika t-modem gsm modem..
it's quite new actually and it is connected to my PC thru a usb port..
my problem now is that
i can,t seem to get the hang in storing received messages in
my SQL database..
the code goes like this..
a receive button is clicked to generate an instance
where all SMS sent to my sim card would then be accessed by my gsm modem..
private void btnReceive_Click(object sender, EventArgs e)
{
ListViewItem Component;
Cursor c = Cursor.Current;
Cursor.Current = Cursors.WaitCursor;
listView1.Items.Clear();
btnReceive.Enabled = false;
objGsmIn.Device = cmbDevice.SelectedItem.ToString();
objGsmIn.LogFile = txtLogging.Text;
if (cmbDeviceSpeed.SelectedIndex == 0)
{
objGsmIn.DeviceSpeed = 0;
}
else
{
objGsmIn.DeviceSpeed = System.Int32.Parse(cmbDeviceSpeed.Text);
}
objGsmIn.Storage = fMsgStore.MsgStorage;
if (fMsgStore.DelMsg == true)
{//deletes your message after receiving
objGsmIn.DeleteAfterReceive = 1;
}
objGsmIn.Receive(); //this checks your new incoming messages
if (objGsmIn.LastError == 0)
{
objGsmIn.GetFirstMessage();
while (objGsmIn.LastError == 0)
{
//continously receive all messages..
Component = listView1.Items.Add(objGsmIn.MessageSender);
Component.SubItems.Add(objGsmIn.MessageData);
objGsmIn.GetNextMessage();
}
txtResults.Text = "Messages Successfully Received..";
}
else
{
//error in message receiving process..
txtResults.Text = "Error " + objGsmIn.LastError + "(" + objGsmIn.GetErrorDescription(objGsmIn.LastError) + ")";
}
btnReceive.Enabled = true;
Cursor.Current = c;
}
now my problem is that i cant figure out where i would place my stored procedure
in storing those messages to my database..
the problem is that..
if i place my stored procedure on the 1st "IF" statement..
it will only save my 1st incoming message..
but another thing is that if i put it under my "WHILE" statement..
dont you think that it will continiously loop?
meaning to say that the next tym i tried to access all my received messages
it would definitely store all the previously read messages again to my database..
which then creates multiple copies of my previously stored messages again and again
well i hope you get the gist of what i mean..
its quite complicated to explain..
but then again thanks a lot for taking notice on my post regarding this..
thanks!!^_^
another thing..i used the active xperts c# application in order to
create this application..
How can I store the big files in the SQL Database, like MP3 files or an image or an video file?
View 3 Replies View Related