How To Design The History Table To Be More Efficient?

Feb 8, 2007

I am running a website of crossword puzzle and Sudoku games. The website is designed to be:

There are 20-30 games onlines each day.
Every registered user could play and submit the game to win scores.
For each game, every registered user could get the score for ONLY one time. i.e., No score will be calculated if the user had finished the game before.
To avoid wasting time on a game finished before, user will be notified with hint message in the page when enter a already finished game.


The current solution is:
3 tables are designed for the functions mentioned above.

Table A: UserTable --storing usering information, userid
Table B: GameList --storing all the game information.
Related fields:
GameID primary key
FinshiedTimes recording how many times the game has been finished
Table C: FinishHistory --storing who and when finished the game
Related fields:
GameID ID of the game
UserID ID of the user
FinishedDate the time when the game was finshied

PS: Fields listed above are only related ones, not the complete structure.

Each time when user enters the game, the program will read Table B(GameList), listing all the available game and the times games have been finished. User could then choose a desired game to play.

When user clicks the link and enter a page showing the detail content of the game, the program will read Table C(FinishHistory) to check whether user has finished this game before. If yes, hint message will be shown in the page.

When user finishes the game and submit, the program will again read Table C(FinishHistory) to check whether user has finished this game before. If yes, hint message will be shown in the page. If no, user will get the score.

Existing Problems:
With the increase of game and users, the capacity of Table C(FinishHistory) grows rapidly. And each time when a game is loaded, the Table C will be loaded to check, and when a game is submitted, the Table C will be loaded to check again. So it is only a time question to find out Table C to become a bottleneck.

Does any one here have any good suggestions to change / re-invent a new structure or design to avoid this bottleneck?

View 5 Replies


ADVERTISEMENT

History Table Design Issue

Nov 15, 2007

Hi all,
 this is more of a design issue for a History table.
Suppose if i have a transaction table and then based on the transactions i want to keep a history of those do i need to define Primary Key and Foreign Key for history table.
Regards,
General Problem

View 1 Replies View Related

DB Design :: Extracting History From OLTP?

Nov 3, 2015

Simple and conventional OLTP database, we need to capture all changes for insert into a DW via staging / ods etc.

Is there a recommended approach for this? Obviously it has to be real time as there might be multiple updates for a time period. I'm thinking of triggers on OLTP tables (bad for performance as it's synchronous), or change data capture, service broker as asynchronous methods.

View 8 Replies View Related

Most Efficient Way To Count Subset Of Rows In A Table?

Sep 14, 2005

Hi all,
I’ve a table with production objects, and another with possible items composition of the object. I need to count how many occurrences of each standard composition appears:
Table PROD:
PROD_ID    COMPONENT   TYPE
--------- ----------- -----
1         AAA         X1         BBB         Y2         AAA         X3         BBB         Y4         AAA         Y5         AAA         X5         BBB         YTable ITEM_COMPITEM_ID   COMPONENT   TYPE--------  ---------   -----7         AAA         X7         BBB         Y8         AAA         X9         BBB         Y
The result should be:
ITEM_ID   OCCURRENCES--------  ----7         28         19         1
Table PROD have millions of rows, my way is too slow (I’ve a loop where each PROD object are separately queried against the ITEM_COMP), some have an idea for a most efficient way?
 
ThX
 
NeuralC
 
 

View 2 Replies View Related

Need An Efficient Way To Repeat The Column Headers Of A Table

Dec 19, 2007



There are 2 tables
tPeople

ID Name

1 Arun
2 B
3 C

tAddress

PeopleID Address
1 Test Address A
1 Test Address B
1 Test Address C
2 Address A
3 Address1
3 Address2

Expected Result

ID Name Address1 Address2 Address3

1 Arun TestAdressA Test Address B Test Address C


2 B Address A

3 C Address1 Address2

Address columns repeated = max(count(Address)) for PeopleID

Help me on this!

I can do it through dynamic sql using Execute sp_executesql

Is there any workaround except this?

View 2 Replies View Related

Which Is The Most Efficient Query To Find Out The Total Number Of Rows In A Table?

Mar 30, 2006

which is the most efficient query to find out the total number of rows in a table other than using - SELECT COUNT(*) ... in query

View 10 Replies View Related

Instances And History In One Table

Jan 11, 2008

I have a database design question. There're lots of ways to rome they say, and I want to hear what you think of this way.

Government supplies wheel chairs (and thinks alike) to people who need them. They stay in possesion of the (local) government and are distrubuted by a company X.

So we have Tools (Wheelchairs) and Users (of wheel chairs). The life-cycle of a wheel chair is that more than one user while use it over time.

I want to keep track of which users used a instance of a wheelchair.

No there's a developer who likes to put this in one table. (the chair and it's user) in a way like this


UID, WheelChairId, UserId, OwnerId, SerialNumber, BeginDateTime, EndDateTime, SomeOtherColumns

The UID is unique, the WheelChairId is a GUID which is Unique per wheelchair, but can have mutliple records in the table with no overlap.

If one of the values of the columns is changed a new record is made with the same wheelchair and a new begin date (the closed record gets an Enddate). So history is made automaticaly. By using the right query's I can see what users uses the chair in what period of time. But also changed ownerships and other changes in Someother columns overtime.

Is this a good or a common practice? Why use it, or stay away from it?

Henri
~~~~
There's no place like 127.0.0.1

View 1 Replies View Related

Common Need: History Table

Jan 22, 2008

Hi there!

I'm working on an application designed like this:
There's a table "DailyTransations" (DT) containing daily transactions...
Then there's an archive table "TransationsArchive" (TA) with the exact same structure.

When a record is inserted in DT, it is also in TA (via a trigger) and the reporting is done against TA.
Now for performance issues, we delete from DT the records older than 2 days since they are not needed for processing.

First, what do you think of that implementation?

We thought about using partitions based on the transaction date and completely eliminate TA, but it seems once a record is assigned to a partition, it is not moved automatically...

What is the common solution for this need?

Thanks
Frantz

View 4 Replies View Related

T-SQL (SS2K8) :: Show Changes From And To In History Table

Jun 28, 2014

I have a table history of Employee data.

id | EmpNo | EmpName | MobileNo | Email | EmpSSS | UpdateDate | UpdateUser

I have to make a stored procedure that will show the history and changes made to a given EmpNo, with the UpdateDate, UpdateUser and indicate which field is modified. Ex. Employee Mobile number was changed from '134151235' to '23523657'.

Result must be:

EmpNo | UpdateDate | UpdateUser | Field changed | Change from | change to

View 4 Replies View Related

T-SQL (SS2K8) :: Get Data Changes From History Table?

Mar 25, 2015

how to get products on which SalesPerson changed. Here is table with data.

View 9 Replies View Related

SQL 2012 :: Query For History Table?

Nov 3, 2015

I have order history table that tracks who worked on a order

ID. ColA. ColB. ColC
1. Process 1234
2. Work. 7666
3. Return. 6789
4. Work. Null Role1
5. Return. 6538

I want a query to return recent colB or ColC where colA or colB are not null. In this case row 4

I have order history table that tracks who worked on a order

Another example

ID. ColA. ColB. ColC
1. Work. 1234
2. Process. 7666
3. Return. 6789

I want a query to return most recent colB or ColC where colB or ColC are not null. In this case row 2

View 1 Replies View Related

History Table &> X Days Between Progression

May 21, 2008

I work for a college and have recently been working on our enquiries and applications process (getting it onto our big enrollment db rather than standalone). It has all been going well but now they have asked for a report of students where it has taken more than x days or weeks to progress to the next stage code.

For stage codes they basically follow something like application, guidance interview, programme area interview, conditional/ unconditional offer... Although they could skip a stage code.

Any ideas how to do this bearing in mind I can't guarantee them to go to every stage so really I need to look in the history table and find records more than x days apart where one is the next progression date of the other. Hope I explained that ok.

history table is similar to:

StudentID CourseCode StageDate InputDate Stage

View 1 Replies View Related

New Unique Number In History Table

May 23, 2008

Hello,
I am using SQL Server 2005 and am having trouble with making a history table like mentioned in my earlier thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102811

This is the table "People" I have created:

|PersonId (PK)|DateFrom (PK)|DateTo|PersonName|Other Attributes....

Each change to a person's attributes results in a new row formed with the same PersonId as in the row with old attributes and the Date these new attributes are valid (DateFrom). So as shown above the Primary Key is a combination of the PersonId and DateFrom as a change to a person's attributes should never happen at the same time twice.

My problem is when I want to create a new person, how do I get a new unique id? Ideally I want the a new incremented id, so that all peoples' ids are in a sequential order.

As always, thanks for the help!

View 4 Replies View Related

Referencing A Users Table For History

May 6, 2008

I've recently finished an application for a small company with perhaps two hundred employees. Each employee was set up in a Users table in the database, against which application logins were processed.

For just about every other table in the database, other than pure lookup tables, we created columns to indicate the user who created the entry, and the user who last modified the entry. This was done using FK references back to the Users table. Each table contains two references back to the Users table, and there are over 150 tables now that follow this scheme. At first I was not concerned, other than the fact that it makes a visual picture of the data model look very confusing (almost every table has a pair of links back to the Users table), until I encountered an issue where I could no longer delete from the Users table. Upon surpassing 253 FK references to Users, I can no longer delete users, as the Query Optimizer can't complete the query.

Now, all of that so far is really not a big deal. Deleting users was never my intent anyway. The only real question I have is whether this is the standard way of maintaining history for table records. Have others used this method? Is there a better way?

View 1 Replies View Related

Nothing Written In View History Table

Mar 25, 2008



Hi guys,

acutally i have setup a Disaster Recovery plan for my database.. i m taking a full back once in a week,. i dont' know when i right click on the job and trying to check the view history option to check when was last backup has been taken, it's showing nothing..but when i check on acutall location the backup was taken there.. i don't know y it's not writing any info in view history table.. or is it clear once in a week and i cann't see that...

Can any one tell's me about this...

View 3 Replies View Related

DB Engine :: Table Transaction History

Oct 22, 2015

i have a table and i would like to know what are the queries that were executed referring to that table in the past 5 days. is it possible?

View 4 Replies View Related

DB Design :: Table Design For Packages

Aug 18, 2015

I would like to create a table called product. My objective is to get list of packages available for each product in data grid view column while selecting each product. Each product may have different packages type (eg:- Nos, CTN, OTR etc). Some product may have two packages and some for 3 packages etc. Quantity in each packages also may be differ ( for eg:- for some CTN may contain 12 nos or in other case 8 nos etc). Prices for each packages also will be different that also need to show.  How to design the table.. 

Product name   :  
Nestle milk |
Rainbow milk
packages  :
CTN,OTR, NOs |

CTN, NOs
Price:
50,20,5 |
40,6

(Remarks for your reference):CTN=10nos, OTR=4 nos  
| CTN=8 Nos

View 3 Replies View Related

SQL Server 2012 :: Password History Table

May 7, 2014

I have a table of users including: UserName, Password (comuted col), FirstName, LastName, Address and other details....

I have to keep 10 Recent passwords , so I created another table "ut_Password " (Table2)

This table contains the following columns : Username, Password , and Password_Date.

I searched a lot but could not find something similar in my opinion need SP for it.

- 10 row Max for Password History in table 2
- when user change password it's need to be uniqe and it should not appear last 10 passwords
- Each user can have a maximum of 10 lines containing history password table
- Most old password deleted and replaced with a new password will enter the correct date (FIFO method first in first out).

View 9 Replies View Related

SQL 2012 :: Select Statement From History Table?

Jan 26, 2015

Project has 2 tables process(parent) and processchild(child).

Project workflow recorddsany changes to these tables as a history.

I want to find out all the process that are in status = saved(1) where processchild is at status = started(1).

Here is example.

Process table

PK, processid, status , other data
1, 1, 1,...
2, 1, 2,...
3, 2, 1,...
4, 3, 1,...

ProcessChild table

PK, processid, processchildid status, other data
1, 1, 1, 1,..
2, 1, 1, 2,..
3, 1, 2, 1,...
4, 1, 2, 2,...
5, 2, 1, 1,..
6, 2, 1, 2,...
7, 2, 2, 1,...
8, 3, 1, 1,..

I want to find out all the processes where processchildid=2 and processchild.status =1

View 3 Replies View Related

T-SQL (SS2K8) :: Get Recent Changed Value From History Table

Apr 6, 2015

I have a history table with the following values

CREATE TABLE History (SnapShotDate DATETIME, UID VARCHAR(10), DUEDATE DATETIME)

INSERT INTO History VALUES ('03-23-2015','PT-01','2015-04-22')
INSERT INTO History VALUES ('03-30-2015','PT-01','2015-04-20')
INSERT INTO History VALUES ('04-06-2015','PT-01','2015-06-30')

[Code] ....

I need an output in the below format. I need the most recent changed value for any given UID. Need to get the below result

OUTPUT
UID PreviousDueDate CurrentDueDate
----------------------------------------
PT-01 2015-04-20 2015-06-30
PT-02 2015-04-22 2015-04-22
PT-03 2015-04-18 2015-04-22
PT-042015-04-222015-04-18

View 4 Replies View Related

Employee Data - Show Changes From And To In History Table

Jun 27, 2014

I have a table history of Employee data.

id | EmpNo | EmpName | MobileNo | Email | EmpSSS | UpdateDate | UpdateUser

I have to make a stored procedure that will show the history and changes made to a given EmpNo, with the UpdateDate, UpdateUser and indicate which field is modified. Ex. Employee Mobile number was changed from '134151235' to '23523657'.

Result must be:

EmpNo | UpdateDate | UpdateUser | Field changed | Change from | change to

View 4 Replies View Related

Complex Stored Procedure On History Table

Jun 7, 2006

Hi All,

I have a table that hold status history records for cases. In this table is a status field with values, opened, assigned, or complete. Each case can be assigned a number of times before it is complete, and can be reassigned. I have the need to run a query that will get each case that is still assigned, and not yet complete. I wrote a stored procedure that contains a cursor containing each case, and get the last status history record for each case and puts it into a temp table to return to the user, but is hurting performance as there are .5 million records here. Does anyone know of a better way of doing this?



Thanks in advance : )

View 1 Replies View Related

Updating Daily Information In A History Table (was Help-Brainstorming)

Feb 25, 2005

Hi everyone,
I have a big table which contains approx. 31,524,044 rows. The structure of the table look like this:
date ID A B C D
1/1/65 X Null Null Null Null
1/4/65 X 1 2 3 4
...
2/25/05 X 2 3 4 5
1/1/65 Y Null Null Null Null
1/4/65 Y Null Null Null Null
...
2/25/05 Y 2 3 4 5
...

The number of distinct(ID) is approx 3200 and each one has daily historical A, B, C, and D back for 40 years. For going forward I need to update daily information for 3200 ids. Currently, I am runing query against to this table is ok. I am thinking by the time pass by the table will be hudge since "stored historical information". It probably takes "long long long" time to run the query against this table. Any suggestion or comments... what is the best/better solution? Or it is not problem at all?

Thank you everyone for the help.
shiparsons

View 8 Replies View Related

SQL Query History Table To Return Only Active Rows

Mar 5, 2008



Given the following data how do I make a SQL query that returns only 1 row per product?

The returned rows need consist of only currently active products (that is WHERE (DateEffective <= { fn NOW() }).
The twist: Sometimes a product will have duplicate DateEffective records. In that case, only return the record created latest because that's the most current data that exists for a product. RowTimeStap is when the record was created.


Example Data:
HistoryID ProductID Name Color DateEffective RowTimeStamp
(auto-number PK)
1 1 Wheel Red 2/1/2008 2/1/2008
2 1 Wheel Blue 3/5/2008 3/1/2008
3 1 Wheel Orange 3/5/2008 3/2/2008
4 1 Wheel Black 1/1/2010 3/3/2008
5 2 Knob Blue 3/2/2008 3/2/2008
6 2 Knob Green 3/3/2008 3/3/2008

Query should return:
3 1 Wheel Orange 3/5/2008 3/2/2008
5 2 Knob Green 3/3/2008 3/3/2008


The query I've created fails on the twist part. I have to allow duplicate DateEffective to keep a history of changes.
Can anyone help?










View 15 Replies View Related

A Basic History Table - Foreign Keys And Deletes

Nov 19, 2007

Let's say you have a Users table, which simply contains a collection of people. Each person has attributes like their password, first and last name, username, etc. It would be useful to track changes to that table over time, so that if an entry is changed, you can simply look at a history table and see which changes occured, and when.

An INSERT/UPDATE trigger works well for this sort of thing, where the trigger inserts all of the 'INSERTED' values into a history table that has basically the same table definition as the Users table. But it gets tricky when considering Deletes.

If my History table entries reference back to the User in the Users table, this means that if I ever want to delete the user, I need to delete all their History first. This means I can't keep records of user deletions, which is significant. The other approach is not to have a foreign key reference in the History table, so that if a user is deleted, I can still keep my History about that user. Including deletes.

I have been timid about doing it this way, since I felt it broke the idea of a well structured database. Is there a better way to approach this? Is there a standard way to set up triggered history to track changes, including deletions, from a table?

Thanks,
-Dan

View 1 Replies View Related

SQL Server Admin 2014 :: Schema Change History - Identify Who Dropped A Table?

Jun 30, 2015

How can I easily identify who dropped a table?

View 8 Replies View Related

Insert / Update In Master Table And Also Save A History Of Changed Records : Using Data Flow/simple Sql Queries

Feb 9, 2007

Hi,

My scenario:

I have a master securities table which has 7 fields. As a part of the daily process I am uploading flat files into database tables. The flat files contains the master(static) security data as well as the analytics(transaction) data. I need to

1) separate the master (static) data from the flat files,

2) check whether that data is present in the master table, if not then insert that data into the master table

3) If data present then move that existing record to an history table and then update the main master table.

All the 7 fields need to be checked to uniquely identify a single record in the master table.

How can this be done? Whether we can us a combination of data flow items or write a sql procedure to do all this.

Thanks in advance for your help.

Regards,

$wapnil

View 4 Replies View Related

DB Design :: Insert / Update FACT Table From Staging Table

May 6, 2015

We need to Insert/Update a Fact Table from staging Table. currently we are using a SP which update Fact Table for Each region.  this process is schedule,  every 5 min job is run and Update fact table.but time of Insert and Update too long from  staging  to Fact, currently we are using merge statement for Insert and update.in my sp we are looping number  how many region we need to update and at a time single Region we are updating using while loop in current SP.

View 7 Replies View Related

DB Design :: Table Partitioning Using Reference Table Data Column

Oct 7, 2015

I have a requirement of table partitioning. we have 10 years of data on a table which is 30 billion up rows on 2005 server we are upgrading it to 2014. we have to keep 7 years of data. there is no keys on table or date column. since its a huge amount of data and many users its slow down the process speed. we are thinking to do partition on 7 years for Quarterly based. but as i said there is no date column on table we have to use reference table to get date. is there a way i can do the partitioning with out adding date column on table? also does partition will make query faster? 

I have think three ways to do it.
1. leave as it is.
2. 7 years partition on one server
3. 3 years partition on server1 and 4 years partition on server2 (for 4 years is snapshot better?)

View 3 Replies View Related

DB Design :: Insert Data From One Table To Another Table

Jul 30, 2015

I have to tables like given below Landing table "A" (Data load will happen over here, No primary keys mentioned over here) table "B" .Now I want to move the data from A to B.I have made use of below query insert into B select * from A...Landing table "A" has huge no of records, MS SQL server is taking huge amount of time.any alternative way to make this insertion process faster?

View 6 Replies View Related

Table Design

Apr 30, 2008

Hi, I am developing an application to a garment factory. I have a doubt in designing a table.Basic tables:Jobs, JobColors, Material, Units, Currencies ...These tables are designed with normalization rules.I got a problem at PurchaseOrderDetailsMain
table is JobMaterial. It has materialid, jobid, supplierid, description
and TypeFactor(which represents the type of order) means that the
material is ordered based on size or colors or total qty.1 for ByColor, 2 for BySize, 3 for ByQty, 4 for By Contrast colorsThe main problem at the details of the sub table.JobMaterialDetailsIf typefactor is by size, i need to store the details based on sizeex: S - 2000pcs, M - 4000pcs, L - 4000pcs, XL - 2000pcsSo I will have 4 records per each sizeIf it is by color, White - 3000pcs, Portabella - 5000pcs, Black - 2000pcs.If it is by general, Total qty 10000pcsHow
can I design this table. If I take, ColorOrSize column, it will refer
different values for diffrent typefactor. When by size, it will have
Size and when by color, it will refer colorcode.But colors are having referential integrity. So it is violated other than by color as typefactor.What is the best way to design this table?Can anybody suggest?Thanks in advance

View 2 Replies View Related

Table Design

Jul 24, 2000

I am desingning a table and i have a column OrderID and another column call Order, is neccessary to use a primary key, because One OrderID may have many Orders?
Thanks.

View 5 Replies View Related

Table Design

Aug 21, 2000

Coming from a support background and having to design my first database I have a couple of questions re- table design. Firstly I have set up several tables and included one field (of the same name) in each. This is a primary key in one table with an incremental seed. I would like this info to appear in the other tables although these can be duplicates in the other tables. How is it best to achieve this relationship. From reading it suggest FK in the relationship application but looking at other databases this seems to have been achieved by some other means. Is it more common to use stored procedures to enforce this? If so please add pointers. Secondly, I have set up a couple of master tables to act as looks ups for fields in other tables. Again how do I get this to look up the table - is it done through stored procedures or at the time of writing the front end application?? Sorry if this is all basic stuff but it is new to me.

Thanks for any help

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved