Database

CodingLover5-Apr-11 18:40

5-Apr-11 18:40

Actually I don't want to keep records for a long time. Only for an instance.

The requirement is, for few sales rep I want to find the sales in a date range. That is for each sales rep I want to find the sales in a date range.

I appreciate your help all the time...
CodingLover Smile | :)

Wendelius5-Apr-11 18:46

5-Apr-11 18:46

CodingLover wrote:
The requirement is, for few sales rep I want to find the sales in a date range

This is a different thing. What I'm saying is that consider populating the table with long enough date range. And when you use the table take only the relevant portion of dates from it using proper WHERE condition. For example:

SELECT ...
FROM DateTable dt LEFT OUTER JOIN MyTable mt
     ON dt.DateColumn =  mt.DateColumn
WHERE dt.DateColumn BETWEEN @startdate AND @enddate

This way you can reuse the same data every time you fetch data for a new date range without having to re-create the dates.

The need to optimize rises from a bad design.My articles[^]

CodingLover5-Apr-11 18:55

5-Apr-11 18:55

Oh, I think I got your point now.

What you are saying is keep dates in a permanent table ( for a long dates, eg: 2000-01-01 to 2500-01-01) and use that table to join with the other table in the date range I want to.

I appreciate your help all the time...
CodingLover Smile | :)

Wendelius5-Apr-11 19:04

5-Apr-11 19:04

Exactly

The need to optimize rises from a bad design.My articles[^]

CodingLover5-Apr-11 22:14

5-Apr-11 22:14

Thanks buddy.

I'll give a try and let you know.

So I'll keep this thread as it is. Smile | :)

I appreciate your help all the time...
CodingLover Smile | :)

Shameel5-Apr-11 5:22

Shameel

5-Apr-11 5:22

Step 1: Create a table variable with a date column and fill the table with the date range that you need (probably the min and max dates from 'packages' table)

Step 2: Use your query and do a union with the temp table created in step 1

CodingLover5-Apr-11 17:53

5-Apr-11 17:53

Shameel wrote:
Step 1: Create a table variable with a date column and fill the table with the date range that you need (probably the min and max dates from 'packages' table)

So I can move with a temporary table or a permanent one. But I'm stuck with how to insert range of dates into it. Something like this ...

CREATE TEMPORARY TABLE date_col (add_dates nvarchar(50));
INSERT INTO date_col (add_dates) VALUES ('2011-04-06' through '2011-04-01'); /* how can I do that */

I appreciate your help all the time...
CodingLover Smile | :)

jschell6-Apr-11 8:30

jschell

6-Apr-11 8:30

I would suppose that there is some clever way to do this with an "integers" table.

The following link shows an example used to create a range of dates.

http://books.google.com/books?id=EoSNYVb5LAsC&pg=PA101&lpg=PA101&dq=%22integers+table%22+sql&source=bl&ots=-cOiNaw7DQ&sig=DKJMIdo4xUIIzepSw1icsnmCFkg&hl=en&ei=qrCcTez9DJOx0QGFv-nKAg&sa=X&oi=book_result&ct=result&resnum=6&ved=0CEEQ6AEwBQ#v=onepage&q=%22integers%20table%22%20sql&f=false[^]

SilimSayo4-May-11 10:30

SilimSayo

4-May-11 10:30

This my help http://weblogs.sqlteam.com/jeffs/archive/2007/09/10/group-by-month-sql.aspx[^]

Realtime Backups

Lasanthapw4-Apr-11 20:14

Lasanthapw

4-Apr-11 20:14

Hi all,

My web application base on money transactions. My client wants to keep a realtime backups. That means i host the web app in one server and have another server for backups. If system down in the hosted server there is a backup link to the system in backup server. The problem is i want to update the backup server database in the same time when updating the original server database. What is the best and more efficient solution for this requirement.

thanks

Re: Realtime Backups

Wendelius4-Apr-11 20:50

4-Apr-11 20:50

If your requirement is not to loose data in case of a disaster, have a look at database mirroring, especially synchronous High-safety mode: http://msdn.microsoft.com/en-us/library/ms191456.aspx[^]

The need to optimize rises from a bad design.My articles[^]

Re: Realtime Backups

Mycroft Holmes4-Apr-11 22:21

Mycroft Holmes

4-Apr-11 22:21

This is a standard setup for a high a proficiency site, you need mirroring (as Mika suggested) and more probably failover web and database servers. I would suggest talking to a hosting site, it may be simpler to pay someone else to do these.

Never underestimate the power of human stupidity
RAH

Re: Realtime Backups

Wendelius5-Apr-11 6:03

5-Apr-11 6:03

Good addition Smile | :)

Marked as good answer.

The need to optimize rises from a bad design.My articles[^]

Schema ideas

Super Lloyd4-Apr-11 17:58

Super Lloyd

4-Apr-11 17:58

In our application the user can define custom "user defined type" called "data context" to store a bunch of named datetime/string/number/list(Guid)
such as (for example)
--
Contact Information
Name
Age
Address
Birthday
--
we store that in the DB with some table like that (simplified version)

Sets -- a such item, ex: a contact information
id Guid
idDef Guid -- PK to the type definition table

SetValues -- store the values, ex: Contact.Name
id Guid
idSet Guid -- PK to a record in Sets
idDef Guid -- PK to the type definition table
Value sql_variant

In fact it's a it more complicated (deep) due to list and list with property (2 more tables to store the value of a single user type value)
The problem is it's quite cumbersome to do a search for a value for example and we were thinking to store the data instead, in a table like that

Sets
id Guid
string1 nvarchar(max)
...
string15 nvarchar(max)
datetime1 datetimeoffset(7)
..
datetime15 datetimeoffset(7)
number1 decimal(8,2)
..
number15 decimal(8,2)
list1 uniqueidentifier
..
list15 uniqueidentifier

I'm not sure I explain my problem clearly but.. what I wonder is: is there a clever schema that will allow to store the untyped hierarchical user data, yet makes it easy to join / search, etc...

A train station is where the train stops. A bus station is where the bus stops. On my desk, I have a work station....
_________________________________________________________
My programs never have bugs, they just develop random features.

Re: Schema ideas

Wendelius4-Apr-11 18:50

4-Apr-11 18:50

Hi,

If I understood you correctly, you have already created tables where you can handle dynamic amount of attributes and values. Querying such tables is often a bit tricky if you have several levels etc, but CTE[^] is a helpful tool when handling dynamic hierarchies.

On the other hand another possibility could be to use XML data type. This would allow you flatten the relational hierarchy and still be able to use highly dynamic values.

The need to optimize rises from a bad design.My articles[^]

Re: Schema ideas

Super Lloyd4-Apr-11 18:56

Super Lloyd

4-Apr-11 18:56

Yeah, XML data type, someone mentioned that... Have to have a look, thanks!! Smile | :)

I though I knew about CTE already but.. didn't know ou can use them for recursive queries!!! mm... thanks (again!)!

A train station is where the train stops. A bus station is where the bus stops. On my desk, I have a work station....
_________________________________________________________
My programs never have bugs, they just develop random features.

Re: Schema ideas

Wendelius4-Apr-11 19:15

A question about design Relational Model

4-Apr-11 19:15

No problem Smile | :)

The need to optimize rises from a bad design.My articles[^]

Duc Huy Nguyen2-Apr-11 22:31

Duc Huy Nguyen

2-Apr-11 22:31

Hi guys, I've learnt 'bout database designing and I got a difficulty that you can help.
Here the question is: a relation R has 3 attributes, A,B,C and A is a primary key. Then I want to insert new attribute D. In each tuple of R, value of attribute D is assigned by (value of B + value of C).
My task is using Relational algebra, writing a expression to do so.
Below is an example
R - before doing anything
A B C
1 10 9
2 3 7
3 9 6
4 12 0
5 1 5
R - after inserting attribute D
A B C D
1 10 9 19
2 3 7 10
3 9 6 15
4 12 0 12
5 1 5 6

Anyway, thanks alot for reading my question and helping my solve this problem

Wendelius2-Apr-11 22:41

2-Apr-11 22:41

It's not quite clear what your actual question is but concerning calculated columns. It's typically best to avoid columns which are calculations based on the values of the same (or other) rows. This is because you have to take care that when the original values change, also the result of the calculation is changed.

If you're using SQL Server, then things are a bit different since the database has the ability to use computed columns (see: http://msdn.microsoft.com/en-us/library/ms191250.aspx[^]).

If your question is about updating the value, then:
- first insert the new column
- then update the existing rows. Something like:

UPDATE R
SET D = A + B

The need to optimize rises from a bad design.My articles[^]

Eddy Vluggen3-Apr-11 1:50

Eddy Vluggen

3-Apr-11 1:50

Duc Huy Nguyen wrote:
My task is using Relational algebra, writing a expression to do so.

That sounds complicated. I'd rephrase that to "you need to add a column to the table, that holds the sum of columns B + C". You'd need to alter the table[^], ending up with something like this;

SQL

BEGIN TRAN
-- prep example data
CREATE TABLE [R]
(
	A INT,
	B INT,
	C INT
)

INSERT INTO [R]
	SELECT 1, 10, 9 UNION ALL
	SELECT 2, 3, 7 UNION ALL
	SELECT 3, 9, 6 UNION ALL
	SELECT 4, 12, 0 UNION ALL
	SELECT 5, 1, 5

-- I want to insert new attribute D. In each tuple of R, value of 
-- attribute D is assigned by (value of B  +  value of C).

ALTER TABLE [R]
	ADD D AS [B] + [C]

-- Proof
SELECT *
  FROM [R]

ROLLBACK

I are Troll Suspicious | :suss:

Keith Barrow3-Apr-11 2:33

Keith Barrow

3-Apr-11 2:33

You shouldn't include calculated columns in your table like this, IIRC it breaks first normal form as the columns are no longer atomic. You should put the calculation into your select statements -or- [better] create a View with the calculated column in it.

Sort of a cross between Lawrence of Arabia and Dilbert.[^]
-Or-
A Dead ringer for Kate Winslett[^]

Eddy Vluggen3-Apr-11 20:45

Eddy Vluggen

3-Apr-11 20:45

May I ask how it's no longer atomic? The "new" value (the result from the calculation) is a new atomic fact, even though it's derived from other data - it's not a concatenation of both original columns and you cannot deduce the original values from it.

I are Troll Suspicious | :suss:

Keith Barrow3-Apr-11 22:17

Keith Barrow

3-Apr-11 22:17

Eddy Vluggen wrote:
May I ask how it's no longer atomic?

The calculated column relies on other data, it isn't atomic. The classic examples use concatenation, but it is realy a relies upon relationship.

Sort of a cross between Lawrence of Arabia and Dilbert.[^]
-Or-
A Dead ringer for Kate Winslett[^]

Eddy Vluggen4-Apr-11 0:15

Eddy Vluggen

4-Apr-11 0:15

Keith Barrow wrote:
The calculated column relies on other data, it isn't atomic

It relies on other data, that's true, but you cannot decompose the value. It's a new atomic fact, not a composition of other facts. Yes, you can deduce it from other data, but in this case only in one direction (as opposed to a birthdate and an age)

Keith Barrow wrote:
The classic examples use concatenation, but it is realy a relies upon relationship.

Any concatenation of characters would fall under this category. The question is whether the fact that's being described is atomic, not the data.

Whether or not a column is computed (and whether or not the value is stored or recomputed), doesn't matter much for the relational model; that's merely an implementation-detail.

I are Troll Suspicious | :suss: