Relationships, keys, UniqueIDs - why... really?!

I've done my database studies and know about normalizing, relationships, primary keys and so forth. However, when it comes to actual developing of web sites, I've always skipped setting up relationships, and at times, the primary and (especially) foreign keys.

Now I'm designing my first db in Asp.Net 2, and the thought struck me - Why should I do this?

I guess the reason for setting up relationships is that the website should not get any trouble if rules are violated or poorly written, that is, data is input in an Orders table but not the Oredered_products table or the like. The only reason I can think of for using primary keys (when not defining relationships anyway) is to get the Insert, Delete, and Update statements of GridViews etc to work.

Or am I missing something? For many years, I've been told that the keys plus relationships taken together speed up the SQL queries, but, honestly, I can't see why they should. And I guess millions of web developers do like I do - skip the relationship thing altogether.

Please enlighten me, someone! (Or let's have an interesting discussion on this topic - if there is something to discuss, that is!)

Pettrer

pettrer:

Or am I missing something? For many years, I've been told that the keys plus relationships taken together speed up the SQL queries, but, honestly, I can't see why they should. And I guess millions of web developers do like I do - skip the relationship thing altogether.

Actaully indexes speed up SQL queries, keys are only constraints used to enforce the integrity of database (but keep in mind in SQL Server a unique,cluster index is automatically created when a PRIMARY KEY) . And I guess relationships you mentioned should be about data tables in application, or reference integrity between database tables. Here are some links that may help you understand:

Constraints:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_0777.asp

Indexes:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_30s5.asp

Creating an Index:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_8185.asp

|||

Thanks a lot for the links!

P

Relational DB Model for an Engineering applicaiton

I'm not sure where to post this - but, I'm a DBA trying to do some modeling for a client developing an Engineering applciation.

There is a lot of interaction at the application layer between external systems and the database - I'm trying to find some ideas or templates of how others designing for a similar functionality did this.

THere are real-time displays for engineering equipment where the database must interact with different states of the equipment. The user enters search criteria for data stored in the database, and results and real-time attributes are displayed.

THis seems to be similar to a manufacturing model, but I'm struggling. I'm used to doing models for OLTP or business reporting databases. Part of the problem may be that there doesn't seem to be a good way to translate relational concepts to engineers. There are a lot of requests for bit fields.

Can anyone reccomend a reference I might look at to get some ideas on how to mesh the modeling of external processes with the database? A simple flow model doesn't seem to work (either technically or as a communication tool - I'm not sure which)

If you posted this on data mining zone I think you cant do he following things according the field:

If you can track this interaction "at the application layer between external systems and the database" you ca analyze this collected data and find some commonly behaviors of your sistems. Let's think at all the customers'bill in an retail magazine:these can say what customers like to buy.

In your case you can know:

- what' happend time to time at with these interactions;

-the links between differents states of equipments and differents real-time attributes .

After finding the behaviors follow predicting future situations.

|||

We used Winform WireFrame application with a reporting component that includes reports with the engineering diagrams, the most important thing we had access to at least ten engineers who look are what we have done and make corrections. The database used only concatenated natural keys and we the developer cannot create table and columns but we have DBO permissions. This is a fortune 10 company and we were rewriting an existing application for a new project it will be a lot of work and many developers, at one time we were about 15 just writing code.

http://www.boxesandarrows.com/view/wireframe_annotations_in_visio_special_deliverable_11

Relate two databases

I am developing an application which consists of two separate products
that both use SQL Server 2000 to store data (database A and database
B). In the application logic we define relations between objects in
these products.
Is it possible to ensure that when restoring a database from a backup,
the other database is automatically restored at the same time to keep
data consistent between the two databases?
What I want to avoid is having a DBA restoring one database and
forgetting the other, thereby creating inconsistencies.
Kind regards,
Martin
Hi,
You can write a script to restore both the databases in order. So instead of
doing a manual restore you could just run the
restore script which loads both the database.
Some thing like:-
Alter database <db1> set single_user with rollback immediate
go
Restore database <db1> from disk='c:\backup\file_name.bak'
go
Alter database <db1> set Multi_user
go
Alter database <db2> set single_user with rollback immediate
go
Restore database <db2> from disk='c:\backup\file_name.bak'
go
Alter database <db2> set Multi_user
Thanks
Hari
MCDBA
"Martin Perfelian" <zonk99@.hotmail.com> wrote in message
news:b9c2729.0408110037.738ea8c2@.posting.google.co m...
> I am developing an application which consists of two separate products
> that both use SQL Server 2000 to store data (database A and database
> B). In the application logic we define relations between objects in
> these products.
> Is it possible to ensure that when restoring a database from a backup,
> the other database is automatically restored at the same time to keep
> data consistent between the two databases?
> What I want to avoid is having a DBA restoring one database and
> forgetting the other, thereby creating inconsistencies.
> Kind regards,
> Martin
|||Even tho Hari describes the correct method, you may still have problems...
Databases are backed up independently of one another. You must use the logs
and probably, roll both forward to the same point in time, otherwise you may
still get mis-matches
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Martin Perfelian" <zonk99@.hotmail.com> wrote in message
news:b9c2729.0408110037.738ea8c2@.posting.google.co m...
> I am developing an application which consists of two separate products
> that both use SQL Server 2000 to store data (database A and database
> B). In the application logic we define relations between objects in
> these products.
> Is it possible to ensure that when restoring a database from a backup,
> the other database is automatically restored at the same time to keep
> data consistent between the two databases?
> What I want to avoid is having a DBA restoring one database and
> forgetting the other, thereby creating inconsistencies.
> Kind regards,
> Martin

Relate two databases

I am developing an application which consists of two separate products
that both use SQL Server 2000 to store data (database A and database
B). In the application logic we define relations between objects in
these products.
Is it possible to ensure that when restoring a database from a backup,
the other database is automatically restored at the same time to keep
data consistent between the two databases?
What I want to avoid is having a DBA restoring one database and
forgetting the other, thereby creating inconsistencies.
Kind regards,
MartinHi,
You can write a script to restore both the databases in order. So instead of
doing a manual restore you could just run the
restore script which loads both the database.
Some thing like:-
Alter database <db1> set single_user with rollback immediate
go
Restore database <db1> from disk='c:\backup\file_name.bak'
go
Alter database <db1> set Multi_user
go
Alter database <db2> set single_user with rollback immediate
go
Restore database <db2> from disk='c:\backup\file_name.bak'
go
Alter database <db2> set Multi_user
Thanks
Hari
MCDBA
"Martin Perfelian" <zonk99@.hotmail.com> wrote in message
news:b9c2729.0408110037.738ea8c2@.posting.google.com...
> I am developing an application which consists of two separate products
> that both use SQL Server 2000 to store data (database A and database
> B). In the application logic we define relations between objects in
> these products.
> Is it possible to ensure that when restoring a database from a backup,
> the other database is automatically restored at the same time to keep
> data consistent between the two databases?
> What I want to avoid is having a DBA restoring one database and
> forgetting the other, thereby creating inconsistencies.
> Kind regards,
> Martin|||Even tho Hari describes the correct method, you may still have problems...
Databases are backed up independently of one another. You must use the logs
and probably, roll both forward to the same point in time, otherwise you may
still get mis-matches
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Martin Perfelian" <zonk99@.hotmail.com> wrote in message
news:b9c2729.0408110037.738ea8c2@.posting.google.com...
> I am developing an application which consists of two separate products
> that both use SQL Server 2000 to store data (database A and database
> B). In the application logic we define relations between objects in
> these products.
> Is it possible to ensure that when restoring a database from a backup,
> the other database is automatically restored at the same time to keep
> data consistent between the two databases?
> What I want to avoid is having a DBA restoring one database and
> forgetting the other, thereby creating inconsistencies.
> Kind regards,
> Martinsql

Relate two databases

I am developing an application which consists of two separate products
that both use SQL Server 2000 to store data (database A and database
B). In the application logic we define relations between objects in
these products.
Is it possible to ensure that when restoring a database from a backup,
the other database is automatically restored at the same time to keep
data consistent between the two databases?
What I want to avoid is having a DBA restoring one database and
forgetting the other, thereby creating inconsistencies.
Kind regards,
MartinHi,
You can write a script to restore both the databases in order. So instead of
doing a manual restore you could just run the
restore script which loads both the database.
Some thing like:-
Alter database <db1> set single_user with rollback immediate
go
Restore database <db1> from disk='c:\backup\file_name.bak'
go
Alter database <db1> set Multi_user
go
Alter database <db2> set single_user with rollback immediate
go
Restore database <db2> from disk='c:\backup\file_name.bak'
go
Alter database <db2> set Multi_user
Thanks
Hari
MCDBA
"Martin Perfelian" <zonk99@.hotmail.com> wrote in message
news:b9c2729.0408110037.738ea8c2@.posting.google.com...
> I am developing an application which consists of two separate products
> that both use SQL Server 2000 to store data (database A and database
> B). In the application logic we define relations between objects in
> these products.
> Is it possible to ensure that when restoring a database from a backup,
> the other database is automatically restored at the same time to keep
> data consistent between the two databases?
> What I want to avoid is having a DBA restoring one database and
> forgetting the other, thereby creating inconsistencies.
> Kind regards,
> Martin|||Even tho Hari describes the correct method, you may still have problems...
Databases are backed up independently of one another. You must use the logs
and probably, roll both forward to the same point in time, otherwise you may
still get mis-matches
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Martin Perfelian" <zonk99@.hotmail.com> wrote in message
news:b9c2729.0408110037.738ea8c2@.posting.google.com...
> I am developing an application which consists of two separate products
> that both use SQL Server 2000 to store data (database A and database
> B). In the application logic we define relations between objects in
> these products.
> Is it possible to ensure that when restoring a database from a backup,
> the other database is automatically restored at the same time to keep
> data consistent between the two databases?
> What I want to avoid is having a DBA restoring one database and
> forgetting the other, thereby creating inconsistencies.
> Kind regards,
> Martin

Re-indexing required?

Hi, I'm developing a database driven application that, besides everything
else, it keeps a log file of all the actions a user has taken during his use
of the app.
This log file is stored in a database table that has a primary key of type
"bigint" that auto increments (1, 1).
If ~100 to ~500 actions (insertions, deletions) are made to this table per
day, how long before I need to re-index the table? Do I need to re-index it
at all?
Thanks in advance,
Peter
pnp,
When are your maintenance windows? Do you have ANY maintenance windows? If you get a chance it would be good to recreate your indexes using the CREATE INDEX statement and the DROP_EXISTING clause - however test this for performance against DBCC DBREINDEX.
Remember that these are OFFLINE operations and will lock tables.
If you don't have a maintenance window, then measure your defragmentation using DBCC SHOWCONTIG. Based on a value acceptable to you, you can rebuild your index with DBCC INDEXDEFRAG - this is an ONLINE operation and will not lock tables, however it is not
as thorough as the other methods.
My advice would be to run DBCC SHOWCONTIG first before doing a rebuild, and then decide when to do it based on your maintenance windows. From the activity you describe it sounds like you may need to monitor it daily with DBCC SHOWCONTIG.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
|||Hi,
Execute the below command with in the database to identify the
fragmentation,
DBCC SHOWCONTIG ('table_name') WITH FAST
DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table
fragmentation occurs through the process of data modifications (INSERT,
UPDATE, and DELETE statements) made against the table. This
will cause additional page reads results in slow performance.
How to over come the Fragmentation:
1. Drop and re-create a clustered index.
2. DBCC INDEXDEFRAG (Refer books online)
Have a look into DBCC SHOWCONTIG in books online for more information.
Thanks
Hari
MCDBA
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm developing a database driven application that, besides everything
> else, it keeps a log file of all the actions a user has taken during his
use
> of the app.
> This log file is stored in a database table that has a primary key of type
> "bigint" that auto increments (1, 1).
> If ~100 to ~500 actions (insertions, deletions) are made to this table per
> day, how long before I need to re-index the table? Do I need to re-index
it
> at all?
> Thanks in advance,
> Peter
>
|||On a slighly different thread.
I'd be curious to know how SQL Server indexes deal with incremental keys.
Other RDBMS implemented hash indexes as btrees can become lopsided with
these keys.
Paul Cahill
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eoOuuoJGEHA.1180@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Execute the below command with in the database to identify the
> fragmentation,
> DBCC SHOWCONTIG ('table_name') WITH FAST
> DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table
> fragmentation occurs through the process of data modifications (INSERT,
> UPDATE, and DELETE statements) made against the table. This
> will cause additional page reads results in slow performance.
> How to over come the Fragmentation:
> 1. Drop and re-create a clustered index.
> 2. DBCC INDEXDEFRAG (Refer books online)
> Have a look into DBCC SHOWCONTIG in books online for more information.
> Thanks
> Hari
> MCDBA
>
> "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
> news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
everything
> use
type
per
> it
>
|||To add to all the other (sound) advice, please checkout the excellent
whitepaper at
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
It gives extensive details on how to diagnose and cope with fragmentation,
including working out which indexes to focus on and even whether you need to
bother, based on your workload.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm developing a database driven application that, besides everything
> else, it keeps a log file of all the actions a user has taken during his
use
> of the app.
> This log file is stored in a database table that has a primary key of type
> "bigint" that auto increments (1, 1).
> If ~100 to ~500 actions (insertions, deletions) are made to this table per
> day, how long before I need to re-index the table? Do I need to re-index
it
> at all?
> Thanks in advance,
> Peter
>

Re-indexing required?

Hi, I'm developing a database driven application that, besides everything
else, it keeps a log file of all the actions a user has taken during his use
of the app.
This log file is stored in a database table that has a primary key of type
"bigint" that auto increments (1, 1).
If ~100 to ~500 actions (insertions, deletions) are made to this table per
day, how long before I need to re-index the table? Do I need to re-index it
at all?
Thanks in advance,
Peterpnp,
When are your maintenance windows? Do you have ANY maintenance windows? If y
ou get a chance it would be good to recreate your indexes using the CREATE I
NDEX statement and the DROP_EXISTING clause - however test this for performa
nce against DBCC DBREINDEX.
Remember that these are OFFLINE operations and will lock tables.
If you don't have a maintenance window, then measure your defragmentation us
ing DBCC SHOWCONTIG. Based on a value acceptable to you, you can rebuild you
r index with DBCC INDEXDEFRAG - this is an ONLINE operation and will not loc
k tables, however it is not
as thorough as the other methods.
My advice would be to run DBCC SHOWCONTIG first before doing a rebuild, and
then decide when to do it based on your maintenance windows. From the activi
ty you describe it sounds like you may need to monitor it daily with DBCC SH
OWCONTIG.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk|||Hi,
Execute the below command with in the database to identify the
fragmentation,
DBCC SHOWCONTIG ('table_name') WITH FAST
DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table
fragmentation occurs through the process of data modifications (INSERT,
UPDATE, and DELETE statements) made against the table. This
will cause additional page reads results in slow performance.
How to over come the Fragmentation:
1. Drop and re-create a clustered index.
2. DBCC INDEXDEFRAG (Refer books online)
Have a look into DBCC SHOWCONTIG in books online for more information.
Thanks
Hari
MCDBA
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm developing a database driven application that, besides everything
> else, it keeps a log file of all the actions a user has taken during his
use
> of the app.
> This log file is stored in a database table that has a primary key of type
> "bigint" that auto increments (1, 1).
> If ~100 to ~500 actions (insertions, deletions) are made to this table per
> day, how long before I need to re-index the table? Do I need to re-index
it
> at all?
> Thanks in advance,
> Peter
>|||On a slighly different thread.
I'd be curious to know how SQL Server indexes deal with incremental keys.
Other RDBMS implemented hash indexes as btrees can become lopsided with
these keys.
Paul Cahill
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eoOuuoJGEHA.1180@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Execute the below command with in the database to identify the
> fragmentation,
> DBCC SHOWCONTIG ('table_name') WITH FAST
> DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table
> fragmentation occurs through the process of data modifications (INSERT,
> UPDATE, and DELETE statements) made against the table. This
> will cause additional page reads results in slow performance.
> How to over come the Fragmentation:
> 1. Drop and re-create a clustered index.
> 2. DBCC INDEXDEFRAG (Refer books online)
> Have a look into DBCC SHOWCONTIG in books online for more information.
> Thanks
> Hari
> MCDBA
>
> "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
> news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
everything
> use
type
per
> it
>|||To add to all the other (sound) advice, please checkout the excellent
whitepaper at
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
It gives extensive details on how to diagnose and cope with fragmentation,
including working out which indexes to focus on and even whether you need to
bother, based on your workload.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm developing a database driven application that, besides everything
> else, it keeps a log file of all the actions a user has taken during his
use
> of the app.
> This log file is stored in a database table that has a primary key of type
> "bigint" that auto increments (1, 1).
> If ~100 to ~500 actions (insertions, deletions) are made to this table per
> day, how long before I need to re-index the table? Do I need to re-index
it
> at all?
> Thanks in advance,
> Peter
>