Horizontal Partitioning question

[MissLivvy]

I recently came across a database where the data are horizonally partitioned
into 4 tables. I'm not sure if this was a poor design choice, or if it was
done for valid performance reasons. The schema of the tables are essentially
the same, it's just that they are named differenly and the columns are named
differenlty to differentiate the data from a business usage perspective. The
tables could easily be combined inot one by adding a new colum to the
clustered index that would be used to differentiate the business usage. I am
trying to evaluate whether combining the tables would improve performance or
if it would be better to leave them the way they are. Many queries that run
against these tables do not request records from more than one of the
tables, which is good. However, there are a number of processes that query
against all of the tables on the identical clustered index range. I am not
sure exactly how many rows are in the tables but I'm fairly certain the
entire database is < 50 GB.

[MissLivvy]

It's a financial forecasting application and the data are heavily
manipulated by the users after copying from another version of the forecast.
Copying is just an easier way for them to get started vs. starting over
completely from scratch. They also run variance reports to compare different
versions of the forecast. To reduce the size of the database, I think an
archiving strategy would be appropriate.

"Dan Gidman" <da*******@gmai l.com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .

For example, one thing that

users routinely need to do is copy a large range of rows from all of
the
tables and insert them back into the same tables (with a new PK, of
course).

This seems to me like a lot of redundant data will get created
needlessly. It is probably why the db is +50 gig in size. Also a good
indication of poor design. Is this data historic or frequently
updated? if it is historic and is not changed (like a pos sales
record) Why copy the data around so much?

[MissLivvy]

Maybe I misunderstood the problem. The way I understood it:

1] a directory can be one of 3 types: input, output, or binary.
2] A job has up to 3 directories: input, output and binary.
3] A directory can be shared by more than one job.

Is that correct?

"Vincent Lascaux" <no****@nospam. org> wrote in message
news:41******** *************** @news.free.fr.. .

Directories
nick_name varchar(20)
type byte //0: input, 1: output, 2:binary
path varchar(1000)
primary key(nick_name, type)

Job
(JobID int primary key,
JobName varchar(20)
)

Job_Directory
(JobID int,
nickname varchar(20),
type (byte)
)
with PK on JobID + nickname + type
Considering that any job has one and exactly one path of each type, you

have a 1-3 relationship. I dont know if it is better than 1-1, that I heard is
bad :)
And it makes the SQL queries more complex to write (for no added value)

--
Vincent

[Vincent Lascaux]

> 1] a directory can be one of 3 types: input, output, or binary.

True
And the same nickname can be used for different types

2] A job has up to 3 directories: input, output and binary.
Half true : a job has exactly 3 directories : one input, one output and one
binary directory
3] A directory can be shared by more than one job.

True

--
Vincent

[MissLivvy]

Then like my design better. You may find it a pain to have to join to an
extra table, but with your design you need 3 joins to get each of the 3
directories related to a job. Also, if you ever have a 4th directory related
to a job you have to add a new column.

If you have no other attributes to add to the Job table, then you could get
rid of the job table and just do:

Directory (
nick_name varchar(20)
type byte //0: input, 1: output, 2:binary
path varchar(1000)

)
(with primary key(nick_name, type))

Job_Directory
(JobName nvarchar(20),
nickname varchar(20),
type (byte)
)

(with primary key(JobName, nick_name, type))
and fk to Directory on nickname, type)

"Vincent Lascaux" <no****@nospam. org> wrote in message
news:41******** *************** @news.free.fr.. .

Directories
nick_name varchar(20)
type byte //0: input, 1: output, 2:binary
path varchar(1000)
primary key(nick_name, type)

Job
(JobID int primary key,
JobName varchar(20)
)

Job_Directory
(JobID int,
nickname varchar(20),
type (byte)
)
with PK on JobID + nickname + type
Considering that any job has one and exactly one path of each type, you

have a 1-3 relationship. I dont know if it is better than 1-1, that I heard is
bad :)
And it makes the SQL queries more complex to write (for no added value)

--
Vincent