Your Professional Advice Please - Design
May 18, 2006
Hi All, I have read MANY posts on how to track changes to data over
time
It appears there are two points of view
1. Each record supports a Change Indicator flag to
indicate the current record
(would this be EVERY table?)
2. Each table is duplicated as an archive table and
triggers are used to update archive
Can someone give me some guidance based on REAL world experience
which works best for them?
My scenario - I have insurance policies and must track history as
policies are updated by customer service reps.
Imagine many tables Policy>LifePol>LifePolRiders[color=blue]
>AccidentPol >etc...
>DIPol>DIPolRiders[/color]
To me the archive table scenario does not seem scalable at all....some
guidance on design would be aprreciated...Thanks!!!
View 2 Replies
ADVERTISEMENT
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
Jul 9, 2004
I'm trying to design a database that handles Clients, Cases, Individual and Group Sessions. My problem is that a client can have individual sessions and belong to more than one group at the same time, so I have a many-to-many relationship to deal with. Also I'm trying to design it so that I can have a form that when a group is selected from a drop down it shows all clients assigned to that group and will let me enter new session data for them.
Just looking for some advice on how to handle the relationships.
Maybe someone could show me how they see the relationships working.
My take is that the session is linked to the case not the client, I could be thinking incorrectly?
Thank you,
tblClient
tblClientCase
tblCaseSessionLog
tblClientCaseGroupLink
tblGroups
View 3 Replies
View Related
Jun 3, 2004
I have an construction estimation system, and I want to develop a project management system. I will be using the same database because there are shared tables. My question is this, critical data tables are considered tables with dollar values and these tables should not be shared across the whole company. I do however need information from these tables, such as product and quantity of the product for a given project. When an estimate becomes a project it is assigned a project number. At this point I thought of Copying the required data from the estimate side to the project side. This would result in duplicate data in a sence but the tables will be referenced from two standalone front end applications. Should I copy the data from one table to another, or create new "views" to the estimate tables for the project management portion.
What would be the best solution to this problem? I find in some circumstances, a new table is required because additional data will be saved on the "Project Management" side, but not in all cases.
Mike B
View 2 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
Nov 27, 2007
Hello Everyone. Im sorry for this urgent post, but have critical issue that needs a solution quick. So for my issue. I am adjusting our sales order tables to handle a couple different scenarios. Currently we have 2 tables for sales orders
SALESORDERS
------------
SORDERNBR int PK,
{ Addtl Header Columns... }
SALESORDERDETAILS
-------------------
SODETAILID int,
SORDERNBR int FK,
PN varchar,
SN varchar(25),
{ Addtl Detail Columns ... }
Currently the sales order line item is serial number specific. I need to change the tables to be able to handle different requests like :
Line Item Request ( PN, QTY )
Line Item Request ( SN )
Line Item Request ( PN, GRADE, QTY )
ETC.
I am thinking i need to create a new table to hold the specifics for a particular line item. Maybe like this :
SALESORDERSPECS
----------------
SOSPECID int,
SODETAILID int FK,
SPECTYPE varchar, IE : SN, PN, GRADE. { one value per row }
SPECVALUE varchar IE : GRADE A
Im thinking i would need to rename the SALESORDERDETAILS table to SALESORDERITEMS. SALESORDERITEMS would just contain header info like
SalePrice,
Warranty,
Etc...
Then rename SALESORDERSPECS to SALESORDERDETAILS...
Anyone understand what im trying to do? If you need more info please ask. You can also get a hold of me through IM.
Thanks!
JayDial
JP Boodhoo is my idol.
View 3 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
Oct 22, 2007
Hi all-
I have a schema that is mostly working, but I was wondering if some of you with more experience than I might give me some constructive criticism on my methodology.
Basically, I have a single table that stores data for many records. Each record has a variable number of fields, each of which can be a different data type. Later, queries will pull filtered subsets of data from the table, and do calculations on specific fields. In my implementation, the fields for a record are bound together by the datagroup (uniqueidentifier) column in the LotsOData table, the field name is defined by the dataname column, and the field value is stored in the datavalue column, which is type sql_variant.
One problem I had, and I'm not able to reliably replicate, is that the more complicated queries sometimes raise casting errors on the sql_variant column, even when the data is absolutely correct. I've been able to avoid this case by pre-selecting some of the subqueries into temporary tables first, then joining on the temp tables in the main query, but that seems horribly inefficient.
I've included a sample table, data, and query to demonstrate my basic solution. I was wondering if anybody could provide some insight on a better way of designing a solution for this scenario.
Thanks!
-Eric.
PS: bonus points if you have any insight at all on the casting error I mentioned!!
-- create tablecreate table LotsOData( pk int identity, dataname nvarchar(16) not null, datagroup uniqueidentifier, datavalue sql_variant);-- lot of insertsdeclare @group_a uniqueidentifier, @group_b uniqueidentifier, @group_c uniqueidentifier;set @group_a = newid();set @group_b = newid();set @group_c = newid();insert into LotsOData (dataname, datagroup, datavalue)select 'some_int', @group_a, 1union all select 'some_int', @group_b, 2union all select 'some_int', @group_c, 3insert into LotsOData (dataname, datagroup, datavalue)select 'some_char', @group_a, 'a'union all select 'some_char', @group_b, 'b'union all select 'some_char', @group_c, 'c'insert into LotsOData (dataname, datagroup, datavalue)select 'some_string', @group_a, 'abc'union all select 'some_string', @group_b, '!@#'union all select 'some_string', @group_c, 'xyz'insert into LotsOData (dataname, datagroup, datavalue)select 'some_float', @group_a, 1.23union all select 'some_float', @group_b, 2.34union all select 'some_float', @group_c, 3.45insert into LotsOData (dataname, datagroup, datavalue)select 'some_datetime', @group_a, cast('01/01/2001 01:00:00' as datetime)union all select 'some_datetime', @group_b, getdate()union all select 'some_datetime', @group_c, cast('01/01/2009 01:00:00' as datetime)-- do some big ugly query:select cast(a.datavalue as datetime) as datatime_data, cast(b.datavalue as int) as int_data, cast(c.datavalue as char(1)) as char_data, cast(d.datavalue as nvarchar(max)) as string_data, cast(e.datavalue as float) as float_data, cast(b.datavalue as int) * cast(e.datavalue as float) as calc_datafrom ( select datavalue, datagroup from LotsOData where dataname = 'some_datetime' ) a inner join ( select datavalue, datagroup from LotsOData where dataname = 'some_int' ) b on b.datagroup = a.datagroup inner join ( select datavalue, datagroup from LotsOData where dataname = 'some_char' ) c on c.datagroup = a.datagroup inner join ( select datavalue, datagroup from LotsOData where dataname = 'some_string' ) d on d.datagroup = a.datagroup inner join ( select datavalue, datagroup from LotsOData where dataname = 'some_float' ) e on e.datagroup = a.datagroupwhere cast(a.datavalue as datetime) between '01/01/2006' and '01/01/2008';
View 6 Replies
View Related
Jan 24, 2008
Any design suggestions for the best way to architect this report using SQL Reporting Services 2005 are appreciated!
My website features a catalog of roughly 50,000 items, each of which may be appear in a list of search results or in a detailed view. There are counters on the pages that update totals for such appearances and track other item-specific information in several tables in a SQL database. The catalog of items changes frequently, so the list of item IDs is never exactly the same from month to month.
I've been asked to produce a monthly report of this data for each of the items in the catalog, with reports for the current and previous months (for many years) accessible at all times. Some -- but not all -- items are useful for one purpose or another and so can be considered as belonging to a group of items. Although I have not yet been asked to create a report that aggregates the values for all Group members into a single report for that Group, I can clearly see it would be valuable and will be requested soon.
To ensure the report captures the data for an entire month, it must be run at the very end of each month. That means I will need to run the report using a Schedule that kicks off the process at 12:01am every 1st of the month. The report must be processed and stored for later retrieval and rendering on demand.
Considering the number of items and the indefinite length of time the report data must be retained, my question is really what's the best way to set all this up?
Should I create a report for each item separately? That would mean the scheduled task would have to somehow discover the current list of item IDs (which is available via query from the database) and create and process (but not render) a report for each (passing the item ID as a report parameter?), adding it to the report history. Although each report would be small take only a short time to run, overall that seems like it would take a long time to run and create a huge number of reports to store each month.
Or should I create a single 'master' report that contains all the data for every item for the month, and then use the item ID as a filter on the data when it is rendered? While that means only one report is created each month and added to the history, it would be a much larger report and take much longer to run (with more potential for timeouts and errors to scuttle the whole report). It also means all the data for the entire report has to be loaded every time the report is rendered, even though only 1/50,000 of the data (the data for 1 of the 50,000 items) will actually be viewed with any given rendering. But that would seem overly cumbersome, slow, and wastefully band-width intensive.
Any alternatives, suggestions, considerations, etc. -- all welcome!
Thanks
BillB
View 2 Replies
View Related
Jul 23, 2004
I need to create a text file using information from SQL tables/views in the following format...Can anyone recommend a direction or procedure to look into, i.e, sql script, custom dts, etc. The items in parentheses identify specific portions of the text file.
(01)
101081,84423,customer ,072304,customer ,11310 Via Playa De Cortes , ,San Diego ,CA,92124,
(02) 6 ,1 , , , , ,22 ,1 ,0.00 ,160.46 ,160.46 ,0.00 , , , , , , , , ,1,1
(03)B130907540,5.41 ,1
(03)B130907550,5.41 ,1
(03)B130907560,5.41 ,1
(03)B130907570,6.04 ,1
(03)B065007550,1.72 ,2
(03)B065007560,1.72 ,6
(03)B519926530,4.66 ,13
(03)B519926550,4.66 ,12
(03)B560911200,2.14 ,1
(03)B560912500,2.14 ,1
(03)B095305750,3.65 ,1
View 5 Replies
View Related
Jul 23, 2005
Hi,I have two tables Table A and B, below with some dummy data...Table A (contains specific unique settings that can be requested)Id, SettingName1, weight2, lengthTable B (contains the setting values, here 3 values relate to weightand 1 to length)Id, Brand, SettingValue1, A, 1001, B, 2001, null, 3002, null, 5.3(There is also a list of Brands available in another table). No primarykeys / referential integrity has been setup yet.Basically depending upon the Brand requested a different setting valuewill be present. If a particular brand is not present (signified by anull in the Brand column in table B), then a default value will beused.Therefore if I request the weight and pass through a Brand of A, I willget 100If I request the weight but do not pass through a brand (i.e. null) Iwill get 300.My question is, what kind of integrity can I apply to avoid the userspecifying duplicate Ids and Brands in table B. I cannot apply acomposite key on these two fields as a null is present. Table B willprobably contain about 50 rows and probably 10 of them will be brandspecific. The reason its done like this is in the calling client code Iwant to call some function e.g.getsetting(weight) .... result = 300Or if it is brand specificgetsetting(weight,A) ..... result = 100Any advice on integrity or table restructuring would be greatlyappreciated. Its sql 2000 sp3.Thanksbrad
View 9 Replies
View Related
Jul 6, 2006
Hello all,
I have recently been task with rewriting a database that holds large volumes of data, whilst ensuring that query can be run in optimal time. Having never really delved into this sort of thing before, I hoped you guys might be able to offer some advice and guidance.
The design I have inherited is based around 2 main tables:
[captured_traps]
[id] [int] IDENTITY (1, 1) NOT NULL
[snmp_version] [int] NULL
[community_name] [varchar] (255)
[packet_type] [varchar] (50)
[oid] [varchar] (500)
[source_ip] [varchar] (15)
[generic] [int] NULL
[specific] [int] NULL
[time_stamp] [varchar] (15)
[trap_entered] [datetime] NULL
[status] [int] NULL
[captured_varbinds]
[id] [int] IDENTITY (1, 1) NOT NULL
[captured_trap_id] [int] NOT NULL
[varbind_oid] [varchar] (500)
[varbind_text] [varchar (500)
The relationship between the two tables is on the "captured_traps (id)" to "captured_varbinds (captured_trap_id)". Currently the "captured_traps" table contains around 350 million rows, the "captured_varbinds" table contains around 900 million rows.
Now as you can probably gather this model runs like a....well it sort of hobbles more than runs hence the need to redesign.
My current thoughts on this are:
- Normalising all varchars - there is alot of duplicate values in most of the varchar fields.
- Full Text Indexing
However beyond that I am not sure which route to go down. After googling for most of today I have come across a number of "solutions" however I do not want to go steaming down the track of one of these to discover that it is fatally flawed somewhere.
View 6 Replies
View Related
May 24, 2001
Hi all,
I think most of the visitors here are experts of SQL 7.0. But I am a absolutely new one. So I may ask some very foolish questions.
I plan to learn how to use MS SQL 7.0 recently, but I don't know how to begin, can anybody here give me some good experience or material(websites) on learning SQL 7.0?
Also, I want to install SQL 7.0 in my home PC. should I install it with Win 2000 professional or Server version?
Thank you.
Henry
View 2 Replies
View Related
Oct 11, 2002
Which Edition of SQL SERVER 7 and SQL SERVER 2000 can be run on Windows XP Professional? I'm talking about the server itself not the client tools.
Thank you for any help.
View 2 Replies
View Related
Mar 4, 2006
Hi All
I am trying to install sql server on my xp machine. When I run the install it says it can't validate the product key. I have all legit copies of the software. I did a search on the web and i found something about problems in NT, but nothing for XP. Any ideas?
thanks!
View 3 Replies
View Related
Jul 27, 2005
Dear all,I have XP pro as Operating system.I installed Webmatrix, downloaded SQL 2000 desktop (sql2ksp3), run the command prompt in the directory where dir MSDE of sql2ksp3 was as so: setup.exe SAPWD=hello INSTANCENAME=RMSI lat the installation ran and I got msg that installation ran successfully. So far, so good. From WebMatrix, I tried to add a database connection within a project. When I select SQL-database and click on OK, I get the error message that SQL server administration requires SQL client installed on my machine. In Control Panel - AddRemove Programs, I see indeed that there is no MSSQLserver or client mentionned.I check also the services. The only one I have is SQLADHelper that starts manually. I must have to do with XP security, right... Can someone please please help me. I am a newbie and only want to learn ASP and VB programming via Matrix and Visual Studio IDE.Thanks in advance!Christiane
View 1 Replies
View Related
Jul 3, 2001
Hi,
I am looking at installing SQL Server 7 on my PC which runs Windows 2000 Professional. Can anyone give me some advice before I do the installation of SQL Server 7. Any tips or any problems I might encounter.
Regards
Guy
View 1 Replies
View Related
Jun 28, 2000
Currently, I have the client side of SQL Server 6.5 (Enteprise Manager, Security Manager, Books Online, etc.) installed and running under Windows NT 4.0 Workstation. When I attempt to install the same package under Windows 2000 Professional I get the following message - This program does not run correctly on this version of windows. To continue, click Run Program. Has anyone sucessfully installed this under Windows 2000 Professional? Are there any patches or service packs to apply? Thanks.
View 1 Replies
View Related
Oct 4, 2004
Hi there,
We are currently developing a E-Com research site where subscribed members can conduct market research. The web site is developed and ready to go, however we are undecided what is required on the Database side and could do with some of your expert help.
Users will be conducting research on our MS SQL database. The database holds every product sale made by a select number of consumer good companies. The database holds 12 million records at any one time, however overall the database is very static and is not updated online. We intend to only store 1 months data, therefore on a weekly basis we delete old & add new records.
We are offering three categories of searches on the DB.
1. By Seller
2. By Category (i.e. Hair Care, Laundry etc)
3. By Sale.
The first two searches are not performance intensive as they are compiled and stored in the database - therefore no need for calculation online. Our main concern is the 3rd report. This report is where we allow the user to search for a specified search string. Therefore in the main table that holds all the sales, we have a full-text index for the field "Product Description".
What we are unclear on is the Capacity requirements regarding providing such functionality. I.e What server do we require?
We have researched many articles and have absorbed all the information regarding multiple disk controllers, lots of memory etc.. etc.. However we still have not been able to obtain an idea on what Server would serve our purposes. For example: would the following server do the job?
2 x 2.4Ghz Hyper-threading Xeon CPUs with 512Kb Cache and 400MHz System Bus
2GB Samsung RAMBUS 800-45 Memory (RIMMS / RDRAM)
A lot is determined by the usage, i.e. how many queries are executed per minute etc. At present we do not have a clear idea on the number of users. However we are not truely concerned about this as the 3rd report needs to execute and return within a short period of time. That is our main concern.
Therefore our question is as follows: If you were going to host a database that consisted of 12 million records & full-text indexing which had to be queried in a few seconds, what would you purchase?
We ask you to consider that funds are extremely tight and although the new latest server may do the job, it is most likely out of our budget. We are looking for the most cost effective solution but still statisfying the purpose.
Kind regards
Angus
View 1 Replies
View Related
Jun 4, 2007
I'm sure this problem borders on retarded for most readers of this form, but here it is:
I have table with contest and another table with user ratings on those contests.
Contests: ContestID, CreatorUserID, Title
Ratings: ContestID, UserID, Rating
If I
SELECT Contests.*, Ratings.*
FROM Contests INNER JOIN Ratings ON Contests.ContestID = Ratings.ContestID
WHERE (Ratings.UserID = '1')
This only returns the contests that User 1 has rated. But I need all contests. If the user hasn't rated one, I want the result set to show Rating = NULL. The problem is that if a user hasn't rated a contest, that ContestID does not exist in the Ratings table.
Can someone please show me how to solve this?
Thanks!
Gavin
View 2 Replies
View Related
Sep 24, 2007
Occasionally, when I try to edit a package I get an error. Visual studio is busy found VS hanged.
It is specific to my machine or a common problem.
View 4 Replies
View Related
Jul 20, 2005
I have a copy of MS Visual Studio 2002 Professional ACADEMIC which Iunderstand is the same as the regular Professional version with theaddition of a student CDROM. The installation program on the firstCDROM only copies the MSDE to the harddrive and the copied setup filehas to then be manually executed to complete the installation.Are the SQL tools such the SQL Enterprise Manager and MS Queryincluded in the professional version? If so, how can I install thesetools? The course that I am taking uses these tools to study SQL. Ifnot, how is the MSDE to be used?
View 4 Replies
View Related
Jun 19, 2006
Just curious if anyone out there has had success running a Distributed Transaction on Windows 2000 Professional SP4 lately?
I have come to the conclusion that is is virutally impossible. I am running MSDE on a Win2k Pro machine and trying to run a distributed transaction via linked server and am getting the
"The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction."
error. Yes I have been scouring the web looking for answers and have implemented pretty much everything I have run accross... and Yes the MSDTC service is started on both machines... BTW: If I run the procedure on XP it works great!!!
I am wondering if anyone even uses Win2k Pro anymore let alone trying to run a distributed tarnsaction on the darn thing.
If anyone out there has it running let me know I would love to chat with you for a minute or two.
View 3 Replies
View Related
Dec 6, 2006
Hello all,
This is my first time posting, so be gentle as I'm not exactly sure what the protocol here is regarding posting errors.
I'm encountering an issue with an installation of SQL 2005 Express (SQLEXPR_ADV.EXE) on a Windows 2000 Professional SP4 machine (Intel P4 1.8 Ghz). The following error is what I receive:
Setup.exe - Entry point not found:
The procedure entry point _create_local could not be located in the dynamic link library mscvr80.dll
< OK >
The following is what I see in the error log (C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFiles):
SQLSetup0007_SQL2005TEST_Core.log
Microsoft SQL Server 2005 Setup beginning at Wed Dec 06 11:28:08 2006
Process ID : 452
c:98b179fcf692887ac3setup.exe Version: 2005.90.2047.0
Running: LoadResourcesAction at: 2006/11/6 11:28:7
Complete: LoadResourcesAction at: 2006/11/6 11:28:7, returned true
Running: ParseBootstrapOptionsAction at: 2006/11/6 11:28:7
Loaded DLL:c:98b179fcf692887ac3xmlrw.dll Version:2.0.3609.0
Complete: ParseBootstrapOptionsAction at: 2006/11/6 11:28:8, returned true
Running: ValidateWinNTAction at: 2006/11/6 11:28:8
Complete: ValidateWinNTAction at: 2006/11/6 11:28:8, returned true
Running: ValidateMinOSAction at: 2006/11/6 11:28:8
Complete: ValidateMinOSAction at: 2006/11/6 11:28:8, returned true
Running: PerformSCCAction at: 2006/11/6 11:28:8
Complete: PerformSCCAction at: 2006/11/6 11:28:8, returned true
Running: ActivateLoggingAction at: 2006/11/6 11:28:8
Complete: ActivateLoggingAction at: 2006/11/6 11:28:8, returned true
Running: DetectPatchedBootstrapAction at: 2006/11/6 11:28:8
Complete: DetectPatchedBootstrapAction at: 2006/11/6 11:28:8, returned true
Action "LaunchPatchedBootstrapAction" will be skipped due to the following restrictions:
Condition "EventCondition: __STP_LaunchPatchedBootstrap__452" returned false.
Running: PerformSCCAction2 at: 2006/11/6 11:28:8
Loaded DLL:C:WINNTsystem32msi.dll Version:3.1.4000.2435
Loaded DLL:C:WINNTsystem32msi.dll Version:3.1.4000.2435
Complete: PerformSCCAction2 at: 2006/11/6 11:28:8, returned true
Running: PerformDotNetCheck at: 2006/11/6 11:28:8
Complete: PerformDotNetCheck at: 2006/11/6 11:28:8, returned true
Running: ComponentUpdateAction at: 2006/11/6 11:28:8
Complete: ComponentUpdateAction at: 2006/11/6 11:45:55, returned true
Running: DetectLocalBootstrapAction at: 2006/11/6 11:45:55
Complete: DetectLocalBootstrapAction at: 2006/11/6 11:45:55, returned true
Running: LaunchLocalBootstrapAction at: 2006/11/6 11:45:55
Error: Action "LaunchLocalBootstrapAction" threw an exception during execution. Error information reported during run:
"c:Program FilesMicrosoft SQL Server90Setup Bootstrapsetup.exe" finished and returned: 128
Aborting queue processing as nested installer has completed
Message pump returning: 128
Notice the time jump; I did this to make sure I was looking at the right log file.
Any help is greatly appreciated as I'll be monitoring this thread closely. Searches of my error show that other people have received it as well, although no resolution has ever been forthcoming. Thanks in advance.
View 3 Replies
View Related
Nov 17, 2006
We are looking for some professional, low-cost, and easily configurable CMS tools that work hand-in-hand with SQL Server Express. Any come to mind?
View 1 Replies
View Related
May 26, 2006
Hi Everyone,Can you please kindly tell me how to create a new user on the MS SQL 2005 Professional edition for accessing the existing database called VCalendar?thanks for your time and help!Jiimmy
View 13 Replies
View Related
Nov 29, 2002
I try to install MSDE as a new instance (SQL2) on my computer with Windows 2000 Professional, but a problem occurs and instalaltion does not complete successfully. I am an administrator of the computer. The application log contains the following errors descriptions:
"The SYSTEMCurrentControlSetServicesMSSQL$SQL2Perfo rmance key could not be opened or accessed in order to install counter strings.The Win32 status returned by the call is in the Record Data as DWORD 0. " and
"Installing the performance counter strings for MSSQL$SQL2 failed. The Error code is DWORD 0 of the Record Data. "
Have someone experienced a similar problem?
View 1 Replies
View Related
Nov 8, 2006
Hi Every one,
I have installed Visual studio 2005 professional edition.(with SQL Server 2005 expree Component). The Installation was done by Administrator.
I am able to work to access all database servers, Except SQL Server 2005. When I am trying to Add>>New Item>>SQL database in project, I am getting following error.
Login Failed for "XXXYYY"
Please some one help me to short it out.
thanks in Advance
View 1 Replies
View Related
Sep 18, 2007
Can you explain please why Extended SP does not work under Vista ?
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 6.0 (Build 6000: )
UAC turned off. MSQSL runs under Administrator account.
Any call of user esp - and SQL server goes to loop.
If I call system esp - all works fine.
If I try to read properties of ESP from Management Studio - I have the same effect - SQL server is in loop.
select object_id('dbo.xp_mylog')
-- works.
EXEC('sp_helpextendedproc ''xp_mylog''')
xp_mylog c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnLogEsp.dll
exec master..xp_mylog
die forever
Konstantin
View 6 Replies
View Related
Apr 1, 2006
The computers here at work run Windows 2000 5.00.2195 SP4. Everything checks out with the minimum requirements at http://msdn.microsoft.com/vstudio/express/support/sqlreadme/#_3462_setup_issues_238p
However, one of the "Reports" from the installation says the machine does not meet the minimum requirements, but provides no details. It installs and I have Admin rights on this machine, but I can't even connect to the Northwind database that I downloaded.
Is this due to our logging process where the "system" is actually in another country? (ie, "CITYNAME/Username"
Any help is appreciated!
Jason
View 4 Replies
View Related
Jun 20, 2006
Hi
I am trying to install SQL Server 2005 Standard on my laptop, which has Windows XP Professional with Service Pack 2. I could not able to install properly and i am getting error always. i tried whatever the people mentioned in Google, but I could not able to fix the problem. please some one can help my problem.
Here is the Error I am getting always "The certificate chain was issued by an authority that is not trusted" . I am trying for past few days i could not able to resolve.
please help
Note : I have SQL Server 2000 Client installed in my laptop. this is my development machine
Thanks
Paddy
Here is the Error message from Summary.txt
Product : SQL Server Database Services
Error : SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client]SSL Provider: The certificate chain was issued by an authority that is not trusted.
Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online.
--------------------------------------------------------------------------------
Machine : W4102762
Product : Microsoft SQL Server 2005
Product Version : 9.00.1399.06
Install : Failed
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_w4102762_SQL.log
Last Action : InstallFinalize
Error String : SQL Server Setup could not connect to the database service for server configuration. The error was: {Microsoft}{SQL Native Client}SSL Provider: The certificate chain was issued by an authority that is not trusted.
Error Number : 29515
--------------------------------------------------------------------------------
Machine : W4102762
Product : SQLXML4
Product Version : 9.00.1399.06
Install : Successful
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_w4102762_SQLXML4_1.log
--------------------------------------------------------------------------------
SQL Server Setup failed. For more information, review the Setup log file in %ProgramFiles%Microsoft SQL Server90Setup BootstrapLOGSummary.txt.
View 1 Replies
View Related
Sep 7, 2007
Hi, I am trying to open or create a sqlexpress database within Visual Studio 2005 Professional in the App_Data folder. If I attempt either method, I get the following dialog box:Required Components MissingConnections to SQL Server files (*.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component or download from the URL: http://go.microsoft.com/fwlink/?LinkID=49251 The link above just takes you to the download page for Sql Server Express 2005. I have both SQL Server 2005 Developer and SQL Server 2005 Expression instances running. The existing database will work properly in my web application, however I cannot access it through Visual Studio.As background, I did have a problem connecting to the database via the web application, receiving a "Failed to generate a User Instance of SQL Server" error. But I was able to fix that by renaming my SQLEXPRESS folder in C:Documents and SettingsuserLocal SettingsApplication DataMicrosoftMicrosoft SQL Server Data. When the web page ran, it created another SQLEXPRESS folder.However, I cannot figure out why I am having the other issue.Thanks in advancePatrick
View 1 Replies
View Related
May 13, 2015
I have a scenario like below
Product1
Product2 Product3
Product4 Product5
Product1 1
1 0 0
1
Product2 1
1 0 0
1
Product3 0
0 1 1
0
Product4 0
0 1 1
0
Product5 1
1 0 0
1
How to design tables in SQL Server for the above.
View 2 Replies
View Related
