Many to many table - index design question

Hi
I have a Many-to-Many table , like
Author_ID int
Book_ID int
What is the best practices in designing the indexes for this table?
Of course the table should not be clustered, do you all agree on that?
So my options is a combined compound index on both fields. The
advantage
is that I get "is unique" automatically.
Or I create two separate indexes, one for each field.
What a bout primary keys? I guess I should set that on both? But not
making it
clustered?
What do you recommend that I do here?
//Andy
I would use both columns in the primary key, and yes I would cluster
it. And I would use both columns in the reverse order in a
non-clustered unique index, so it would be indexed both ways.
Roy Harvey
Beacon Falls, CT
On Thu, 21 Feb 2008 07:43:51 -0800 (PST), Sune42 <sune42@.hotmail.com>
wrote:

>Hi
>I have a Many-to-Many table , like
>Author_ID int
>Book_ID int
>What is the best practices in designing the indexes for this table?
>Of course the table should not be clustered, do you all agree on that?
>So my options is a combined compound index on both fields. The
>advantage
>is that I get "is unique" automatically.
>Or I create two separate indexes, one for each field.
>What a bout primary keys? I guess I should set that on both? But not
>making it
>clustered?
>What do you recommend that I do here?
>//Andy
|||Thanks for the reply.
Why would you want to have a clustered index on a many-to-many column?
I mean there's nothing sequencial, increasing and
stuff are added and deleted in random order. I guess inserts will take
quite some time if the DB has to phyiscally re-order the
data? Or am I wrong?
Why is it better to do clustering in this case?
//andy
|||On Thu, 21 Feb 2008 14:37:46 -0800 (PST), Sune42 <sune42@.hotmail.com>
wrote:

>Thanks for the reply.
>Why would you want to have a clustered index on a many-to-many column?
>I mean there's nothing sequencial, increasing and
>stuff are added and deleted in random order. I guess inserts will take
>quite some time if the DB has to phyiscally re-order the
>data? Or am I wrong?
>Why is it better to do clustering in this case?
>//andy
I want two indexes, and both indexes "cover" all columns in the table.
If neither index is clustered there will be the table in a heap, a
complete copy of the table in the first index, and a complete copy of
the table in the second index. Three copies. If we cluster one of
the indexes, the leaf level is the base table, so we only have two
complete copies.
Roy Harvey
Beacon Falls, CT
|||Generally, but not always, tables with clustered indexes perform better than
heaps (the term for a table without a clustered index).
It is true that doing an insert that inserts a row that row needs to go into
a page that was full, the that page will have to be split, but while that
costs something, it's normally not that bad. I'm not quite sure what you
mean by "physically reorder the data", but I suspect you are thinking that
something goes on that is very drastic and that's not true.
You may want to read
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/clusivsh.mspx
for a case study of a comparison of performance results with and without a
clustered index.
Of course, as always, "your mileage may vary". And it is possible that with
your data and your particular combination os selects, updates, inserts, and
deletes the heap will be faster. The only way to know is for you to test it
with your data. But my guess is that you will find either the clustered
index is faster, or there is no significant difference in performance.
If it were my system, unless I had some a priori reason to believe this
table was going to be a major performance bottleneck in my system, I would
just use a clustered index since that should usually turn out to be the
correct choice. If it turned out to be a performance problem, then I would
would run tests to see if I either made the wrong choice of keys for the
clustered index and/or if a heap was faster.
Tom
"Sune42" <sune42@.hotmail.com> wrote in message
news:9e5429c5-1f73-4a0b-9b70-6a9cff80803a@.c33g2000hsd.googlegroups.com...
> Thanks for the reply.
> Why would you want to have a clustered index on a many-to-many column?
> I mean there's nothing sequencial, increasing and
> stuff are added and deleted in random order. I guess inserts will take
> quite some time if the DB has to phyiscally re-order the
> data? Or am I wrong?
> Why is it better to do clustering in this case?
> //andy

Many to many table - index design question

Hi
I have a Many-to-Many table , like
Author_ID int
Book_ID int
What is the best practices in designing the indexes for this table?
Of course the table should not be clustered, do you all agree on that?
So my options is a combined compound index on both fields. The
advantage
is that I get "is unique" automatically.
Or I create two separate indexes, one for each field.
What a bout primary keys? I guess I should set that on both? But not
making it
clustered?
What do you recommend that I do here?
//AndyI would use both columns in the primary key, and yes I would cluster
it. And I would use both columns in the reverse order in a
non-clustered unique index, so it would be indexed both ways.
Roy Harvey
Beacon Falls, CT
On Thu, 21 Feb 2008 07:43:51 -0800 (PST), Sune42 <sune42@.hotmail.com>
wrote:
>Hi
>I have a Many-to-Many table , like
>Author_ID int
>Book_ID int
>What is the best practices in designing the indexes for this table?
>Of course the table should not be clustered, do you all agree on that?
>So my options is a combined compound index on both fields. The
>advantage
>is that I get "is unique" automatically.
>Or I create two separate indexes, one for each field.
>What a bout primary keys? I guess I should set that on both? But not
>making it
>clustered?
>What do you recommend that I do here?
>//Andy|||Thanks for the reply.
Why would you want to have a clustered index on a many-to-many column?
I mean there's nothing sequencial, increasing and
stuff are added and deleted in random order. I guess inserts will take
quite some time if the DB has to phyiscally re-order the
data? Or am I wrong?
Why is it better to do clustering in this case?
//andy|||On Thu, 21 Feb 2008 14:37:46 -0800 (PST), Sune42 <sune42@.hotmail.com>
wrote:
>Thanks for the reply.
>Why would you want to have a clustered index on a many-to-many column?
>I mean there's nothing sequencial, increasing and
>stuff are added and deleted in random order. I guess inserts will take
>quite some time if the DB has to phyiscally re-order the
>data? Or am I wrong?
>Why is it better to do clustering in this case?
>//andy
I want two indexes, and both indexes "cover" all columns in the table.
If neither index is clustered there will be the table in a heap, a
complete copy of the table in the first index, and a complete copy of
the table in the second index. Three copies. If we cluster one of
the indexes, the leaf level is the base table, so we only have two
complete copies.
Roy Harvey
Beacon Falls, CT|||Generally, but not always, tables with clustered indexes perform better than
heaps (the term for a table without a clustered index).
It is true that doing an insert that inserts a row that row needs to go into
a page that was full, the that page will have to be split, but while that
costs something, it's normally not that bad. I'm not quite sure what you
mean by "physically reorder the data", but I suspect you are thinking that
something goes on that is very drastic and that's not true.
You may want to read
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/clusivsh.mspx
for a case study of a comparison of performance results with and without a
clustered index.
Of course, as always, "your mileage may vary". And it is possible that with
your data and your particular combination os selects, updates, inserts, and
deletes the heap will be faster. The only way to know is for you to test it
with your data. But my guess is that you will find either the clustered
index is faster, or there is no significant difference in performance.
If it were my system, unless I had some a priori reason to believe this
table was going to be a major performance bottleneck in my system, I would
just use a clustered index since that should usually turn out to be the
correct choice. If it turned out to be a performance problem, then I would
would run tests to see if I either made the wrong choice of keys for the
clustered index and/or if a heap was faster.
Tom
"Sune42" <sune42@.hotmail.com> wrote in message
news:9e5429c5-1f73-4a0b-9b70-6a9cff80803a@.c33g2000hsd.googlegroups.com...
> Thanks for the reply.
> Why would you want to have a clustered index on a many-to-many column?
> I mean there's nothing sequencial, increasing and
> stuff are added and deleted in random order. I guess inserts will take
> quite some time if the DB has to phyiscally re-order the
> data? Or am I wrong?
> Why is it better to do clustering in this case?
> //andy

many to many relationship - whats best way to add/edit/delete

I have a many to many relationship

I can design the table 2 ways:

1) Category table (cat_id, cat_name, active) - cat_id as PK
CategoryReq (cat_id, req_name) - cat_id & req_name as PK

2)
CategoryReq (req_name, cat_name) - req_name & cat_name as PK

IfI design 1st way. Then when they want to add and delete from theCategoryRequest table, they would have to add to the category tablefirst. Then maybe build a list of checkboxes to select from. The one'sthey check insert into the CategoryRequest table.

Drawback ofthis is that they can't edit the list on the fly. Since it may be usedby other request (since cat_id CategoryReq is fk into Category table)

If I design it the 2nd way. Then they can edit, delete, add on the fly. But there won't be a master category list.

Which way is better?

Hi,

The design of the database strictly derives from the application requirements. But as a rule of thumb, you should generally create normalized tables to the third normal form in RDBMS theory. And then denormalize only if necessary. (Usually, the main reason for denormalization is performance improvement). Denormalization can be very harmful. Do it sparingly and only if you know what are you doing!

Many to Many Look alike Dimensions

Hi all,

I have this design scenario that I need to validate if this is the best approach and that there are no other alternatives that I have not looked at.

OLTP 3 Tables:

Loans (PK-->LoanID, LoanSequence)

Borrower (PK-->BorrowerID, columns LoanID, LoanSequence are also there)

BorrowerAddress (PK--> BorrowerAddressID, columns BorrowerID, LoanID, LoanSequence are also there)

1. The OLTP system does not do update, every update is inserted as a new record. So we practically have type 2 change on OLTP system side. This is why Primary Key in Loan table is a com

2. One Loan record can have many borrowers (Primary, Co-borrower, Other Borrrower)

3. One Borrower can change his/her address many time during the course of the application process. So this implies many BorrowerAddresses for Each Borrower.

OLAP Tables:

table FactLoanBorrower and FactBorrowerAddress are the middle table for the many-to-many relationship.

FactLoans (TimeKey,FactLoanBorrowerKey)

FactLoanBorrower (FactLoanBorrowerKey, LoanID, LoanSequence, BorrowerID, BorrowerTypeKey)

FactBorrowerAddress (FactLoanAddressKey, BorrowerAddressID, BorrowerID, LoanID, LoanSequence)

DimBorrowerType (BorrowerTypeKey)

DimBorrower (BorrowerKey with Natural Keys (BorrowerID, LoanID, LoanSequence))

DimBorrowerAddress (BorrowerAddressKey, BorrowerID, LoanID, LoanSequence)

Basically the relationships look like this:

FactLoans<--FactLoanBorrower-->DimBorrower<--FactBorrowerAddress-->DimBorrowerAddress.

Is this design valid?

thank ahead.

Yes, this may work, however please measure performance on the predicted data volumes you're likelt to have and for a representative query workload.

Thank you

Managing Very Large number of rows in SQL Server

Hi,
We have a need to manage about 900 million rows/ year of data of one
kind. I am looking for suggestions on how best to design a database table(s)
to handle this.
These data is essentially information - across various markets and time. We
need the ability to search across markets/ time and update across markets
and time.
I did some basic tests and extrapolating the data would lead me to believe
that if I were have this as a single table the table size will be over 100
GB.
Thanks
* Get a fast machine with a lot of RAM.
* Have much patience!
* Look into partitioned tables.
* Look into OLAP cubes.
Which is only to say, as the scale of your app challenges the hardware, be
very sure you know what your real requirements are. A minor glitch in design
can cost you big, when your data is big, but what's a glitch and what's a
feature depends on the situation.
Sounds like fun anyway, good luck!
Josh
"shikarishambu" wrote:

> Hi,
> We have a need to manage about 900 million rows/ year of data of one
> kind. I am looking for suggestions on how best to design a database table(s)
> to handle this.
> These data is essentially information - across various markets and time. We
> need the ability to search across markets/ time and update across markets
> and time.
> I did some basic tests and extrapolating the data would lead me to believe
> that if I were have this as a single table the table size will be over 100
> GB.
> Thanks
>
>
|||Hire a pro to do the design and spec work. To do anything other is to set
yourself up for extreme disappointment. The cost now will be a LOT less
than in the future when your system is live and non-performant!! :-)
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"JRStern" <JRStern@.discussions.microsoft.com> wrote in message
news:E4D50946-48C9-4264-B344-E35F8E757FAF@.microsoft.com...[vbcol=seagreen]
>* Get a fast machine with a lot of RAM.
> * Have much patience!
> * Look into partitioned tables.
> * Look into OLAP cubes.
> Which is only to say, as the scale of your app challenges the hardware, be
> very sure you know what your real requirements are. A minor glitch in
> design
> can cost you big, when your data is big, but what's a glitch and what's a
> feature depends on the situation.
> Sounds like fun anyway, good luck!
> Josh
>
> "shikarishambu" wrote:
|||Indexing and partitioning will be your friend with a huge table such as
this. Also if you find a need to update large amounts of data consider
doing it in batches to avoid lock escalation issues.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"shikarishambu" <shikarishambu70@.hotmail.com> wrote in message
news:%23hJvuDOPIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Hi,
> We have a need to manage about 900 million rows/ year of data of one
> kind. I am looking for suggestions on how best to design a database
> table(s) to handle this.
> These data is essentially information - across various markets and time.
> We need the ability to search across markets/ time and update across
> markets and time.
> I did some basic tests and extrapolating the data would lead me to believe
> that if I were have this as a single table the table size will be over 100
> GB.
> Thanks
>
|||On Wed, 12 Dec 2007 14:49:29 -0600, "TheSQLGuru"
<kgboles@.earthlink.net> wrote:

>Hire a pro to do the design and spec work. To do anything other is to set
>yourself up for extreme disappointment. The cost now will be a LOT less
>than in the future when your system is live and non-performant!! :-)
Never time to do it right, always time to do it over.
J.
|||Well, up to the point where it stops functioning or meeting SLAs. Then they
call for me (or another performance consultant) in a panic!! ;)
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:sls1m3liavtoscl3l9doi5ajrg2ak2rs7j@.4ax.com...
> On Wed, 12 Dec 2007 14:49:29 -0600, "TheSQLGuru"
> <kgboles@.earthlink.net> wrote:
>
> Never time to do it right, always time to do it over.
> J.
>
|||On Dec 12, 11:03 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
wrote:
> Hi,
> We have a need to manage about 900 million rows/ year of data of one
> kind. I am looking for suggestions on how best to design a database table(s)
> to handle this.
> These data is essentially information - across various markets and time. We
> need the ability to search across markets/ time and update across markets
> and time.
> I did some basic tests and extrapolating the data would lead me to believe
> that if I were have this as a single table the table size will be over 100
> GB.
> Thanks
Maybe you can design your database so it vertically partitions your
data into 90 tables with 10 milion records per table.

Managing Very Large number of rows in SQL Server

Hi,
We have a need to manage about 900 million rows/ year of data of one
kind. I am looking for suggestions on how best to design a database table(s)
to handle this.
These data is essentially information - across various markets and time. We
need the ability to search across markets/ time and update across markets
and time.
I did some basic tests and extrapolating the data would lead me to believe
that if I were have this as a single table the table size will be over 100
GB.
Thanks* Get a fast machine with a lot of RAM.
* Have much patience!
* Look into partitioned tables.
* Look into OLAP cubes.
Which is only to say, as the scale of your app challenges the hardware, be
very sure you know what your real requirements are. A minor glitch in desig
n
can cost you big, when your data is big, but what's a glitch and what's a
feature depends on the situation.
Sounds like fun anyway, good luck!
Josh
"shikarishambu" wrote:

> Hi,
> We have a need to manage about 900 million rows/ year of data of one
> kind. I am looking for suggestions on how best to design a database table(
s)
> to handle this.
> These data is essentially information - across various markets and time. W
e
> need the ability to search across markets/ time and update across markets
> and time.
> I did some basic tests and extrapolating the data would lead me to believe
> that if I were have this as a single table the table size will be over 100
> GB.
> Thanks
>
>|||Hire a pro to do the design and spec work. To do anything other is to set
yourself up for extreme disappointment. The cost now will be a LOT less
than in the future when your system is live and non-performant!! :-)
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"JRStern" <JRStern@.discussions.microsoft.com> wrote in message
news:E4D50946-48C9-4264-B344-E35F8E757FAF@.microsoft.com...[vbcol=seagreen]
>* Get a fast machine with a lot of RAM.
> * Have much patience!
> * Look into partitioned tables.
> * Look into OLAP cubes.
> Which is only to say, as the scale of your app challenges the hardware, be
> very sure you know what your real requirements are. A minor glitch in
> design
> can cost you big, when your data is big, but what's a glitch and what's a
> feature depends on the situation.
> Sounds like fun anyway, good luck!
> Josh
>
> "shikarishambu" wrote:
>|||Indexing and partitioning will be your friend with a huge table such as
this. Also if you find a need to update large amounts of data consider
doing it in batches to avoid lock escalation issues.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"shikarishambu" <shikarishambu70@.hotmail.com> wrote in message
news:%23hJvuDOPIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Hi,
> We have a need to manage about 900 million rows/ year of data of one
> kind. I am looking for suggestions on how best to design a database
> table(s) to handle this.
> These data is essentially information - across various markets and time.
> We need the ability to search across markets/ time and update across
> markets and time.
> I did some basic tests and extrapolating the data would lead me to believe
> that if I were have this as a single table the table size will be over 100
> GB.
> Thanks
>|||On Wed, 12 Dec 2007 14:49:29 -0600, "TheSQLGuru"
<kgboles@.earthlink.net> wrote:

>Hire a pro to do the design and spec work. To do anything other is to set
>yourself up for extreme disappointment. The cost now will be a LOT less
>than in the future when your system is live and non-performant!! :-)
Never time to do it right, always time to do it over.
J.|||Well, up to the point where it stops functioning or meeting SLAs. Then they
call for me (or another performance consultant) in a panic!! ;)
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:sls1m3liavtoscl3l9doi5ajrg2ak2rs7j@.
4ax.com...
> On Wed, 12 Dec 2007 14:49:29 -0600, "TheSQLGuru"
> <kgboles@.earthlink.net> wrote:
>
> Never time to do it right, always time to do it over.
> J.
>|||On Dec 12, 11:03 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
wrote:
> Hi,
> We have a need to manage about 900 million rows/ year of data of one
> kind. I am looking for suggestions on how best to design a database table(
s)
> to handle this.
> These data is essentially information - across various markets and time. W
e
> need the ability to search across markets/ time and update across markets
> and time.
> I did some basic tests and extrapolating the data would lead me to believe
> that if I were have this as a single table the table size will be over 100
> GB.
> Thanks
Maybe you can design your database so it vertically partitions your
data into 90 tables with 10 milion records per table.

Managing Very Large number of rows in SQL Server

Hi,
We have a need to manage about 900 million rows/ year of data of one
kind. I am looking for suggestions on how best to design a database table(s)
to handle this.
These data is essentially information - across various markets and time. We
need the ability to search across markets/ time and update across markets
and time.
I did some basic tests and extrapolating the data would lead me to believe
that if I were have this as a single table the table size will be over 100
GB.
Thanks* Get a fast machine with a lot of RAM.
* Have much patience!
* Look into partitioned tables.
* Look into OLAP cubes.
Which is only to say, as the scale of your app challenges the hardware, be
very sure you know what your real requirements are. A minor glitch in design
can cost you big, when your data is big, but what's a glitch and what's a
feature depends on the situation.
Sounds like fun anyway, good luck!
Josh
"shikarishambu" wrote:
> Hi,
> We have a need to manage about 900 million rows/ year of data of one
> kind. I am looking for suggestions on how best to design a database table(s)
> to handle this.
> These data is essentially information - across various markets and time. We
> need the ability to search across markets/ time and update across markets
> and time.
> I did some basic tests and extrapolating the data would lead me to believe
> that if I were have this as a single table the table size will be over 100
> GB.
> Thanks
>
>|||Hire a pro to do the design and spec work. To do anything other is to set
yourself up for extreme disappointment. The cost now will be a LOT less
than in the future when your system is live and non-performant!! :-)
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"JRStern" <JRStern@.discussions.microsoft.com> wrote in message
news:E4D50946-48C9-4264-B344-E35F8E757FAF@.microsoft.com...
>* Get a fast machine with a lot of RAM.
> * Have much patience!
> * Look into partitioned tables.
> * Look into OLAP cubes.
> Which is only to say, as the scale of your app challenges the hardware, be
> very sure you know what your real requirements are. A minor glitch in
> design
> can cost you big, when your data is big, but what's a glitch and what's a
> feature depends on the situation.
> Sounds like fun anyway, good luck!
> Josh
>
> "shikarishambu" wrote:
>> Hi,
>> We have a need to manage about 900 million rows/ year of data of one
>> kind. I am looking for suggestions on how best to design a database
>> table(s)
>> to handle this.
>> These data is essentially information - across various markets and time.
>> We
>> need the ability to search across markets/ time and update across markets
>> and time.
>> I did some basic tests and extrapolating the data would lead me to
>> believe
>> that if I were have this as a single table the table size will be over
>> 100
>> GB.
>> Thanks
>>|||Indexing and partitioning will be your friend with a huge table such as
this. Also if you find a need to update large amounts of data consider
doing it in batches to avoid lock escalation issues.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"shikarishambu" <shikarishambu70@.hotmail.com> wrote in message
news:%23hJvuDOPIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Hi,
> We have a need to manage about 900 million rows/ year of data of one
> kind. I am looking for suggestions on how best to design a database
> table(s) to handle this.
> These data is essentially information - across various markets and time.
> We need the ability to search across markets/ time and update across
> markets and time.
> I did some basic tests and extrapolating the data would lead me to believe
> that if I were have this as a single table the table size will be over 100
> GB.
> Thanks
>|||On Wed, 12 Dec 2007 14:49:29 -0600, "TheSQLGuru"
<kgboles@.earthlink.net> wrote:
>Hire a pro to do the design and spec work. To do anything other is to set
>yourself up for extreme disappointment. The cost now will be a LOT less
>than in the future when your system is live and non-performant!! :-)
Never time to do it right, always time to do it over.
J.|||Well, up to the point where it stops functioning or meeting SLAs. Then they
call for me (or another performance consultant) in a panic!! ;)
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:sls1m3liavtoscl3l9doi5ajrg2ak2rs7j@.4ax.com...
> On Wed, 12 Dec 2007 14:49:29 -0600, "TheSQLGuru"
> <kgboles@.earthlink.net> wrote:
>>Hire a pro to do the design and spec work. To do anything other is to set
>>yourself up for extreme disappointment. The cost now will be a LOT less
>>than in the future when your system is live and non-performant!! :-)
> Never time to do it right, always time to do it over.
> J.
>|||On Dec 12, 11:03 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
wrote:
> Hi,
> We have a need to manage about 900 million rows/ year of data of one
> kind. I am looking for suggestions on how best to design a database table(s)
> to handle this.
> These data is essentially information - across various markets and time. We
> need the ability to search across markets/ time and update across markets
> and time.
> I did some basic tests and extrapolating the data would lead me to believe
> that if I were have this as a single table the table size will be over 100
> GB.
> Thanks
Maybe you can design your database so it vertically partitions your
data into 90 tables with 10 milion records per table.