Database Table Structure
Jan 29, 2008
Hi i have a student table and also an societies table, and a student can belong to as many societies as possible, for example student John Taylor belongs to the Football society, rugby societey and dance society, where as student Jim Jenkins belongs to the Football society, rugby society, arts society and graphics society.
My tables so far look like this;
STUDENTS; student_id, student_name
SOCIETIES; society_id, society_name
My question is how can i make the scenario above reflect in my table structure, would i need to create another table, if so what fields or columns should i place in this new table, thank you.
View 7 Replies
ADVERTISEMENT
Dec 16, 2007
Hello friends,
I am new to the SQL Server 2005 development.
From last 1 week or so, i have been facing very strange problem with my sql server 2005s database
which is configured and set on the hosting web server. Right now for managing my sql server 2005 database,
i am using an web based Control Panel developed by my hosting company.
Problem i am facing is that, whenever i try to modify (i.e. add new columns) tables in the database,
it gives me error saying that,
"There is already an object named 'PK_xxx_Temp' in the database. Could not create constraint. See previous errors.
Source: .Net SqlClient Data Provider".
where xxx is the table name.
I have done quite a bit research on the problem and have also searched on the net for solution but still
the problem persist.
Thanks in advance. Any help will be appreciated.
View 5 Replies
View Related
Dec 14, 2006
Hi,
I am creating reports for an application, that when installed can have various different table owners/qualifiers depending on how client created the DB. How can I create standard reports across all the DB without hardcoding the tablenames qualifier/owner in the dataset query? Again the table structure remain the same just the qualifiers may be different. Any help would be great.
--DCB
View 1 Replies
View Related
Mar 6, 2008
I have set up mirroring in high availability mode (principal, mirror, and witness).
After setting up mirroring, I needed to adjust the size of a column in one of my tables. I did so with the following command
ALTER TABLE OrderOptimizer.dbo.rCodes ALTER COLUMN CodeDescription VARCHAR(8000).
The column was initially VARCHAR(128)
When I look at the table structure it shows that the column is now VARCHAR(8000), however I can only enter 128 characters into the field.
I forced a failover to look at the table on the mirrored version. It too showed that the column was VARCHAR(8000).
If I open the table I can enter more than 128 characters into the field, however when I refresh, it is truncated to 128 characters,
Any help would be appreciated
View 4 Replies
View Related
Jun 1, 2015
I have an issue in my production , SQL server 2008 R2 , in Error log it is said “ An error occurred in dialog transmission Error : 9655 , State : 3 and Error : 9736 Severity : 16 State 0
I checked in sysmessages , it is said :” The transmission queue table structure in database is inconsistent. Possible database corruption “
So do you think I need to run DBCC checkdb ? Should I run it after working hours or it is fine within working hours ?
View 9 Replies
View Related
Oct 25, 2015
we have a table in our ERP database and we copy data from this table into another "stage" table on a nightly basis. is there a way to dynamically alter the schema of the stage table when the source table's structure is changed? in other words, if a new column is added to the source table, i would like to add the column to the stage table during the nightly refresh.
View 4 Replies
View Related
Aug 14, 2015
Below is my table structure. And I am inserting data from other temp table.
CREATE TABLE #revf (
[Cusip] [VARCHAR](50) NULL, [sponfID] [VARCHAR](max) NULL, GroupSeries [VARCHAR](max) NULL, [tran] [VARCHAR](max) NULL, [AddDate] [VARCHAR](max) NULL, [SetDate] [VARCHAR](max) NULL, [PoolNumber] [VARCHAR](max) NULL, [Aggregate] [VARCHAR](max) NULL, [Price] [VARCHAR](max) NULL, [NetAmount] [VARCHAR](max) NULL,
[Code] ....
Now in a next step I am deleting the records from #revf table. Please see the delete code below
DELETE
FROM #revf
WHERE fi_gnmaid IN (
SELECT DISTINCT r2.fi_gnmaid
FROM #revf r1, #revf r2
[Code] ...
I don't want to create this #rev table so that i can avoid the delete statement. But data should not affect. Can i rewrite the above as below:
SELECT [Cusip], [sponfID], GroupSeries, [tran], [AddDate], [SetDate], [PoolNumber], [Aggregate], [Price], [NetAmount], [Interest],
[Coupon], [TradeDate], [ReversalDate], [Description], [ImportDate], MAX([fi_gnmaid]) AS Fi_GNMAID, accounttype, [IgnoreFlag], [IgnoreReason], IncludeReversals, DatasetID, [DeloitteTaxComments], [ReconciliationID],
[Code] ....
If my above statement is wrong . Where i can improve here? And actually i am getting 4 rows difference.
View 5 Replies
View Related
Feb 24, 2006
Hello everyone,I have a webcontrol that uses database-structures alot, it uses the system tables in SQL to read column information from tables. To ease the load of the SQL server I have a property that stores this information in a cache and everything works fine.I am doing some research to find if there are anyway to get information from the SQL server that the structure from a table has changed.I want to know if a column or table has changed any values, like datatype, name, properties, etc.Any suggestions out there ?!
View 3 Replies
View Related
Jan 6, 2008
Hi,
I have a complicated sql server mobile database (.sdf) and need to create a SQL SERVER database with the same tables. How can I do it without scripting the whole thing? I thought of using the views.information_schema databases, but it is still a lot of coding.
thanks
View 3 Replies
View Related
Jan 20, 2007
Hi allI have two tables in SqlServer with Exactly Same Structure,I want to Copy all Records fromone of them to another one.I came across to "Insert....select..." statement But i have two problem
1) I don't know any thing about Columns name!!! i just know they have same structure and as far as i know , "Insert...select..." need the Column list to operate correctly, am i right?
2) these two table have One Prinary Key column with IDENTITY feature.
Any Help Greatly appriciated.Regards.
View 6 Replies
View Related
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
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
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
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
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
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
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
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
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
Feb 11, 2008
Hi,
I have one field "Status" with 15 different types of values. Like 1, 2, 3, 4 .. 15.
Now this Status field contains the values like this:
Id Status
1 1,2
2 3
3 1,2,3
4 13,15
What is the best way to organize this information in SQLServer table?
My idea is Create one table with the following volumns: ID, Status1, Status2, Status3, ... Status14, Status15.
Now the above data look like this:
Id Status1 Status2 Status3 ... Status13 Status14 Status15
1 1 1 0 0 0 0
2 0 0 1 0 0 0
3 1 1 1
4 0 0 0 1 0 1
So, In future If they add new column they can easily add column like status16.
And status table look like this
Status
StatusID Description
1 a
2 b
..
15
Thanks in advance
View 6 Replies
View Related
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
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
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
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
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
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
Jul 8, 2006
Is there a way I can copy my SQL 2000 database including procedures, views tec. the whole works but not the data in any table.
I want to share with a relative a copy of my application (web based) he has his own SQL 2000 server but I do not want to give my data just the database so the application will run and he can start inputting his own data.
How can I do this like a backup which has everything but with no data.
I tried scripting it using the EM but it did not give me everything just the system db's
Thanks
Xavier
View 3 Replies
View Related
Jan 11, 2008
I'm designing a web application that acts as business management software for many small autobody shops. This method was chosen because it creates a central point of administration for the software and I believe that a properly configured cluster of services will meet performance requirements. Using SQL Server 2k5 as my backend, I need to structure my DB(s) so that performance and size are kept to a minimum.
That being said, the first problem I encounter is in creating a structure that supports multiple businesses, each with their own unique dataset.
For example, each business will have work orders specific to their business. This necessitates a uniqueness constrained by businessID (hereafter titled "BizID") and WorkOrderID (WoID). The first solution I happened on was the simplest: A composite primary key composed of BizID (int) and WoID (int). The unfortunate consequence of this, as I soon realized, was that *every* table containing multiple business' data would need this composite key structure. The second problem with this method is that there is no such thing (to my knowledge) as a composite identity. I could identify WoID as the identity column, but it would not iterate WoID for BizID x properly if any other Biz added a work order to the table. (i.e. biz 1 adds three work orders (identitied 1, 2, 3), biz 2 adds one work order (would identity as 4) and Biz 1 adds another (would identity as 5), leaving us with 1, 2, 3, 4, 5 across two businesses, rather than 1, 2, 3, 4 for biz 1 and 1 for Biz 2)
The second idea I considered was one DB per business. I didn't consider that for very long, since I assumed size considerations would be blown out of the water if I had 50 business and 50 DBs. I could be wrong, as I am decidedly a novice at this. It does benefit from not putting all my eggs in one basket though and makes backup/recovery/troubleshooting much simpler.
The third idea I considered was one table per business. This suffers from the same issues as #2 above with not as many of the benefits.
Both ideas 2 and 3 would need a master table relating logins to the DBs they belong to, which would not be hard to implement at all, but the implications of using this method are unknown to me.
So quite obviously, I'm a novice at this. Perhaps I took a project on that was too big for my britches, but just getting out of college puts me in a good place to take a risk and I will learn a great deal from putting a complex (is it so?) system like this into action.
I appreciate any and all responses, thanks in advance for your replies!
-Devin
View 5 Replies
View Related
Jun 24, 2007
All,On my aspx page i have 2 sets of checkboxlists, each with a number of items and i am storing that info in my db. currently i have my table set up this way: InfoID--PK autoUserID-intCheckBoxList1ID-intCheckBoxList2ID-int So a user can select more than 1 item in each checkbox, so this table will contain multiple user-ids and each id from the respective checkboxes. My question is, is there a better way to store this data? I hope this is clear, any input would be greatly appreciated.Thank you.
View 2 Replies
View Related
Aug 30, 2007
Hi....I execute select statement on three tables and i get the following table:
ID
Value
Name
1
10
color
2
20
color
3
30
color
4
40
color
View 5 Replies
View Related
Oct 27, 2007
Hi,I have a table for storing my picture albums here it is:CREATE TABLE [UserAlbum] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [UserID] [int] default(0) , [AlbumName] [nvarchar] (100) NULL , [Audience] [int] default(0) , [Date_added] [smalldatetime] NULL , CONSTRAINT PK_CmmNet_userAlbum_ID PRIMARY KEY CLUSTERED ( ID )) ON [PRIMARY]GO
it has a field called "audience" as the name defines i want to choose who will see my albumsit is integer and as follows:0 value=everyone1=friends only2=private
as i started to write a query for it i came into this problem: for exampleif i write SELECT * FROM UserAlbum where audience=1 then only friends can see the infowhat happens to everyone with value of 0?when i set it to 0, friends should also be able to see the info because it is set for everybody
i noticed i should use "in cluase" like thisSELECT * FROM UserAlbum where audience in ('0','1') > so in this way both everyone and also friends can see the info
or maybe i should add 3 different fields for private,public,friends
it seems to be simple but at the same time harddoes anyone have a suggestion?
View 1 Replies
View Related
Apr 16, 2008
I'm a VB.net programmer working in an environment that doesn't have a true sql db administrator. We have a situation where we're thinking about adding a new database (db2) with the same Tables used in an existing database (db1). We were hoping to avoid this, but circumstances are forcing us to think about it. Are there any sql tools out there to maintain a dual-db like this? What are the main disadvantages to this?
View 3 Replies
View Related
Oct 27, 2000
I just started with SQL 7 and I would like to know how to print
out the structure of a table in SQL 7.
thanks in advance.
View 1 Replies
View Related
