NestedTree Structure

Jan 4, 2008

I need to create a table that supports a potentially infinite amount of subcategories.
An example would be:
Food
Pastries(Parent=Food)
Cupcakes(Parent=Pastries)
Chocolate(Parent=Cupcakes)
Vanilla(Parent=Cupcakes)
Danish(Parent=Pastries)
Meat(Parent=Food)
Beef(Parent=Meat)
Chicken(Parent=Meat)
Cajun(Parent=Chicken)
Fried(Parent=Chicken)

The subcategories will vary in depth.

Can someone demonstrate a table create statement and then the proper select to pull this type of data?

Many thanks in advance.

View 2 Replies

Any Structure On Sql Server Like WITH ... SELECT Structure On DB2

Jul 20, 2005

Hi,I'm using DB2 UDB 7.2.Also I'm doing some tests on SQL Server 2000 for some statements touse efectively.I didn't find any solution on Sql Server about WITH ... SELECTstructure of DB2.Is there any basic structure on Sql Server like WITH ... SELECTstructure?A Sample statement for WITH ... SELECT on DB2 like belowWITHtotals (code, amount)AS (SELECT code, SUM(amount) FROM trans1 GROUP BY codeUNION ALLSELECT code, SUM(amount) FROM trans2 GROUP BY code)SELECTcode, SUM(amount)FROM totalsGROUP BY code.............................Note: 'creating temp table and using it' maybe a solution.However i need to know the definition of the result set of Unionclause. I don't want to use this way.CREATE TABLE #totals (codechar(10), amount dec(15))GOINSERT INTO #totalsSELECT code, SUM(amount) FROM trans1 GROUP BY codeUNION ALLSELECT code, SUM(amount) FROM trans2 GROUP BY codeGOSELECT code, sum(amount) FROM #totals GROUP BY codeGOAny help would be appreciatedThanks in advanceMemduh

View 3 Replies View Related

Structure

Apr 18, 2004

let me explain my business rules first.

One company can have many site addresses;
One company can have many trades
And one trade can have many activities.

Ok seems very basic at this stage does'nt it, but if i give some data the complexity changes completly for example:

the trades are as follows:
TRADE TABLE
ID 1: DESC: Printing
ID 2: DESC: Artwork
ID 3: DESC: Photography
ID 4: DESC: Reprographics.

now in this case only the printing trade has activities:
TRADE ACTIVITIES TABLE
ID 1: DESC: Flexo
ID 2: DESC: Digital

So, in the above i am saying which ever company picks Printing as there trade, then they have the option of specifying which particular printing trade it is that they do.

Again simple enough by using one-to-many from the Trade Table to the Trade Activity table.

The problem is that not all trades have associated trade activities, for example the artwork trade activity is just artwork, but i need to be able to allow this to be so in the future.

When I try to create a view to select a particular company, with there associated trade and trade activity, I do not get any result at all because not all of the trade activities have any records assigned to them.

Please can anyone help.

I need to be able to pull back all companies with their associated trades and associated trade activities.

Cheers

Wayne

View 2 Replies View Related

DB Structure

Jan 11, 2006

Hi All

I want the structure of the entire database without the data from a database , I mean all the tables withouut the data ... Can i do that

Plz help

View 1 Replies View Related

DB Structure

Sep 28, 2006

As my learning process continues, I have found that Database structure is probably the most important & most difficutl part of development. Like database normallization etc.

How can I master database structure, ? but I want learning other than any database software like Access or SQL Server, I m looking for a generallized learning.

Will some1 give me assistance ...

View 3 Replies View Related

Exporting Db Structure

Feb 14, 2007

Hi,
is there a way of exporting a database structure and not the data in it?
Thanks.

View 5 Replies View Related

Database Structure

Sep 25, 2007

Hello I am a final year student and for my final year project I have decided to try and create a price comparison website due to the fact that i feel it would be a good project to work on and develop my technical skills in C# and microsoft sql server 2005, since these are mainly the two programs I would say i have more experience with. Basically I want to create a website such as www.pricerunner.com. At this point in time i have started playing around with how i feel the database will look, in terms of tables and columns, below is what i have produced;
Company
Company_ID
Company_name
ADDRESS
Website link
contact
Products
Product_ID
Product_name
Product_Category
Product_Price

I am now stuck as how i will link each company with a product bearing in mind a company will stock different categories of products, also do you think i need any more columns in any of the above tables, as well as am i missing out on anything, I am hoping someone with more knowlegde than myself can shed some knowledge on my problem, thank you.

View 5 Replies View Related

What And Why Is Better Table Structure?

Mar 7, 2008

Structure Number1
Category: CategoryId(P.K.), UniqueName, CreatedDate, ModifiedDateCategoryNative: CategoryId(F.K.), LanguageId(F.K.), NativeName, Description, Importance, IsVisible, CreatedDate, ModifiedDate
Structure Number2
Category: CategoryId(P.K.), UniqueName, CreatedDate, ModifiedDateCategoryNative: CategoryNativeId(P.K.), CategoryId(F.K.), LanguageId(F.K.), NativeName, Description, Importance, IsVisible, CreatedDate, ModifiedDate
Can anyone tell me that between above 2 structure what is better and why?
I just add CategoryNativeId(P.K.) in Number 2 structure.

View 2 Replies View Related

Table Structure

Mar 17, 2004

Dear all,

how can I use a store procedure to get the structure of a table like data type, length, description etc.

I have used sp_columns to get the information but it can't get the description of each field.

Thanks.

View 1 Replies View Related

Getting SQL Database Structure From Here To There

Aug 10, 2005

I'm sure there is a simple way to do this but I cannot think of it.I have an ASP.NET application that I am publishing to a webhost. I have chosen to use FTP to copy my files (aspx, etc.). They work as far as links etc. Now, the next step is to create my SQL database on their server as they instruct, which I have done in name only. (Oh, if it makes a difference, I access the hosting service using Plesk control panel.) Is there any simple way to get my database structure from my PC to the host server? All responses welcome. Thanks.

View 5 Replies View Related

DB Structure/Replication

Jun 12, 2001

Our company would like to start using SQL 7.0 Snapshot replication for some of our customers. My problem is that we often have to make changes to the actual structure of the DB. Will I need to disable replication each time we need to make a change to the db structure on the publisher and subscribers?? Thank you...

View 1 Replies View Related

Database Structure

Oct 9, 2001

Hi All,
I need to get some info on how I can get the script of the entire database stucture. I need to write a procedure to do this. I know in SQL Server I could get the script in a mouse click. But I need to write a procedure to do the task. Is there a package in SQL Server that I could use to accomplish this task?
Any thoughts will be of help.
Thanks in advance.
sj

View 1 Replies View Related

MDF File Structure

Sep 10, 2003

Could anybody tell me the detail structure of the .mdf file.
Actully I want to read the mdf file with out using MS SQL 2000.
I know few details like it has 96 bytes header and pages are divided in 8Kb etc. But I want to know the exact details, so that I can read the Table names and columns at least.

Thanks in advance,
Prasanna.

View 2 Replies View Related

New Database Structure

Sep 2, 1999

I am very new to SQL server, and am completely stumped here. I need to transfer the data from an old database to a new one (both SQL Server 6.5 DBs.) The only problem is that the database has been restructured, and I can't for the life of me figure out how to get the data imported. I am working on manually making all of the structure changes, but there must be a better way. Any ideas? Thank you SO much!

Jeff

View 1 Replies View Related

MDF File Structure

May 8, 2002

When you attach an MDF that was created using SQL 7.0 to SQL Server 2000, the MDF file structure gets updated. The resulting MDF can no longer be used by SQL 7.0.

My Question: does anyone know how to read the file structure version of a detached MDF file?

If the file structure is 7.0, I would like to warn the user and allow her to cancel the attach + conversion to SQL 2000.

Thanks!

View 1 Replies View Related

Updating Structure Of DB?

Jul 18, 2001

I a DB deployed in the field at numerous customer sites. I have made changes
to the DB in my dev environment and now I need a way to update the DBs out in
the field to the new DB structure. Examples of updates are adding a new column
to a table, or adding some more code to a stored proc.

So what do I do ? I need to be able to email comething to the customer that
they can run that will make my recent updates to their DB structure. It can't
screw up their existing data either.

So what's the solution ? A big SQL script wil ALTER TABLE commands ?
How do I updated stored procs through a script ? I need help seriously.
What is the accepted industry standard method of updating deployed DBs
when a new version is released ? Please Please help.

Thanks in advance
Josh

View 1 Replies View Related

Syncing DB Structure

Jun 25, 2004

I have 2 databases with corresponding history databases.
As the system is under development, the 2 databases are changing in structure i.e the table structure(new tables as well as changes to existing ones) and indexes will change every week.
What is the best solution to replicate the changes on the history database.

Regards,

Ashutosh

View 1 Replies View Related

Table Structure

Jul 13, 2004

How can I get the structure of a table?
With MySql I can do it this way: DESC | DESCRIBE <tablename>

Thank you,

Dirk
Dirk.Ulrich@gmx.de

View 5 Replies View Related

Database Structure

Nov 1, 2004

This is a sort of database structure question that could I guess apply to any type of database...

Say I have 4 production lines and I want to log serial numbers on the start of every line and off the end of every line.

Is there any advantage in having a separate table for each Production Line and serial number logging point? (i.e. t_Line1Start, t_Line1End, t_Line2Start etc) or is it just the same as having one big table and having a LineNumber field and a StartEnd field?

Is SQL Server just as happy dealing with fewer transactions to several tables as it is to many transactions to one table?

View 1 Replies View Related

Structure Compare

Nov 19, 2007

Hi everybody
I want to compare structure of two database
how could I do it?

Thanks
Mokhtari

View 4 Replies View Related

Database Structure

Apr 9, 2008

Hi,
I have a question. I want to build a db for the following entities:
drivers
cars
companies

each driver nay has a car (NULL or carId)
each driver belongs to company
each car belongs to company

The db structure I built is:
car(carId, companyId)
driver(driverId, companyId, carId)
company(companyId)

The problem is that in this structure driver from company a can have a car from company b.

but this situation is not possible.. How can I prevent such situation??

tnx

View 14 Replies View Related

I Can Really Use Some Help.Table Structure

May 4, 2004

How can I setup a table structure for the diagram shown in the attached bitmap?

1) I need to create a product using labour and materials.
2) I need to create hardware using labour and materials.
3) I need to be able to include some hardware in some products.
4) Some materials in the product are made of a culmination of materials. E.G., Concrete is made of sand, stone, and cement.

Any suggestions?

So far I have :

USE NORTHWIND

Create Table tbProducts (
ProductID int NOT NULL,
Product varchar(50)
)

go

ALTER TABLE tbProducts
ADD CONSTRAINT tbProducts_pk PRIMARY KEY (ProductID)
GO

CREATE Table tbMaterials (
MaterialID int NOT NULL,
Material varchar (50)
)

GO

ALTER TABLE tbMaterials
ADD CONSTRAINT tbMaterials_pk PRIMARY KEY (MaterialID)
GO

CREATE Table tbLabour (
LabourCode char (2) NOT NULL,
Labour varchar(50)
)

GO
ALTER TABLE tbLabour
ADD CONSTRAINT tbLabour_pk PRIMARY KEY (LabourCode)
GO

CREATE Table tbProductMaterials (
fkProductID int NOT NULL,
fkMaterialID int NOT NULL,
Quantity Float NOT NULL
)

GO
ALTER TABLE tbProductMaterials
ADD CONSTRAINT tbProductMaterials_pk PRIMARY KEY (fkProductID, fkMaterialID)
GO

ALTER TABLE tbProductMaterials
ADD CONSTRAINT tbProductMaterils_fk FOREIGN KEY (fkMaterialID)
REFERENCES tbMaterials (MaterialID)

GO
ALTER TABLE tbProductMaterials
ADD CONSTRAINT tbProductMaterils_Product_fk FOREIGN KEY (fkProductID)
REFERENCES tbProducts (ProductID)
GO

CREATE TABLE tbProductLabour (
fkProductID int NOT NULL,
fkLabourCode char(2) NOT NULL,
Manpower int NULL DEFAULT(0),
Hours float NULL DEFAULT(0.0)
)

GO

ALTER TABLE tbProductLabour
ADD CONSTRAINT tbProductLabour_pk PRIMARY KEY (fkProductID, fkLabourCode)
GO

ALTER TABLE tbProductLabour
ADD CONSTRAINT tbProductLabour_fk FOREIGN KEY (fkLabourCode)
REFERENCES tbLabour (LabourCode)

GO

ALTER TABLE tbProductLabour
ADD CONSTRAINT tbProductLabour_Product_fk FOREIGN KEY (fkProductID)
REFERENCES tbProducts (ProductID)

GO

CREATE TABLE tbHardware (
HardwareID int NOT NULL,
Hardware varchar(50)
)

go

ALTER TABLE tbHardware
ADD CONSTRAINT tbHardware_pk PRIMARY KEY (HardwareID)
GO

CREATE Table tbHardwareMaterials (
fkHardwareID int NOT NULL,
fkMaterialID int NOT NULL,
Quantity Float NOT NULL
)

GO
ALTER TABLE tbHardwareMaterials
ADD CONSTRAINT tbHardwareMaterials_pk PRIMARY KEY (fkHardwareID, fkMaterialID)
GO

ALTER TABLE tbHardwareMaterials
ADD CONSTRAINT tbHardwareMaterials_fk FOREIGN KEY (fkMaterialID)
REFERENCES tbMaterials (MaterialID)

GO
ALTER TABLE tbHardwareMaterials
ADD CONSTRAINT tbHardwareMaterials_Hardware_fk FOREIGN KEY (fkHardwareID)
REFERENCES tbHardware (HardwareID)
GO

CREATE TABLE tbHardwareLabour (
fkHardwareID int NOT NULL,
fkLabourCode char(2) NOT NULL,
Manpower int NULL DEFAULT(0),
Hours float NULL DEFAULT(0.0)
)

GO

ALTER TABLE tbHardwareLabour
ADD CONSTRAINT tbHardwareLabour_pk PRIMARY KEY (fkHardwareID, fkLabourCode)
GO

ALTER TABLE tbHardwareLabour
ADD CONSTRAINT tbHardwareLabour_fk FOREIGN KEY (fkLabourCode)
REFERENCES tbLabour (LabourCode)

GO

ALTER TABLE tbHardwareLabour
ADD CONSTRAINT tbHardwareLabour_Product_fk FOREIGN KEY (fkHardwareID)
REFERENCES tbHardware (HardwareID)
GO

This table structure does not include anything about accounts and item 4) making materials from 1 or more other materials (E.G., Concrete).

Any hints how to incorporate these outstanding items?

Mike B

View 3 Replies View Related

How 2 Get The Table Structure Using T-SQL

Sep 11, 2006

How can I get my table structure (columns, their types, whether it s an Identity column) using T-SQL
pls
Thanks you

View 5 Replies View Related

Table Structure

Mar 10, 2007

Hi

How to write a query for table structure which looks like
SQL Design table.

thanks

asm

View 15 Replies View Related

Empty DB Structure

Apr 24, 2007

Hi,

I have to change collation for certain database. I think the only solution is to export or create through scripts the empty db structure (tables, PK & FK, defaults) and thereafter import data.
Since I don't have empty structure of this database I have to create somehow from the existing one which is with data, can somebody suggest how can I do this - take out the structure of the database from the db with data on it. I have 256 tables and I have to order them first with the dependencies of the tables?!

I will be really thankfull if somebody can help me on this issue.

Thnks,
sqlzagi

View 3 Replies View Related

Design Db Structure

Jun 19, 2007

hi all..
if im about to start a new database.. how do i start?
please advise on how do i start sketching a new db structure...
how do u guys set up ur db table struc.. any software to do this designing? any links or tutorial will be appriciated..

~~~Focus on problem, not solution ¯(º_o)/¯ ~~~

View 5 Replies View Related

Structure Table ..

Dec 13, 2007

I'm new in creating tables. and I was asked to create table(s) to capture information using SQL server 2005 (express edition)

please read below and share your thoughts and opinions o what's the efficient and best way to structure the tables.

I need to capture user's input daily and every hour, text can be from 20-100 characters, and my company is expecting to have 50,000+users in the future...Also the hrs's text can be stored for max 120 days, after that data would be deleted from the table(s)
An example is put all together, (not sure if it is the right and efficient way)

An example is put all together, (not sure if it is the rigth and effectient way) and if database would support that.. and if it will be fast enougth to search for data

(table structure) - (example with 3 users)
userId | timestap | message
1 12/12/2007 10:00 some message
1 12/12/2007 11:00 some message
1 12/12/2007 12:00 some message
1 12/12/2007 13:00 some message
1 12/12/2007 14:00 some message
2 12/12/2007 10:00 some message
2 12/12/2007 11:00 some message
3 12/12/2007 12:00 some message
3 12/12/2007 13:00 some message
3 12/12/2007 14:00 some message ...etc..

Thank you..

View 11 Replies View Related

Dirrectory Structure Of SQL

Jan 28, 2008

Hi all,

I just wanted to know the DIRECTORY structure for Ms SQL, and a brief explaination of each of the folders that are created.

For example:
Where is the actaul dbase stored, folder name
Where are logs stored
and Any other folders that are created and their useage?

I am also eager to know HOW WE EXTEND THE TABLESPACES and add DATAFILES using SQL commands?

Thanks and regards
Hunkydude

View 3 Replies View Related

Table Structure

Feb 28, 2008

Hi Guys,

I have another question.

Is there a query to display the structure of a certain table?
e.g. display the columns, data type,...etc.

TIA

View 7 Replies View Related

Backup Structure Only

Jul 13, 2007

Is there a way to back up the structure of the database only? i.e.backup without the data.I sometimes need to backup a database in one location and compare thestructure in another, can be a pain if there's 600MB of data that goeswith it.

View 6 Replies View Related

Table Structure In XML

Jul 20, 2005

Hi,I am new to XML and need a structure of table in XML format, which needsto be created as a table in another server.The Source and destinationservers are SQL Server 2000. Could anyone help me on this.Balaji.S.--Posted via http://dbforums.com

View 1 Replies View Related

Data Structure -- It's Been A While

Jul 20, 2005

Ok, I haven't been doing too much database work lately and my brain has gonesoft.I need some help with proper structure.My database is being used to track television shows.Any given show will be associated with at least one production company (callit ProdCo) but possibly up to 3 production companies (never more than that).I have:tblNetworkNetID (numeric, Identity)NetName (char, 50)tblShowDataBookingNum (numeric, Identity)BookingNumExt (numeric, Identity)Now, if I was just dealing with one ProdCo I would add it tblShowData as aforeign key from tblNetwork; no problem.How do I structure it so that I can combine 1 to 3 ProdCo's as one referencein tblShowData.I tried:tblNetworkComboID (numeric, Identity)Net1Net2Net3The problem is, obviously I can only join the FK from tblNetworks to one ofthe NetX fields in tblNetworkCombo.I don't quite know where to go from here. Any help would be appreciated.

View 3 Replies View Related

Replication Of Structure

Jun 19, 2006

Hello

I have a server with SQL Server 2005 and several machines with SQL Server Express.

I have my "master" DB on the SQL Server 2005 on which I have created a transactional publication (push)

Then all my other DB have a subscription to that publication.

I have different scenarios:

The structure needs to be replicated --> OK it works
The data of some table needs to be replicated --> OK it works

Whatever the data at the client, they are replaced after the replication
The data of some table needs to be partially replicated --> OK it works

Whatever the data at the client, they are replaced after the replication (only part of the server data is replicated with the filters)
Some data must not be replicated --> Don't know how to do this

Whatever the data at the client and at the server, I want them unchanged after the replication

Can you help me with this latest point ? I have tried with the filtering but it always remove all data from the client. What I need is: If I have 10 lines on my server, and 20 on my client, I only want the structure to be updated. But I still need my 10 lines on the server and my 20 on my client after the replication.

Thanks !

View 1 Replies View Related