Database Advice
Dec 8, 2007
Hi,
I recently contacted my hosting company's customer support about my databases not working - saying that I use sql express (which they support).
The guy recommended:
"I would suggest you to upgrade the db's to use mssql 2005."
"This
is because, sql express is built for development environment. When you
are in development environment, you are accessing everything with
administrator permission. However, in live hosting environment (when
there are differnet kind of permission restrictions), sql express often
failed on attaching database."
Does anyone have any opinion on that? Would it be best to change db's to use mssql 2005? How complicated/time consuming will it be to upgrade?
Thanks!
Jon
View 1 Replies
ADVERTISEMENT
Jun 4, 2007
I am thinking of doing a fake PC company site for my ASP project. so
what they will have is a chat, products with reviews, and users can
have "Buddies".So my DB so far (Tables):ProductPC - ID, Processor, RAM, HDD, Graphics, LCD ...ProductHardware
- ID, Title (Like "Intel C2D E6600"), Description, Price, Rating (0-5
stars, so integer), Category (CPU, HDD, Graphics)ProductSoftware - ID, Title (eg. "Adobe CS3"), Description, Price, Rating, Category (eg. Design, Programming)Reviews - ID, ProductID, Title, Content, DateChat - ID, TopicID, Title, Content, DateUsers - ID, Username, Password,The problem, how do i connect Reviews to the products since they are from diff tables.How do i get the "buddy" system workingChat i think its not as simple as thatBut i just need a simple ASP project, so no need to get too complex, but i still hope to learn as much.
View 12 Replies
View Related
Jun 17, 2007
Hi,Apologies if this has already been asked, but I couldnt find a thread that asked exactly what I wanted.Im making an administration panel for a site where you can change various settings, options, and categories that data can fall into. When editing, adding or deleting a record i COULD make a trip to the database every time, but this feels very inefficient as I understand that establishing the connection is usually the biggest performance hit when querying a database.An alternative plan is for me to simply record the changes made in the panel and have a "save" button. When this is clicked, ONE database connection would be opened and all the data would be saved/updated/deleted as necessary. However, this would involve several "for" loops while the connection is open.The question is; which method would you recommend and why? And does having several "for" loops while the connection is open nullify the advantage gained by only opening one connection?Any advice would be very much appreciated. Thank you
View 2 Replies
View Related
Dec 16, 2005
Hello,I am working on a web site which will use SQL 2005.I am planing my first SQL database and I am looking for advice.1. There will be two types of users: students and professors.2. Both users types will have login information. (Username, Password, AccessLevel) 3. The remaining information on students and professores is different. Student (Name, Email, Phone, ...) / Professor (Name, Email, Phone, Subjects, ...)4. Professors can publish documents. Each document has some info (Type, Title, Description, ...)My plan in this moment is to:A. Create the tables Students, Professors, Login and Documents.B. Students table would be connected to Login table. Professors table would be connected to Login table and Documents table.C. The field [Type] in documents table should include the type or should I create a table DocumentsTypes where I add codes for each type. I have seen this. What is the advantage?Can someone give me some advice?Thank you Very Much,Miguel
View 1 Replies
View Related
Oct 19, 2007
Hello,
I am creating a database where:
- I have a Blogs and Folders system.
- Use a common design so I can implement new systems in the future.
Users, Comments, Ratings, View, Tags and Categories are tables common to all systems, i.e., used by Posts and Files in Blogs and Folders.
- One Tag or Category can be associated to many Posts or Files.
- One Comment, View or Rating should be only associated to one Post or one File. I am missing this ... (1)
Relations between a File / Folder and Comments / Ratings / View / Tags / Categories are done using FilesRatings, FoldersViews, etc.
I am using UniqueIdentifier as Primary Keys.
I checked ASP.NET Membership tables, a few articles and few features in my project, such as renaming files with the GUID of their records.
I didn't decided yet for INT or UNIQUEIDENTIFIER.
I am looking for some feedback on the design of my database.
One thing I need to improve is mentioned in (1)
Thank You,
Miguel
My Database Script:
-- Users ...
create table dbo.Users
(
UserID uniqueidentifier not null
constraint PK_User primary key clustered,
[Name] nvarchar(200) not null,
Email nvarchar(200) null,
UpdatedDate datetime not null
)
-- Categories ...
create table dbo.Categories
(
CategoryID uniqueidentifier not null
constraint PK_Category primary key clustered,
[Name] nvarchar(100) not null
)
-- Comments ...
create table dbo.Comments
(
CommentID uniqueidentifier not null
constraint PK_Comment primary key clustered,
AuthorID uniqueidentifier not null,
Title nvarchar(400) null,
Body nvarchar(max) null,
UpdatedDate datetime not null,
constraint FK_Comments_Users
foreign key(AuthorID)
references dbo.Users(UserID)
)
-- Ratings ...
create table dbo.Ratings
(
RatingID uniqueidentifier not null
constraint PK_Rating primary key clustered,
AuthorID uniqueidentifier not null,
Value float not null,
constraint FK_Ratings_Users
foreign key(AuthorID)
references dbo.Users(UserID)
)
-- Tags ...
create table dbo.Tags
(
TagID uniqueidentifier not null
constraint PK_Tag primary key clustered,
[Name] nvarchar(100) not null
)
-- Views ...
create table dbo.Views
(
ViewID uniqueidentifier not null
constraint PK_View primary key clustered,
Ticket [datetime] not null
)
-- Blogs ...
create table dbo.Blogs
(
BlogID uniqueidentifier not null
constraint PK_Blog primary key clustered,
Title nvarchar(400) null,
Description nvarchar(2000) null,
CreatedDate datetime null
)
-- Posts ...
create table dbo.Posts
(
PostID uniqueidentifier not null
constraint PK_Post primary key clustered,
BlogID uniqueidentifier not null,
AuthorID uniqueidentifier not null,
Title nchar(1000) null,
Body nvarchar(max) null,
UpdatedDate datetime not null,
IsPublished bit not null,
constraint FK_Posts_Blogs
foreign key(BlogID)
references dbo.Blogs(BlogID)
on delete cascade,
constraint FK_Posts_Users
foreign key(AuthorID)
references dbo.Users(UserID)
on delete cascade
)
-- PostsCategories ...
create table dbo.PostsCategories
(
PostID uniqueidentifier not null,
CategoryID uniqueidentifier not null,
constraint PK_PostsCategories
primary key clustered (PostID, CategoryID),
constraint FK_PostsCategories_Posts
foreign key(PostID)
references dbo.Posts(PostID)
on delete cascade,
constraint FK_PostsCategories_Categories
foreign key(CategoryID)
references dbo.Categories(CategoryID)
)
-- PostsComments ...
create table dbo.PostsComments
(
PostID uniqueidentifier not null,
CommentID uniqueidentifier not null,
constraint PK_PostsComments
primary key clustered (PostID, CommentID),
constraint FK_PostsComments_Posts
foreign key(PostID)
references dbo.Posts(PostID)
on delete cascade,
constraint FK_PostsComments_Comments
foreign key(CommentID)
references dbo.Comments(CommentID)
on delete cascade
)
-- PostsRatings ...
create table dbo.PostsRatings
(
PostID uniqueidentifier not null,
RatingID uniqueidentifier not null,
constraint PK_PostsRatings
primary key clustered (PostID, RatingID),
constraint FK_PostsRatings_Posts
foreign key(PostID)
references dbo.Posts(PostID)
on delete cascade,
constraint FK_PostsRatings_Ratings
foreign key(RatingID)
references dbo.Ratings(RatingID)
on delete cascade
)
-- PostsTags ...
create table dbo.PostsTags
(
PostID uniqueidentifier not null,
TagID uniqueidentifier not null,
constraint PK_PostsTags
primary key clustered (PostID, TagID),
constraint FK_PostsTags_Posts
foreign key(PostID)
references dbo.Posts(PostID)
on delete cascade,
constraint FK_PostsTags_Tags
foreign key(TagID)
references dbo.Tags(TagID)
)
-- PostsViews ...
create table dbo.PostsViews
(
PostID uniqueidentifier not null,
ViewID uniqueidentifier not null,
constraint PK_PostsViews
primary key clustered (PostID, ViewID),
constraint FK_PostsViews_Posts
foreign key(PostID)
references dbo.Posts(PostID)
on delete cascade,
constraint FK_PostsViews_Views
foreign key(ViewID)
references dbo.Views(ViewID)
on delete cascade
)
-- Folders ...
create table dbo.Folders
(
FolderID uniqueidentifier not null
constraint PK_Folder primary key clustered,
[Name] nvarchar(100) null,
Description nvarchar(2000) null,
CreatedDate datetime not null,
URL nvarchar(400) not null
)
-- Files ...
create table dbo.Files
(
FileID uniqueidentifier not null
constraint PK_File primary key clustered,
FolderID uniqueidentifier not null,
AuthorID uniqueidentifier not null,
Title nvarchar(400) null,
Description nvarchar(2000) null,
[Name] nvarchar(100) not null,
URL nvarchar(400) not null,
UpdatedDate datetime not null,
IsPublished bit not null,
Type nvarchar(50) null,
constraint FK_Files_Folders
foreign key(FolderID)
references dbo.Folders(FolderID)
on delete cascade,
constraint FK_Files_Users
foreign key(AuthorID)
references dbo.Users(UserID)
on delete cascade
)
-- FilesCategories ...
create table dbo.FilesCategories
(
FileID uniqueidentifier not null,
CategoryID uniqueidentifier not null,
constraint PK_FilesCategories
primary key clustered (FileID, CategoryID),
constraint FK_FilesCategories_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesCategories_Categories
foreign key(CategoryID)
references dbo.Categories(CategoryID)
)
-- FilesComments ...
create table dbo.FilesComments
(
FileID uniqueidentifier not null,
CommentID uniqueidentifier not null,
constraint PK_FilesComments
primary key clustered (FileID, CommentID),
constraint FK_FilesComments_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesComments_Comments
foreign key(CommentID)
references dbo.Comments(CommentID)
on delete cascade
)
-- FilesRatings ...
create table dbo.FilesRatings
(
FileID uniqueidentifier not null,
RatingID uniqueidentifier not null,
constraint PK_FilesRatings
primary key clustered (FileID, RatingID),
constraint FK_FilesRatings_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesRatings_Ratings
foreign key(RatingID)
references dbo.Ratings(RatingID)
on delete cascade
)
-- FilesTags ...
create table dbo.FilesTags
(
FileID uniqueidentifier not null,
TagID uniqueidentifier not null,
constraint PK_FilesTags
primary key clustered (FileID, TagID),
constraint FK_FilesTags_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesTags_Tags
foreign key(TagID)
references dbo.Tags(TagID)
)
-- FilesViews ...
create table dbo.FilesViews
(
FileID uniqueidentifier not null,
ViewID uniqueidentifier not null,
constraint PK_FilesViews
primary key clustered (FileID, ViewID),
constraint FK_FilesViews_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesViews_Views
foreign key(ViewID)
references dbo.Views(ViewID)
on delete cascade
)
-- Run script
go
View 2 Replies
View Related
Feb 13, 2008
I'm at a new installation where there's no DBA at all, so, as a Coldfusion programmer, I'm now the DBA, LOL.
The main SQL2000 DB we use is approximately 100MB with about 7MB of free space and is allocated to expand by 10%.
I am adding a new, large table, about 60 columns with lots of variable-length unicode fields, mostly nvarchar. It's being used to track non-USA user-form information. Even though the DB is set to expand, I'm concerned that due to the potential size/volume of records, that the auto-expand could cause performance issues.
The SQL2000 server has plenty of room, about 49GB, so I'm wondering if I should expand the size of the current DB, or if the auto-expand feature will be ok.
At this point I'm not sure what the volume of the user-form records will be in the new table. It won't be a million records certainly, but I'm guessing it could climb to maybe 10-20,000 records.
If I should expand the DB, can I do this while the DB is still online?
thanks...
View 4 Replies
View Related
Jul 20, 2005
hi folks,i'm puzzled over this one, anyone with some solid db experience might beable to enlighten me here.i'm modelling a file system in a database as follows, and i can't figure outto cleanly implement an inheritance mechanism.i have a hierarchy of folders in an sql table. every folder has aparentFolderID, if this value is 0 then it means it's a root folder.then, in a 'files' table, every file has a parentFolderID to give it alocation in the structure. fairly basic.the hard part is that each file record has an attribute 'STYLE' that can beexplicitly specified, or inherited from it's parent folder, or it's parentsparent folder, or.. all the way back to the root.the 2 ways i've come up with representing it are:1) if the style is being inherited, enter a null value in the STYLE field.then to figure out what style applies to a file or folder, i trace backthrough it's parentFolderID records until i find a style attribute that isnot null.the good thing about this is that if i change the style that is applied tothe entire filesystem, it only takes one update.the bad thing is when i want to figure out what style applies to a file, ihave to traverse back through possibly several records to locate the folderthat actually specifies the style being inherited by the file.2) explicitly state all style values in each record.this is good for accessing the style of a file or folder because you get itstraight out first time from the db.the bad thing is if i update the entire file system, i might have severalhundred / thousand update sql statements to execute to update all the valuesin every folder and every file. nasty!thanks for any help, i'm really stumped with this and i'm thinking theremust be a more elegant way to implement inheritance.thankstim
View 2 Replies
View Related
May 1, 2006
Hi,
I.m trying to find the best practice for my solution.
This is the situation.
My application has two databases and 2 client interface. One with a windows forms having a database (sql server 2000) running in an office (on a static IP but on a slow connection). the second one is an web application running on the net with an online database. Both these databases have to have same information. Users can add records to both databases independently but these databases should be synchronize at a point. I hope the situation is cleare. Is there a standard way of doing this. If some one knows good article of how this can be done pls forward it to me.
Many thanks.
-VJ
View 11 Replies
View Related
Oct 21, 2007
Hello,
I am creating a database where:
- I have a Blogs and Folders system.
- Use a common design so I can implement new systems in the future.
Users, Comments, Ratings, View, Tags and Categories are tables common to all systems, i.e., used by Posts and Files in Blogs and Folders.
- One Tag or Category can be associated to many Posts or Files.
- One Comment, View or Rating should be only associated to one Post or one File. I am missing this ... (1)
Relations between a File / Folder and Comments / Ratings / View / Tags / Categories are done using FilesRatings, FoldersViews, etc.
I am using UniqueIdentifier as Primary Keys.
I checked ASP.NET Membership tables, a few articles and few features in my project, such as renaming files with the GUID of their records.
I didn't decided yet for INT or UNIQUEIDENTIFIER
I am looking for some feedback on the design of my database.
One thing I think need to improve is mentioned in (1)
But any advices to improve it would be great.
Thank You,
Miguel
My Database Script:
-- Users ...
create table dbo.Users
(
UserID uniqueidentifier not null
constraint PK_User primary key clustered,
[Name] nvarchar(200) not null,
Email nvarchar(200) null,
UpdatedDate datetime not null
)
-- Categories ...
create table dbo.Categories
(
CategoryID uniqueidentifier not null
constraint PK_Category primary key clustered,
[Name] nvarchar(100) not null
)
-- Comments ...
create table dbo.Comments
(
CommentID uniqueidentifier not null
constraint PK_Comment primary key clustered,
AuthorID uniqueidentifier not null,
Title nvarchar(400) null,
Body nvarchar(max) null,
UpdatedDate datetime not null,
constraint FK_Comments_Users
foreign key(AuthorID)
references dbo.Users(UserID)
)
-- Ratings ...
create table dbo.Ratings
(
RatingID uniqueidentifier not null
constraint PK_Rating primary key clustered,
AuthorID uniqueidentifier not null,
Value float not null,
constraint FK_Ratings_Users
foreign key(AuthorID)
references dbo.Users(UserID)
)
-- Tags ...
create table dbo.Tags
(
TagID uniqueidentifier not null
constraint PK_Tag primary key clustered,
[Name] nvarchar(100) not null
)
-- Views ...
create table dbo.Views
(
ViewID uniqueidentifier not null
constraint PK_View primary key clustered,
Ticket [datetime] not null
)
-- Blogs ...
create table dbo.Blogs
(
BlogID uniqueidentifier not null
constraint PK_Blog primary key clustered,
Title nvarchar(400) null,
Description nvarchar(2000) null,
CreatedDate datetime null
)
-- Posts ...
create table dbo.Posts
(
PostID uniqueidentifier not null
constraint PK_Post primary key clustered,
BlogID uniqueidentifier not null,
AuthorID uniqueidentifier not null,
Title nchar(1000) null,
Body nvarchar(max) null,
UpdatedDate datetime not null,
IsPublished bit not null,
constraint FK_Posts_Blogs
foreign key(BlogID)
references dbo.Blogs(BlogID)
on delete cascade,
constraint FK_Posts_Users
foreign key(AuthorID)
references dbo.Users(UserID)
on delete cascade
)
-- PostsCategories ...
create table dbo.PostsCategories
(
PostID uniqueidentifier not null,
CategoryID uniqueidentifier not null,
constraint PK_PostsCategories
primary key clustered (PostID, CategoryID),
constraint FK_PostsCategories_Posts
foreign key(PostID)
references dbo.Posts(PostID)
on delete cascade,
constraint FK_PostsCategories_Categories
foreign key(CategoryID)
references dbo.Categories(CategoryID)
)
-- PostsComments ...
create table dbo.PostsComments
(
PostID uniqueidentifier not null,
CommentID uniqueidentifier not null,
constraint PK_PostsComments
primary key clustered (PostID, CommentID),
constraint FK_PostsComments_Posts
foreign key(PostID)
references dbo.Posts(PostID)
on delete cascade,
constraint FK_PostsComments_Comments
foreign key(CommentID)
references dbo.Comments(CommentID)
on delete cascade
)
-- PostsRatings ...
create table dbo.PostsRatings
(
PostID uniqueidentifier not null,
RatingID uniqueidentifier not null,
constraint PK_PostsRatings
primary key clustered (PostID, RatingID),
constraint FK_PostsRatings_Posts
foreign key(PostID)
references dbo.Posts(PostID)
on delete cascade,
constraint FK_PostsRatings_Ratings
foreign key(RatingID)
references dbo.Ratings(RatingID)
on delete cascade
)
-- PostsTags ...
create table dbo.PostsTags
(
PostID uniqueidentifier not null,
TagID uniqueidentifier not null,
constraint PK_PostsTags
primary key clustered (PostID, TagID),
constraint FK_PostsTags_Posts
foreign key(PostID)
references dbo.Posts(PostID)
on delete cascade,
constraint FK_PostsTags_Tags
foreign key(TagID)
references dbo.Tags(TagID)
)
-- PostsViews ...
create table dbo.PostsViews
(
PostID uniqueidentifier not null,
ViewID uniqueidentifier not null,
constraint PK_PostsViews
primary key clustered (PostID, ViewID),
constraint FK_PostsViews_Posts
foreign key(PostID)
references dbo.Posts(PostID)
on delete cascade,
constraint FK_PostsViews_Views
foreign key(ViewID)
references dbo.Views(ViewID)
on delete cascade
)
-- Folders ...
create table dbo.Folders
(
FolderID uniqueidentifier not null
constraint PK_Folder primary key clustered,
[Name] nvarchar(100) null,
Description nvarchar(2000) null,
CreatedDate datetime not null,
URL nvarchar(400) not null
)
-- Files ...
create table dbo.Files
(
FileID uniqueidentifier not null
constraint PK_File primary key clustered,
FolderID uniqueidentifier not null,
AuthorID uniqueidentifier not null,
Title nvarchar(400) null,
Description nvarchar(2000) null,
[Name] nvarchar(100) not null,
URL nvarchar(400) not null,
UpdatedDate datetime not null,
IsPublished bit not null,
Type nvarchar(50) null,
constraint FK_Files_Folders
foreign key(FolderID)
references dbo.Folders(FolderID)
on delete cascade,
constraint FK_Files_Users
foreign key(AuthorID)
references dbo.Users(UserID)
on delete cascade
)
-- FilesCategories ...
create table dbo.FilesCategories
(
FileID uniqueidentifier not null,
CategoryID uniqueidentifier not null,
constraint PK_FilesCategories
primary key clustered (FileID, CategoryID),
constraint FK_FilesCategories_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesCategories_Categories
foreign key(CategoryID)
references dbo.Categories(CategoryID)
)
-- FilesComments ...
create table dbo.FilesComments
(
FileID uniqueidentifier not null,
CommentID uniqueidentifier not null,
constraint PK_FilesComments
primary key clustered (FileID, CommentID),
constraint FK_FilesComments_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesComments_Comments
foreign key(CommentID)
references dbo.Comments(CommentID)
on delete cascade
)
-- FilesRatings ...
create table dbo.FilesRatings
(
FileID uniqueidentifier not null,
RatingID uniqueidentifier not null,
constraint PK_FilesRatings
primary key clustered (FileID, RatingID),
constraint FK_FilesRatings_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesRatings_Ratings
foreign key(RatingID)
references dbo.Ratings(RatingID)
on delete cascade
)
-- FilesTags ...
create table dbo.FilesTags
(
FileID uniqueidentifier not null,
TagID uniqueidentifier not null,
constraint PK_FilesTags
primary key clustered (FileID, TagID),
constraint FK_FilesTags_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesTags_Tags
foreign key(TagID)
references dbo.Tags(TagID)
)
-- FilesViews ...
create table dbo.FilesViews
(
FileID uniqueidentifier not null,
ViewID uniqueidentifier not null,
constraint PK_FilesViews
primary key clustered (FileID, ViewID),
constraint FK_FilesViews_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesViews_Views
foreign key(ViewID)
references dbo.Views(ViewID)
on delete cascade
)
-- Run script
go
View 2 Replies
View Related
Feb 27, 2008
hi,
iam thinking of changing my ajax slideshow so that it gets the data from the databse. currently i am finding it hard to add text functianlity the way i want with the slide show.
what my query is, that if i to using a datalist can i add javasscript functionality to the data being retrived. for example, currently i have written some javascript so that a series of text is diplayed one after the other in a sequence from just one button click. so if im pulling data out of a databse can i still add this javascript functionality to it? i hope this makes sense, if it doesnt then i am willing to elaborate. please can any one offer any advice or examples or any suggestions on how i can do this. any help is much appricated as i am struggling to find a solution as i orinally wanted to be able to add this javascript functionality with the play button of the slide show but i couldnt find a solution.also i think its better to use some kind of database as i can use the editing funtions visual web developer offers
thank you
View 2 Replies
View Related
Mar 13, 2008
Hi All Professionals Programmers,
I would like to ask a question that is very important for me. The question is how can i create a flexible data base in which i m able to create the inner levels as much as i can.
Like i have a table building, then i have another child table floor, then the floor become parent and i have its child rooms, then the rooms become parent and i have its child floor tiles etc.
you can see i am going to inner dept, so i need a flexible database because its very costly and intimadting to change the database and every time create a new table and relationships.
Hope you have understood what i am going to say and need advice of professional and expert user to resolve it.
Any concise quality material like articles, white paper etc will also be suitable for me.
Thanks in Advance
View 3 Replies
View Related
May 22, 2008
Greetings,
Have an interesting issue in a database I'm trying to design and I'm trying to find a better way to setup the structure, if there is one. Going to generic example of my issue.
StudentList --- This table is the list of all College Students(with Primary Key of StudentID)
MajorsList --- This is a table listing all of the majors available at the College (PK of MajorID)
CourseList --- This is the list of all of the courses at the school (PK of ClassID)
This college allows students to take 1 or more majors, with the number of majors unknown.
-- Any number of students can take the same majors or different majors.
-- -- (aka 1 StudentID -> 1+ Majors & 1 Major -> 1+ StudentID's)
The majors all have different numbers of courses in them.
-- Many of the majors can have the same, or different courses in their lists.
-- -- (aka 1 Major -> 1+ Courses & 1 Course -> 1+ Majors)
I'm looking to see if their is a better way of tieing together the Students -> Majors and Majors -> Courses.
If done properlly, a single select and a few joins should bring up every class a student is taking for all their majors, or any other relationship to any of the three main tables primary keys.
Currently, to tie the three tables together, I have made two Interrum tables:
-- StudentMajors - - With only two columns
-- -- StudentID (ForeignKey tied to PrimaryKey StudentID in StudentList)
-- -- MajorID (FK tied to PrimaryKey MajorID in MajorList)
and
-- MajorCourses - - with only two columns
-- -- MajorID (FK tied to PrimaryKey MajorID in MajorList)
-- -- CourseID (FK tied to PrimaryKey CourseID in CourseList)
These tables give clear and definate ties between the tables, but my worry is that there is no primary key for these two tables, and no column in the tables is elledgeable for becoming PK, because, in this example the StudentMajors table can lis tthe studentID multiple times, each with a different MajorID. Sames goes with MajorCourses.
Is there a better structure method for reaching this same goal?
Additional, I don't know how to do a contraint that should be in place of:
-- In StudentMajors, for each value of studentID, there can be no duplicate values on MajorID
-- -- (same in MajorCourses tables in relations to classid's)
Any advice on how to do this constraint?
View 3 Replies
View Related
Jun 2, 2008
Hello,
I have an asp.net application which connects to SQL Server 2005 database.
One out of 15 times (approx) the applicaiton does not make connection to the database and an exception is thrown.
I am not sure how to debug this. Should I write some code which can make connections in a loop to test how much stress the sever can handle?
Kindly suggest some ideas. Thanks.
View 3 Replies
View Related
Aug 24, 2005
Dear GroupI'd be grateful if you can give me some advice on the following.An application I wrote uses an MSDE backend and I wonder whetherthere's a way (even for the system administrator) of not seeing ortracing stored procedure code, view and table designs?And I also wonder whether you can advise me on an installer thathandles MSDE and database setup during installation without too mucheffort but is still affordable < USD 1000.Any articles, resources, advice hints for these two topics are veryappreciated.Thank you very much for your help & efforts!Martin
View 3 Replies
View Related
Jan 30, 2008
Greetings!
I would like to create a database for keeping track of payroll data for employees where the supervisors (job coaches) on our workshop floor can use a Pocket PC device to record the hourly employee data on the fly. Then at the end of the day, the supervisor can place the device in a cradle of some sort and synch the newly entered data into the main database.
I'm guessing that SQL Server Compact edition would be perfect for this type of task? Is that correct? Can someone give me recommendations on how to go about setting this up? What should I use as the main database? SQL Server? Access? Any advice is appreciated!
View 1 Replies
View Related
May 23, 2006
Hi, i was after some advice on moving a SQL Server 2000 database from one server to another.
Usually i would do this by backing up the database on the original server the copying it accross the LAN to the new server and restoring it there. This database is 10Gb in size and copying it accross the LAN will take some time and i would like to minimise downtime if possible. The database is at a customer site where i am not responsible for the network or Hardware.
Thanks in advance for your help
Nick
View 4 Replies
View Related
Sep 21, 2007
Hello
Which is better and faster?? and WHY????Writing Select Statement with joins in Stored procedure, or creating view and calling it from stored procedure (select * from view)..
View 4 Replies
View Related
Jan 13, 2004
Hi
Dont laugh but...
I am not entirely ignorant to web technologies, and best practices but i am having a bit of a planning dylema.
My company has a well established SQL 2000 database with windows application which has been created by myself, what i am planning on doing is creating a web site, using asp.net and publishing some of the information, so that our clients may use it, and stop pestering us on the phone. what i would like to know is what would be the best way forward, obviously i don't want to show them all our information, and don't want to put 5Gb worth of data onto a ISP website. What would you suggest i do?
Thanks in advance
Brad
View 1 Replies
View Related
Jun 8, 2004
DECLARE @returnDay int
SELECT @returnDay = DatePart(day,GetDate())
If @returnDay = 8
BEGIN
select * from Hospitals left join Units ON Units.HospitalID = Hospitals.HospitalID where Units.HospitalID is null
RETURN
END
this is just a part of the procedure I am trying to create, I am getting hospitals that haven't submitted any data and wish to send them an email.
on the other hand I have two tables that have all the data for emailing to hospitals but are not linked to tables giving the list of hospitals
I have been advised to create a cursor(easier said then done) that will go through my list of records that need to receive an email
nothing going very well with that at the moment.
so I was hoping to see if somebody has any other suggestions for me.....
View 1 Replies
View Related
Apr 19, 2004
Hi,
I am trying to solve this procedure.
Let me try to explain it...I am getting DEGREEID from one of the SELECT query . I want to OUTPUT (ie , COUNT) from procedure,the number of departments with the degreeid, got from the above query.
With below procedure, Since an employee can have multiple DEGREEID , the cursor is giving OUPTUT ie, COUNT for the LAST Degreeid. Eventhough the previous DEGREEID dont have any DEPARTMENT...but only for the LAST DEGREEID...!
How can I solve this..... whether I can solve this with CURSOR or I have to use someother way...Please advice me !
DATA
-------
DEGREE_EARNED
---------------------
EMPID DEGREEID
------ ------------
201 12
201 3
201 250
202 3
202 10
203 17
DEPARTMENT
---------------
DEPID DEGREEID
------ ------------
10 1 12
111 250
111 12
121 3
121 12
121 250
------------------------------------
--------------------------------------------------------------------
DECLARE @vchid int
DECLARE testcursor CURSOR FOR
SELECT degree_id
FROM degree_earned WHERE emp_id= @empid
OPEN testcursor
FETCH NEXT FROM testcursor INTO @vchid
WHILE (@@FETCH_STATUS <> -1)
BEGIN
Select @outresult = COUNT(*)
from
department
where degree_id = @vchid
FETCH NEXT FROM testcursor INTO @vchid
END
--------------------------------------------------------------------
View 4 Replies
View Related
Mar 15, 2006
I tried to run a SQL script and i get the following message
quote:Error Message = Native SQL Error Code
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ''.
SELECT name, id, description FROM products WHERE id=4INSERT INTO ''admin_login'' (''login_id'', ''login_name'', ''password'', ''details'') VALUES (252,''neo2'',''newpas5'',''NA'')--
Error Code : 350
F:InetpubParexeldefault.ihtml
iSQL dbname="Parexel" ALIAS="prod" SQL="SELECT name, id, description FROM products WHERE id=4INSERT INTO 'admin_login' ('login_id', 'login_name', 'password', 'details') VALUES (252,'neo2','newpas5','NA')--"
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ''.SELECT name, id, description FROM products WHERE id=4INSERT INTO ''admin_login'' (''login_id'', ''login_name'', ''password'', ''details'') VALUES (252,''neo2'',''newpas5'',''NA'')--
Im new to SQL and i dont know what im doing
Also what does this mean
quote:iSQL dbname="admin" ALIAS="prod" SQL="SELECT name, id, description
What do SQL databases do and what would i need to do to enter data in to SQL such as a email address or cant that be done?
I think dbname is the database name ALIAS is that the table name?
im just trying to enter data in to my database database and i have no clue what im doing.
Iv read guides online but i get the above error 350
D
View 6 Replies
View Related
Jul 18, 2006
Hi all,
My first request for help here even if I read this site quite often and got tons of usueful information. Thanx all
I have an application (VB 6) calling store procedure on a SQLServer 2000 DB. One of the table gives me headache. I wrote a simple store procedure to insert a record into that table. If I call the store procedure from query manager it works perfect but if I call it from VB it looks like to work (return from the execute fine) but then I query the table for that record and it'll just take time and then return time out. I have to stop the VB application and then query it again then it'll return no record.
I suspect the table being locked somehow but I check inside the VB app code and that's the only place the table is called. Further more I have hundred of store procedures being used that way and they're all ok. I indexed the table , no use either...
I am just short of ideas how to debug this...I'll need your advice :)
thank you
View 9 Replies
View Related
Feb 6, 2006
Okay, given my newness to SQL, and the complexity of this query, I thought I'drun this by you for your opinion:SELECT DISTINCT a.WeekEnding, c.lastdate, c.numlate, b.totaldateFROM Accomplishment a LEFT OUTER JOIN(SELECT weekending, COUNT(weekending) AStotaldateFROM AccomplishmentWHERE (EmployeeID = 50)GROUP BY weekending) b ON a.WeekEnding =b.weekending LEFT OUTER JOIN(SELECT weekending, MAX(entrydate) ASlastdate, COUNT(weekending) AS numlateFROM accomplishmentWHERE employeeid = 50 AND entrydate >weekendingGROUP BY weekending) c ON a.WeekEnding =c.weekendingORDER BY a.WeekEndingWhat I'm trying to do is for each pay period find which ones the employeesubmitted a timesheet and which they were late (and if they were late, howmany of them). However, the query takes a good 5 seconds, and it seemsremoving the "entrydate > weekending" clause speeds things up to almostinstant, however it does ruin the count that I really want. No idea whythat makes such a difference..
View 4 Replies
View Related
Oct 28, 2006
hellooo
I have an Accounting system(vb.net 2003, SQL server 2000), every new year data is cleared, but i may use some data from previous years (such as liabilities)??
whats the best way to that ???
-Shall I create programmatically a new clone DB every year (new DB with same structure as previous year)
OR
-Shall I add a "year field" for tables in DB????
knowing that data will keep growing every year??????
whats the best solution, knowing that i dont want the end user of my application to do anything manually, such as creating DB ......
Thank you
View 14 Replies
View Related
Jul 11, 2007
I have a couple of files that I have that are comma seperated, and am looking for the best way to take those files, but them in a temp location to run some sql up against, and than update or insert a production sql database based on a SP i have written that takes a number of variable. I have played around with using the recordset destination and defining all the variables and than using a for each loop but seem to be stuck. Somewhat new to the whole programming field, and to be honest, seems a little intimidating with the little I know of the programming side. Just looking for some thoughts.
View 1 Replies
View Related
Aug 3, 2007
Hello,
I am new to the CE OS, SQL CE and mobile computing in general. I have been developing database applications using MS products ever since Windows 3.11 and Visual Basic 1.0. I have used MS Access and SQL Server since they were first released. But, I have never worked with Windows CE or any of the mobile OSs or with SQL CE or SQL Mobile.
We now have a project that requires us to develop a database application on handheld devices using the CE 4.2 and CE 5.0 OSs.We will be using CF 2.0, VS 2005 and SQL 2005 for our development environment.
My questions are:
1. Which version of mobile SQL will allow us to best develop for both the CE 4.2 and 5.0 OSs usinf VS 2005? I have done a lot of reading online and it's pretty confusing. There are quite a number of different versions out there. It seems some work with 4.2 and some with 5.0. Is there a version that will work for both?
2. Is SQL CE 3.0 (Sql 2005 Mobile) available for other than Laptop and Tablet use? When i finaly got to the download page for this version there was verbiage there that suggested it was not available for smart devices.
3. What is a good source of info to resolve these questions? I am using the MSDN areas for CE and SQL Mobile but haven't really found what I need to get started. Any suggestions on forums, books, articles, blogs, etc... would be greatly appreciated.
I know these are very broad questions but I want to get some advice from the experienced before going to far here.
View 10 Replies
View Related
Jun 1, 2007
I just want to ask for any good advice here ragarding my project. I'm planning to create a program that will use an sql server as a database. I have 3 branches and I need the branches to be updated always when they create any transaction or changes. The sql server will be installed in a PC with Windows Server 2003 and with a public IP. the question is can I access the sql server from other branches by using the public IP of the ServerPC thru the Internet? If yes, how? if no, is there a best way to do this updating method?
Thanks!
View 5 Replies
View Related
Mar 6, 2008
Hi,
I am totally new to SQL and need to ask some basic questions.
Can anybody tell me if SQL 2005 will run ok on Windows Server 2008 32bit and/or 64bit.
Also, we are currently planning to implement OCS 2007 and we are considering running it on Windows Server 2008 32bit with SQL 2005. Does anyone know if this combination is possibleand if not what implementation should we be looking at.
Thanks for your time,
Cheers,
Ian.
View 3 Replies
View Related
Aug 9, 2007
Hi everyone,
i wonder if any one can give me some advice.
here is my situation, i have a central database server that contains all the data, running in an intranet, the client application, which is a thick client containing most of the biz logics is installed on a laptop,
i m required to develop a solution so that when the client application is disconnect to the database server (e.g. the laptop is taken away somewhere) the application can still work and when the client application gets connect to the database server again the application will be able to synchronize changes with the database and update all the changes. and i m not allow to develop a web-base application
my initial thinking is to use merge replication, however, i m conerning with the performance on the laptop if i have to install sql server on the laptop.
is there any other approach that can still achieve the requirement and have a better performance?
thanks a lot in advance.
View 1 Replies
View Related
Jan 12, 2007
I'm creating a DB to track clients, programs, and client participation in the programs. They are service programs. A client can be in more than one program and a program can have more than one client.
Can someone give me an example of how they would layout the tables?
My guess is:
tblClient, ClientID
tblClientProgramLog, ProLogID, ClientID
tblProgramDetails, ProDetailID, ProLogID
tblPrograms, ProgramID, ProDetailID
I appreciate any suggestions,
View 4 Replies
View Related
Feb 27, 2007
Hello,Consider I have a String:Dim MyString As String = "Hello"or an Integer:Dim MyInteger As Integer = 100or a class which its properties:Dim MyClass As New MyCustomClass
MyClass.Property1 = "Hello"
MyClass.Property2 = Unit.Pixel(100)
MyClass.Property3 = 100Or even a control:Dim MyLabel As Label
MyLabel.Id = "MyLabel"
MyLabel.CssClass = "MyLabelCssClass" Is there a way to save a String, an Integer, a Boolean, a Class, a Control in an SQL database?Something like: Define something (Integer, String, Class, Control, etc) Save in SQL 2005 Database Later in code Retrive from database given its IDIs this possible?How should I do this?What type of SQL 2005 table field should be used to store the information?Thanks,Miguel
View 1 Replies
View Related
Mar 28, 2007
Hello,I am creating a simple blog system using SQL 2005.I have a Blog table:[BlogId] > PostId (PK), BlogTitle, ...And a Posts table[Posts] > PostId (PK), BlogId (FK), PostContent, PostLabels, ...PostLabels would have the following format:Label1,Label2,Label3, etc ...I will need to perform 3 actions:1. Get all posts in blog2. Get all labels in a post3. Get all unique existing labels in all posts in a blog and make a list.I am not sure if my approach of using a simple labels column in my Posts table is a good idea.So my other idea would be to add two more tables:[BlogLabels] > BlogLabelId (PK), BlogId (FK), LabelName ...[LabelsInPosts] > BlogLabelId (PK), PostId (PK)So my idea is:1. When creating a post one of the parameters would be a comma separating string with all labels for the post. Inside SQL Procedure I will need to loop through each label and check if it exists in BlogLabels. If not then I added it. For each label I add a records in LabelsInPosts. How to create this loop? Am I thinking this right?2. To get a list of all labels in a blog I would need to go to BlogLabels and get all labels which are related with posts in LabelsInPosts. Those posts must be only the ones that are related with my given BlogId. Grrr, this is getting really confusing for me. Is this possible to to? How?Please, give me some advice about all this.Thanks,Miguel
View 1 Replies
View Related
