Database in recovery

Hi,
I'm not that familiar with SQL Server, just mainly use it for
development. Anyway I was modifying some keys in some tables last
night. Everything went fine and I continued to use the database.
When i woke this morning and turned on the computer, my database
was in recovery. I am unable to do anything to get it back.
I have tried a few things to alter the database, (ie. set offline, set
single user) but I get an error saying
"Msg 5011, Level 14, State 7, Line 1
User does not have permission to alter database 'trace05e' or the
database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed."
when I do an sp_who I get
spid ecid status loginame
hostname
blk dbname
cmd request_id
-- -- --
------
------
------
-- --
1 0 background sa
0 NULL
RESOURCE MONITOR 0
2 0 suspended sa
0 NULL
LOG WRITER 0
3 0 background sa
0 NULL
LAZY WRITER 0
4 0 background sa
0 master
UNKNOWN TOKEN 0
5 0 background sa
0 NULL
LOCK MONITOR 0
6 0 sleeping sa
0 master
TASK MANAGER 0
7 0 background sa
0 master
TRACE QUEUE TASK 0
8 0 sleeping sa
0 NULL
UNKNOWN TOKEN 0
9 0 background sa
0 master
BRKR TASK 0
10 0 background sa
0 master
TASK MANAGER 0
11 0 suspended sa
0 master
CHECKPOINT 0
17 0 background sa
0 NULL
UNKNOWN TOKEN 0
19 0 background sa
0 master
BRKR EVENT HNDLR 0
20 0 background sa
0 master
BRKR TASK 0
51 0 sleeping NT AUTHORITY\SYSTEM
computer
0 ReportServer$sqlexpress
AWAITING COMMAND 0
53 0 sleeping computer\User
computer
0 master
AWAITING
COMMAND 0
54 0 runnable computer\User
computer
0
master
SELECT
0
56 0 sleeping computer\User
I can't find any commands to kill the last three processes.
I need to know two things.
1) How can I find out why my database went into recovery mode?
2) How can I get my database back into normal mode?
Thanks a million in advance
JD
I managed to kill some processes and now I have two left, the one I am
using and one more, which is needed as a transport.
i still cannot execute this statment
RESTORE DATABASE trace05e
WITH RECOVERY
I get
Msg 3101, Level 16, State 1, Line 3
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
|||Hi
I can only guess. When you start SQL Server it does perfom the opeartion (in
every databases on the server) 'recovering' by using CHECKPOINT command that
means that all transactions are resided in the cache should be REDO/UNDO
depends on its status.
<the_kiddie98@.yahoo.com> wrote in message
news:1141293232.548857.223880@.t39g2000cwt.googlegr oups.com...
> Hi,
> I'm not that familiar with SQL Server, just mainly use it for
> development. Anyway I was modifying some keys in some tables last
> night. Everything went fine and I continued to use the database.
> When i woke this morning and turned on the computer, my database
> was in recovery. I am unable to do anything to get it back.
> I have tried a few things to alter the database, (ie. set offline, set
> single user) but I get an error saying
> "Msg 5011, Level 14, State 7, Line 1
> User does not have permission to alter database 'trace05e' or the
> database does not exist.
> Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed."
> when I do an sp_who I get
> spid ecid status loginame
> hostname
> blk dbname
> cmd request_id
> -- -- --
> ------
> ------
> --
> ------
> -- --
> 1 0 background sa
>
> 0 NULL
> RESOURCE MONITOR 0
> 2 0 suspended sa
>
> 0 NULL
> LOG WRITER 0
> 3 0 background sa
>
> 0 NULL
> LAZY WRITER 0
> 4 0 background sa
>
> 0 master
> UNKNOWN TOKEN 0
> 5 0 background sa
>
> 0 NULL
> LOCK MONITOR 0
> 6 0 sleeping sa
>
> 0 master
> TASK MANAGER 0
> 7 0 background sa
>
> 0 master
> TRACE QUEUE TASK 0
> 8 0 sleeping sa
>
> 0 NULL
> UNKNOWN TOKEN 0
> 9 0 background sa
>
> 0 master
> BRKR TASK 0
> 10 0 background sa
>
> 0 master
> TASK MANAGER 0
> 11 0 suspended sa
>
> 0 master
> CHECKPOINT 0
> 17 0 background sa
>
> 0 NULL
> UNKNOWN TOKEN 0
> 19 0 background sa
>
> 0 master
> BRKR EVENT HNDLR 0
> 20 0 background sa
>
> 0 master
> BRKR TASK 0
> 51 0 sleeping NT AUTHORITY\SYSTEM
> computer
> 0 ReportServer$sqlexpress
> AWAITING COMMAND 0
> 53 0 sleeping computer\User
> computer
> 0 master
> AWAITING
> COMMAND 0
> 54 0 runnable computer\User
> computer
> 0
> master
> SELECT
> 0
> 56 0 sleeping computer\User
> I can't find any commands to kill the last three processes.
> I need to know two things.
> 1) How can I find out why my database went into recovery mode?
> 2) How can I get my database back into normal mode?
> Thanks a million in advance
> JD
>
|||SQL Server shouldn't let anyone in to the database if it is in recovery state. Perhaps it is only a
refresh problem in EM/SSMS?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<the_kiddie98@.yahoo.com> wrote in message
news:1141293565.559941.65460@.v46g2000cwv.googlegro ups.com...
>I managed to kill some processes and now I have two left, the one I am
> using and one more, which is needed as a transport.
> i still cannot execute this statment
> RESTORE DATABASE trace05e
> WITH RECOVERY
> I get
> Msg 3101, Level 16, State 1, Line 3
> Exclusive access could not be obtained because the database is in use.
> Msg 3013, Level 16, State 1, Line 3
> RESTORE DATABASE is terminating abnormally.
>
|||Hi Uri,
Sorry for my ignorance but what does that mean. Is there a way I
can rollback the logs to a certain point in time?
Is there any way outside of sql server that I can gain access to
the stored procedures inside in the mdf file?
Thanks
JD
Uri Dimant wrote:
> Hi
> I can only guess. When you start SQL Server it does perfom the opeartion (in
> every databases on the server) 'recovering' by using CHECKPOINT command that
> means that all transactions are resided in the cache should be REDO/UNDO
> depends on its status.
>
|||nope it isn't a refresh error as I have rebooted a few times and I am
still unable to get the database to work.
I have now resorted to removing the instance and re-installing it and
trying to get back an older database. I can't run any commands like
dbcc checkdb even on master, there seems to be invisible connections
all over the place.

Database in recovery

Hi,
I'm not that familiar with SQL Server, just mainly use it for
development. Anyway I was modifying some keys in some tables last
night. Everything went fine and I continued to use the database.
When i woke this morning and turned on the computer, my database
was in recovery. I am unable to do anything to get it back.
I have tried a few things to alter the database, (ie. set offline, set
single user) but I get an error saying
"Msg 5011, Level 14, State 7, Line 1
User does not have permission to alter database 'trace05e' or the
database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed."
when I do an sp_who I get
spid ecid status loginame
hostname
blk dbname
cmd request_id
-- -- --
------
------
--
------
-- --
1 0 background sa
0 NULL
RESOURCE MONITOR 0
2 0 suspended sa
0 NULL
LOG WRITER 0
3 0 background sa
0 NULL
LAZY WRITER 0
4 0 background sa
0 master
UNKNOWN TOKEN 0
5 0 background sa
0 NULL
LOCK MONITOR 0
6 0 sleeping sa
0 master
TASK MANAGER 0
7 0 background sa
0 master
TRACE QUEUE TASK 0
8 0 sleeping sa
0 NULL
UNKNOWN TOKEN 0
9 0 background sa
0 master
BRKR TASK 0
10 0 background sa
0 master
TASK MANAGER 0
11 0 suspended sa
0 master
CHECKPOINT 0
17 0 background sa
0 NULL
UNKNOWN TOKEN 0
19 0 background sa
0 master
BRKR EVENT HNDLR 0
20 0 background sa
0 master
BRKR TASK 0
51 0 sleeping NT AUTHORITY\SYSTEM
computer
0 ReportServer$sqlexpress
AWAITING COMMAND 0
53 0 sleeping computer\User
computer
0 master
AWAITING
COMMAND 0
54 0 runnable computer\User
computer
0
master
SELECT
0
56 0 sleeping computer\User
I can't find any commands to kill the last three processes.
I need to know two things.
1) How can I find out why my database went into recovery mode?
2) How can I get my database back into normal mode?
Thanks a million in advance
JDI managed to kill some processes and now I have two left, the one I am
using and one more, which is needed as a transport.
i still cannot execute this statment
RESTORE DATABASE trace05e
WITH RECOVERY
I get
Msg 3101, Level 16, State 1, Line 3
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.|||Hi
I can only guess. When you start SQL Server it does perfom the opeartion (in
every databases on the server) 'recovering' by using CHECKPOINT command that
means that all transactions are resided in the cache should be REDO/UNDO
depends on its status.
<the_kiddie98@.yahoo.com> wrote in message
news:1141293232.548857.223880@.t39g2000cwt.googlegroups.com...
> Hi,
> I'm not that familiar with SQL Server, just mainly use it for
> development. Anyway I was modifying some keys in some tables last
> night. Everything went fine and I continued to use the database.
> When i woke this morning and turned on the computer, my database
> was in recovery. I am unable to do anything to get it back.
> I have tried a few things to alter the database, (ie. set offline, set
> single user) but I get an error saying
> "Msg 5011, Level 14, State 7, Line 1
> User does not have permission to alter database 'trace05e' or the
> database does not exist.
> Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed."
> when I do an sp_who I get
> spid ecid status loginame
> hostname
> blk dbname
> cmd request_id
> -- -- --
> ------
> ------
> --
> ------
> -- --
> 1 0 background sa
>
> 0 NULL
> RESOURCE MONITOR 0
> 2 0 suspended sa
>
> 0 NULL
> LOG WRITER 0
> 3 0 background sa
>
> 0 NULL
> LAZY WRITER 0
> 4 0 background sa
>
> 0 master
> UNKNOWN TOKEN 0
> 5 0 background sa
>
> 0 NULL
> LOCK MONITOR 0
> 6 0 sleeping sa
>
> 0 master
> TASK MANAGER 0
> 7 0 background sa
>
> 0 master
> TRACE QUEUE TASK 0
> 8 0 sleeping sa
>
> 0 NULL
> UNKNOWN TOKEN 0
> 9 0 background sa
>
> 0 master
> BRKR TASK 0
> 10 0 background sa
>
> 0 master
> TASK MANAGER 0
> 11 0 suspended sa
>
> 0 master
> CHECKPOINT 0
> 17 0 background sa
>
> 0 NULL
> UNKNOWN TOKEN 0
> 19 0 background sa
>
> 0 master
> BRKR EVENT HNDLR 0
> 20 0 background sa
>
> 0 master
> BRKR TASK 0
> 51 0 sleeping NT AUTHORITY\SYSTEM
> computer
> 0 ReportServer$sqlexpress
> AWAITING COMMAND 0
> 53 0 sleeping computer\User
> computer
> 0 master
> AWAITING
> COMMAND 0
> 54 0 runnable computer\User
> computer
> 0
> master
> SELECT
> 0
> 56 0 sleeping computer\User
> I can't find any commands to kill the last three processes.
> I need to know two things.
> 1) How can I find out why my database went into recovery mode?
> 2) How can I get my database back into normal mode?
> Thanks a million in advance
> JD
>|||SQL Server shouldn't let anyone in to the database if it is in recovery state. Perhaps it is only a
refresh problem in EM/SSMS?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<the_kiddie98@.yahoo.com> wrote in message
news:1141293565.559941.65460@.v46g2000cwv.googlegroups.com...
>I managed to kill some processes and now I have two left, the one I am
> using and one more, which is needed as a transport.
> i still cannot execute this statment
> RESTORE DATABASE trace05e
> WITH RECOVERY
> I get
> Msg 3101, Level 16, State 1, Line 3
> Exclusive access could not be obtained because the database is in use.
> Msg 3013, Level 16, State 1, Line 3
> RESTORE DATABASE is terminating abnormally.
>|||Hi Uri,
Sorry for my ignorance but what does that mean. Is there a way I
can rollback the logs to a certain point in time?
Is there any way outside of sql server that I can gain access to
the stored procedures inside in the mdf file?
Thanks
JD
Uri Dimant wrote:
> Hi
> I can only guess. When you start SQL Server it does perfom the opeartion (in
> every databases on the server) 'recovering' by using CHECKPOINT command that
> means that all transactions are resided in the cache should be REDO/UNDO
> depends on its status.
>|||nope it isn't a refresh error as I have rebooted a few times and I am
still unable to get the database to work.
I have now resorted to removing the instance and re-installing it and
trying to get back an older database. I can't run any commands like
dbcc checkdb even on master, there seems to be invisible connections
all over the place.

Database in recovery

Hi,
I'm not that familiar with SQL Server, just mainly use it for
development. Anyway I was modifying some keys in some tables last
night. Everything went fine and I continued to use the database.
When i woke this morning and turned on the computer, my database
was in recovery. I am unable to do anything to get it back.
I have tried a few things to alter the database, (ie. set offline, set
single user) but I get an error saying
"Msg 5011, Level 14, State 7, Line 1
User does not have permission to alter database 'trace05e' or the
database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed."
when I do an sp_who I get
spid ecid status loginame
hostname
blk dbname
cmd request_id
-- -- --
----
----
----
----
--
----
----
-- --
1 0 background sa
0 NULL
RESOURCE MONITOR 0
2 0 suspended sa
0 NULL
LOG WRITER 0
3 0 background sa
0 NULL
LAZY WRITER 0
4 0 background sa
0 master
UNKNOWN TOKEN 0
5 0 background sa
0 NULL
LOCK MONITOR 0
6 0 sleeping sa
0 master
TASK MANAGER 0
7 0 background sa
0 master
TRACE QUEUE TASK 0
8 0 sleeping sa
0 NULL
UNKNOWN TOKEN 0
9 0 background sa
0 master
BRKR TASK 0
10 0 background sa
0 master
TASK MANAGER 0
11 0 suspended sa
0 master
CHECKPOINT 0
17 0 background sa
0 NULL
UNKNOWN TOKEN 0
19 0 background sa
0 master
BRKR EVENT HNDLR 0
20 0 background sa
0 master
BRKR TASK 0
51 0 sleeping NT AUTHORITY\SYSTEM
computer
0 ReportServer$sqlexpress
AWAITING COMMAND 0
53 0 sleeping computer\User
computer
0 master
AWAITING
COMMAND 0
54 0 runnable computer\User
computer
0
master
SELECT
0
56 0 sleeping computer\User
I can't find any commands to kill the last three processes.
I need to know two things.
1) How can I find out why my database went into recovery mode?
2) How can I get my database back into normal mode?
Thanks a million in advance
JDI managed to kill some processes and now I have two left, the one I am
using and one more, which is needed as a transport.
i still cannot execute this statment
RESTORE DATABASE trace05e
WITH RECOVERY
I get
Msg 3101, Level 16, State 1, Line 3
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.|||Hi
I can only guess. When you start SQL Server it does perfom the opeartion (in
every databases on the server) 'recovering' by using CHECKPOINT command that
means that all transactions are resided in the cache should be REDO/UNDO
depends on its status.
<the_kiddie98@.yahoo.com> wrote in message
news:1141293232.548857.223880@.t39g2000cwt.googlegroups.com...
> Hi,
> I'm not that familiar with SQL Server, just mainly use it for
> development. Anyway I was modifying some keys in some tables last
> night. Everything went fine and I continued to use the database.
> When i woke this morning and turned on the computer, my database
> was in recovery. I am unable to do anything to get it back.
> I have tried a few things to alter the database, (ie. set offline, set
> single user) but I get an error saying
> "Msg 5011, Level 14, State 7, Line 1
> User does not have permission to alter database 'trace05e' or the
> database does not exist.
> Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed."
> when I do an sp_who I get
> spid ecid status loginame
> hostname
> blk dbname
> cmd request_id
> -- -- --
> ----
---
> ----
---
> --
> ----
---
> -- --
> 1 0 background sa
>
> 0 NULL
> RESOURCE MONITOR 0
> 2 0 suspended sa
>
> 0 NULL
> LOG WRITER 0
> 3 0 background sa
>
> 0 NULL
> LAZY WRITER 0
> 4 0 background sa
>
> 0 master
> UNKNOWN TOKEN 0
> 5 0 background sa
>
> 0 NULL
> LOCK MONITOR 0
> 6 0 sleeping sa
>
> 0 master
> TASK MANAGER 0
> 7 0 background sa
>
> 0 master
> TRACE QUEUE TASK 0
> 8 0 sleeping sa
>
> 0 NULL
> UNKNOWN TOKEN 0
> 9 0 background sa
>
> 0 master
> BRKR TASK 0
> 10 0 background sa
>
> 0 master
> TASK MANAGER 0
> 11 0 suspended sa
>
> 0 master
> CHECKPOINT 0
> 17 0 background sa
>
> 0 NULL
> UNKNOWN TOKEN 0
> 19 0 background sa
>
> 0 master
> BRKR EVENT HNDLR 0
> 20 0 background sa
>
> 0 master
> BRKR TASK 0
> 51 0 sleeping NT AUTHORITY\SYSTEM
> computer
> 0 ReportServer$sqlexpress
> AWAITING COMMAND 0
> 53 0 sleeping computer\User
> computer
> 0 master
> AWAITING
> COMMAND 0
> 54 0 runnable computer\User
> computer
> 0
> master
> SELECT
> 0
> 56 0 sleeping computer\User
> I can't find any commands to kill the last three processes.
> I need to know two things.
> 1) How can I find out why my database went into recovery mode?
> 2) How can I get my database back into normal mode?
> Thanks a million in advance
> JD
>|||SQL Server shouldn't let anyone in to the database if it is in recovery stat
e. Perhaps it is only a
refresh problem in EM/SSMS?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<the_kiddie98@.yahoo.com> wrote in message
news:1141293565.559941.65460@.v46g2000cwv.googlegroups.com...
>I managed to kill some processes and now I have two left, the one I am
> using and one more, which is needed as a transport.
> i still cannot execute this statment
> RESTORE DATABASE trace05e
> WITH RECOVERY
> I get
> Msg 3101, Level 16, State 1, Line 3
> Exclusive access could not be obtained because the database is in use.
> Msg 3013, Level 16, State 1, Line 3
> RESTORE DATABASE is terminating abnormally.
>|||Hi Uri,
Sorry for my ignorance but what does that mean. Is there a way I
can rollback the logs to a certain point in time?
Is there any way outside of sql server that I can gain access to
the stored procedures inside in the mdf file?
Thanks
JD
Uri Dimant wrote:
> Hi
> I can only guess. When you start SQL Server it does perfom the opeartion (
in
> every databases on the server) 'recovering' by using CHECKPOINT command th
at
> means that all transactions are resided in the cache should be REDO/UNDO
> depends on its status.
>|||nope it isn't a refresh error as I have rebooted a few times and I am
still unable to get the database to work.
I have now resorted to removing the instance and re-installing it and
trying to get back an older database. I can't run any commands like
dbcc checkdb even on master, there seems to be invisible connections
all over the place.sql

Database Documentation

Is there a way, or a program or utility out there, that would give me a list
of all the constraints, triggers, indexes, relationships, primary keys,
etc., on an entire database or table, so I don't ahve to go into each table
to find primary keys and indexes.? There are all kinds of tools for this. Any database modeling tool
(including Visio) will have a reverse engineering function to allow this.
You could also look at SQLScribe or something like that.
"Rock" <rockisland@.yahoo.com> wrote in message
news:OfD7$bstEHA.1272@.TK2MSFTNGP12.phx.gbl...
> Is there a way, or a program or utility out there, that would give me a
list
> of all the constraints, triggers, indexes, relationships, primary keys,
> etc., on an entire database or table, so I don't ahve to go into each
table
> to find primary keys and indexes.
>|||I guess I was looking for something free.
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:%23fcYsDutEHA.3016@.TK2MSFTNGP12.phx.gbl...
> ? There are all kinds of tools for this. Any database modeling tool
> (including Visio) will have a reverse engineering function to allow this.
> You could also look at SQLScribe or something like that.
>
> "Rock" <rockisland@.yahoo.com> wrote in message
> news:OfD7$bstEHA.1272@.TK2MSFTNGP12.phx.gbl...
> > Is there a way, or a program or utility out there, that would give me a
> list
> > of all the constraints, triggers, indexes, relationships, primary keys,
> > etc., on an entire database or table, so I don't ahve to go into each
> table
> > to find primary keys and indexes.
> >
> >
>|||If very inexpensive will do...
Consider WT3 - one of the SQL Server Tools applications.
http://www.sqlservertools.us
One of the features is automated generation of database documentation.
"Rock" wrote:
> I guess I was looking for something free.
>
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:%23fcYsDutEHA.3016@.TK2MSFTNGP12.phx.gbl...
> > ? There are all kinds of tools for this. Any database modeling tool
> > (including Visio) will have a reverse engineering function to allow this.
> > You could also look at SQLScribe or something like that.
> >
> >
> > "Rock" <rockisland@.yahoo.com> wrote in message
> > news:OfD7$bstEHA.1272@.TK2MSFTNGP12.phx.gbl...
> > > Is there a way, or a program or utility out there, that would give me a
> > list
> > > of all the constraints, triggers, indexes, relationships, primary keys,
> > > etc., on an entire database or table, so I don't ahve to go into each
> > table
> > > to find primary keys and indexes.
> > >
> > >
> >
> >
>
>

Database Diagrams

I have a question concerning the diagramming tool in SQL
Server 2000. On the development server the keys show up
in the table view of the Enterprise Manager and the
diagramming tool works as expected. However, when we
imported the database into production, the keys did not
come forward so the diagramming tool cannot diagram
relationships. The interesting thing is that the
application works as expected, except the keys dont show
up in the table view.
Question...how is this possible? How do I add the keys
to the design without disrupting the application?
ThanxYou are correct in the assumption that the application works due to the
error checking.
I'm working wiht Mike on this problem and somewhere along the line, all of
the keys got dropped in the production database. We want to recreate them
in the development database and then run the scripts against the production
database to create the keys and the relationships that are required. Is
there any way to automatically compare two versions of the database and
generate a batch of the the ALTER TABLE commands? Or am I looking at
writing each statement inividually?
Thanks
Dave
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:uGKT3TRVDHA.2192@.TK2MSFTNGP10.phx.gbl...
> Check if the FKs are in place. Use sp_help and sp_foreignkeys system SPs
to
> check if FKs are present. You can add them, if they are not in place,
using
> the ALTER TABLE command. If the application respects them, if it has input
> validation & error handling, you should have no problem.
> --
> Dejan Sarka, SQL Server MVP
> FAQ from Neil & others at: http://www.sqlserverfaq.com
> Please reply only to the newsgroups.
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
> "Mike" <Mike149@.yahoo.com> wrote in message
> news:312f01c35514$024662e0$a001280a@.phx.gbl...
> > I have a question concerning the diagramming tool in SQL
> > Server 2000. On the development server the keys show up
> > in the table view of the Enterprise Manager and the
> > diagramming tool works as expected. However, when we
> > imported the database into production, the keys did not
> > come forward so the diagramming tool cannot diagram
> > relationships. The interesting thing is that the
> > application works as expected, except the keys dont show
> > up in the table view.
> >
> > Question...how is this possible? How do I add the keys
> > to the design without disrupting the application?
> >
> > Thanx
>

Database Design Question.?

Hey guys,

I'm currently in the design stage of a database, and have a question of how the keys should be set up in two of my tables.

I have an Orders table, which is related to a Customers table, and also to an Images table.
The orders table has OrderID (PK), CustID (FK), and ImageSetID (PK)

The ImageSetID field joins to the Images table which contains ImageSetID (FK), ImageID (PK), and ImagePath

Now, I plan to have multiple images for each order, which all will have a common ImageSetID, and each individual image will have an Unique ImageID.
And I plan to store all the image paths in one table.

Example:

ImageSetId - ImageID - ImagePath
----------------
001 - 001 - C:\Pictures
001 - 002 - C:\Pictures
002 - 001 - C:\Pictures
002 - 002 - C:\Pictures
002 - 003 - C:\Pictures

Now, am I wondering.. Am I able to Have a unique ImageID, relating to the ImageSetID?
But, while still using the same ImageID, but for a different ImageSetID..

The way I plan to use input the data, is by creating a new order, then importing images into that order, but for the ImageSetID to stay the same while I am in the same order, but for the ImageID to change.

If that is not possible, any there any suggestions to how to set that up?

Anything would be appreciated, thank you.

Quote:

Originally Posted by Jackson77

Hey guys,

I'm currently in the design stage of a database, and have a question of how the keys should be set up in two of my tables.

I have an Orders table, which is related to a Customers table, and also to an Images table.
The orders table has OrderID (PK), CustID (FK), and ImageSetID (PK)

The ImageSetID field joins to the Images table which contains ImageSetID (FK), ImageID (PK), and ImagePath

Now, I plan to have multiple images for each order, which all will have a common ImageSetID, and each individual image will have an Unique ImageID.
And I plan to store all the image paths in one table.

Example:

ImageSetId - ImageID - ImagePath
----------------
001 - 001 - C:\Pictures
001 - 002 - C:\Pictures
002 - 001 - C:\Pictures
002 - 002 - C:\Pictures
002 - 003 - C:\Pictures

Now, am I wondering.. Am I able to Have a unique ImageID, relating to the ImageSetID?
But, while still using the same ImageID, but for a different ImageSetID..

The way I plan to use input the data, is by creating a new order, then importing images into that order, but for the ImageSetID to stay the same while I am in the same order, but for the ImageID to change.

If that is not possible, any there any suggestions to how to set that up?

Anything would be appreciated, thank you.

Here's how I would solve this problem.

Three tables.
Image table. pk_ImageID, description, path, etc..
Orders table. pk_OrderID, fk_CustomerID, order stuff, etc...
OrderImage table. pk_OrderImageID,fk_ImageID,fk_OrderID

This will give you the many to many relationship yet keep your image and order table normalized.|||

Quote:

Originally Posted by Infide

Here's how I would solve this problem.

Three tables.
Image table. pk_ImageID, description, path, etc..
Orders table. pk_OrderID, fk_CustomerID, order stuff, etc...
OrderImage table. pk_OrderImageID,fk_ImageID,fk_OrderID

This will give you the many to many relationship yet keep your image and order table normalized.

But, if I were to do this, and store all the Image paths in one table, and by having the ImageID the PK, I wouldn't be able to have two Images with the ImageID of 001, or 002, which I plan to.

Thank you for your suggestion, but I would prefer if the Images were numbered according to the order they were placed under the order, and not just being counted upwards so that Order 010 has ImagedID of 001, 002,003,, and then Order 011 having Images of ImageID's of 004, 005, 006. I would like the numbering to start back at 001 for each new order.

I hope this doesn't confuse anyone.

Thanks again, if you could give me any more suggestions, it would be appreciated.|||Sorry, It came up with an error when I first posted this, and then I refreshed it.
..And now I can't delete this Double post.
My apologies!

Database Design Question

What are the pros and cons of the following two design methods ?
(1) Using foreign keys to form a composite primary key of a child tables -- as in Example.
(2) Using a new key to form a single primary key of a table, and placing parent tables as only foreign keys -- as in Example 2.
Relationships:
Language to Brochure = one-to-many
Brochure to Heading = many-to-many
Heading to Paragraph = one-to-many
-- *** Example 1 COMPOSITE FOREIGN KEY Code ***
CREATE TABLE tbLanguage
(
LanguageId int identity(1,1) not null,
LangNamevarchar(255) not null,
PRIMARY KEY CLUSTERED (LanguageId)
)
go
CREATE TABLE tbBrochure
(
BrochureIdint identity(1,1) not null,
LanguageIdint not null,
Titlevarchar(255) not null
PRIMARY KEY CLUSTERED(BrochureId,LanguageId),
FOREIGN KEY (LanguageId)
REFERENCES tbLanguage(LanguageId)
)
go
CREATE TABLE tbHeading
(
HeadingIdint identity(1,1) not null,
HeadingTextvarchar(1000) not null,
PRIMARY KEY CLUSTERED (HeadingId)
)
go
CREATE TABLE tbBrochureHeadingMap
(
BrochureIdint not null,
LanguageIdint not null,
HeadingIdint not null,
PRIMARY KEY CLUSTERED (BrochureId,LanguageId,HeadingId),
FOREIGN KEY (BrochureId,LanguageId)
REFERENCES tbBrochure (BrochureId,LanguageId),
FOREIGN KEY (HeadingId)
REFERENCES tbHeading (HeadingId)
)
go
CREATE TABLE tbParagraph
(
BrochureIdint not null,
LanguageIdint not null,
HeadingIdint not null,
SequenceNoint not null,
ParagraphTextvarchar(4000) not null,
PRIMARY KEY CLUSTERED (BrochureId,LanguageId,HeadingId,SequenceNo),
FOREIGN KEY (BrochureId,LanguageId)
REFERENCES tbBrochure (BrochureId,LanguageId),
FOREIGN KEY (HeadingId)
REFERENCES tbHeading (HeadingId)
)
go
-- *** Example 2 SINGLE PRIMARY KEY Code (SQL Server 2000) ***
CREATE TABLE tbLanguage
(
LanguageId int identity(1,1) not null,
LangNamevarchar(255) not null,
PRIMARY KEY CLUSTERED (LanguageId)
)
go
CREATE TABLE tbBrochure
(
BrochureIdint identity(1,1) not null,
LanguageIdint not null,
Titlevarchar(255) not null
PRIMARY KEY CLUSTERED(BrochureId),
FOREIGN KEY (LanguageId)
REFERENCES tbLanguage(LanguageId)
)
go
CREATE NONCLUSTERED INDEX ix_tbBrochure_LanguageId ON tbBrochure (LanguageId)
go
CREATE TABLE tbHeading
(
HeadingIdint identity(1,1) not null,
HeadingTextvarchar(1000) not null,
PRIMARY KEY CLUSTERED (HeadingId)
)
go
CREATE TABLE tbBrochureHeadingMap
(
BrochureHeadingMapId int identity(1,1) not null,
BrochureIdint not null,
HeadingIdint not null,
PRIMARY KEY CLUSTERED (BrochureHeadingMapId),
FOREIGN KEY (BrochureId)
REFERENCES tbBrochure (BrochureId),
FOREIGN KEY (HeadingId)
REFERENCES tbHeading (HeadingId)
)
go
CREATE NONCLUSTERED INDEX ix_tbBrochureHeadingMap_BrochureId ON tbBrochureHeadingMap (BrochureId)
go
CREATE NONCLUSTERED INDEX ix_tbBrochureHeadingMap_HeadingId ON tbBrochureHeadingMap (HeadingId)
go
CREATE TABLE tbParagraph
(
ParagraphIdint identity(1,1) not null,
HeadingIdint not null,
SequenceNoint not null,
ParagraphTextvarchar(4000) not null,
PRIMARY KEY CLUSTERED (ParagraphId),
FOREIGN KEY (HeadingId)
REFERENCES tbHeading (HeadingId)
)
go
CREATE NONCLUSTERED INDEX ix_tbParagraph_BrochureId ON tbBrochureHeadingMap (HeadingId)
go
It has been argued that Example 1: COMPOSITE FOREIGN KEY has the following pros, over Example 2:
1) Fewer indexes are needed. Five (5) Indexes in Example 1 instead of Nine (9) in Example 2.
2) Queries can be created with fewer joins.
For example: (one join in Example 1)
SELECT b.Title,
p.ParagraphText
FROM tbBrochure b
INNER JOIN tbParagraph p
ON (
b.BrochureId = p.BrochureId and
b.LanguageId = p.LanguageId
)
Instead Of: (two joins in Example 2)
SELECT b.Title,
p.ParagraphText
FROM tbBrochure b
INNER JOIN tbBrochureHeadingMap bhm
ON bhm.BrochureId = b.BrochureId
INNER JOIN tbParagraph p
ON p.HeadingId = bhm.HeadingId
Can anyone see any advantages of using the Example 2 over using Example 1 method ?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
<sorengi@.-NOSPAM-yahoo.com> wrote in message
news:OepiUCROEHA.3028@.TK2MSFTNGP11.phx.gbl...
> What are the pros and cons of the following two design methods ?
> (1) Using foreign keys to form a composite primary key of a child
tables -- as in Example.
>
This is one of my favorite designs. It does a couple of things for you
quite nicely.
First off it gives you a single, highly efficient access path to the child
rows, and simultaneously supports your foregn key with a clustered index.
This is especially effective for modeling parent/child relationships where
the child rows will usually be accessed through the parent row. And
especially ineffective elsewhere.
Remember you will need a supporting index on the foregn key column in any
case, and if you make the foreign key the leading columns in the primary
key, you may need a secondary index on the identity column.
.. . .

>Example 1 COMPOSITE FOREIGN KEY Code ***
> CREATE TABLE tbLanguage
> (
> LanguageId int identity(1,1) not null,
> LangName varchar(255) not null,
> PRIMARY KEY CLUSTERED (LanguageId)
> )
> go

> CREATE TABLE tbBrochure
> (
> BrochureId int identity(1,1) not null,
> LanguageId int not null,
> Title varchar(255) not null
> PRIMARY KEY CLUSTERED(BrochureId,LanguageId),
> FOREIGN KEY (LanguageId)
> REFERENCES tbLanguage(LanguageId)
> )
If the foreign key does not lead the Primary Key, you need a secondary index
to support the foreign key.
create index ix_brocure_lang on tbBrocure(LanguageId)
This is incredibly important for queries like
select * from tbBrocure where LanguageId = 123
or
delete tbLanguage where LanguageId = 123
Having LanguageID as the second column in the primary key just doesn't help.
And so Example 1, as written, is pretty useless. You could have left
LanguageID out of the primary key altogher.
Example 1 should be
CREATE TABLE tbBrochure
(
BrochureId int identity(1,1) not null,
LanguageId int not null,
Title varchar(255) not null
PRIMARY KEY CLUSTERED(LanguageId,BrochureId),
FOREIGN KEY (LanguageId)
REFERENCES tbLanguage(LanguageId)
)
Then the foregn key is supported by an index, but you may need a secondary
index on BrocureId to support queries like
select * form tbBrochure where BrochureId = 1234
David
David
|||What you call a "single primary key" is known as a "surrogate key". I think
this article from ASPFAQ covers both sides of the debate fairly well:
http://www.aspfaq.com/show.asp?id=2504
<sorengi@.-NOSPAM-yahoo.com> wrote in message
news:OepiUCROEHA.3028@.TK2MSFTNGP11.phx.gbl...
> What are the pros and cons of the following two design methods ?
> (1) Using foreign keys to form a composite primary key of a child
tables -- as in Example.
> (2) Using a new key to form a single primary key of a table, and placing
parent tables as only foreign keys -- as in Example 2.
>
> Relationships:
> Language to Brochure = one-to-many
> Brochure to Heading = many-to-many
> Heading to Paragraph = one-to-many
> -- *** Example 1 COMPOSITE FOREIGN KEY Code ***
> CREATE TABLE tbLanguage
> (
> LanguageId int identity(1,1) not null,
> LangName varchar(255) not null,
> PRIMARY KEY CLUSTERED (LanguageId)
> )
> go
> CREATE TABLE tbBrochure
> (
> BrochureId int identity(1,1) not null,
> LanguageId int not null,
> Title varchar(255) not null
> PRIMARY KEY CLUSTERED(BrochureId,LanguageId),
> FOREIGN KEY (LanguageId)
> REFERENCES tbLanguage(LanguageId)
> )
> go
> CREATE TABLE tbHeading
> (
> HeadingId int identity(1,1) not null,
> HeadingText varchar(1000) not null,
> PRIMARY KEY CLUSTERED (HeadingId)
> )
> go
> CREATE TABLE tbBrochureHeadingMap
> (
> BrochureId int not null,
> LanguageId int not null,
> HeadingId int not null,
> PRIMARY KEY CLUSTERED (BrochureId,LanguageId,HeadingId),
> FOREIGN KEY (BrochureId,LanguageId)
> REFERENCES tbBrochure (BrochureId,LanguageId),
> FOREIGN KEY (HeadingId)
> REFERENCES tbHeading (HeadingId)
> )
> go
> CREATE TABLE tbParagraph
> (
> BrochureId int not null,
> LanguageId int not null,
> HeadingId int not null,
> SequenceNo int not null,
> ParagraphText varchar(4000) not null,
> PRIMARY KEY CLUSTERED (BrochureId,LanguageId,HeadingId,SequenceNo),
> FOREIGN KEY (BrochureId,LanguageId)
> REFERENCES tbBrochure (BrochureId,LanguageId),
> FOREIGN KEY (HeadingId)
> REFERENCES tbHeading (HeadingId)
> )
> go
>
> -- *** Example 2 SINGLE PRIMARY KEY Code (SQL Server 2000) ***
>
> CREATE TABLE tbLanguage
> (
> LanguageId int identity(1,1) not null,
> LangName varchar(255) not null,
> PRIMARY KEY CLUSTERED (LanguageId)
> )
> go
> CREATE TABLE tbBrochure
> (
> BrochureId int identity(1,1) not null,
> LanguageId int not null,
> Title varchar(255) not null
> PRIMARY KEY CLUSTERED(BrochureId),
> FOREIGN KEY (LanguageId)
> REFERENCES tbLanguage(LanguageId)
> )
> go
> CREATE NONCLUSTERED INDEX ix_tbBrochure_LanguageId ON tbBrochure
(LanguageId)
> go
> CREATE TABLE tbHeading
> (
> HeadingId int identity(1,1) not null,
> HeadingText varchar(1000) not null,
> PRIMARY KEY CLUSTERED (HeadingId)
> )
> go
> CREATE TABLE tbBrochureHeadingMap
> (
> BrochureHeadingMapId int identity(1,1) not null,
> BrochureId int not null,
> HeadingId int not null,
> PRIMARY KEY CLUSTERED (BrochureHeadingMapId),
> FOREIGN KEY (BrochureId)
> REFERENCES tbBrochure (BrochureId),
> FOREIGN KEY (HeadingId)
> REFERENCES tbHeading (HeadingId)
> )
> go
> CREATE NONCLUSTERED INDEX ix_tbBrochureHeadingMap_BrochureId ON
tbBrochureHeadingMap (BrochureId)
> go
> CREATE NONCLUSTERED INDEX ix_tbBrochureHeadingMap_HeadingId ON
tbBrochureHeadingMap (HeadingId)
> go
> CREATE TABLE tbParagraph
> (
> ParagraphId int identity(1,1) not null,
> HeadingId int not null,
> SequenceNo int not null,
> ParagraphText varchar(4000) not null,
> PRIMARY KEY CLUSTERED (ParagraphId),
> FOREIGN KEY (HeadingId)
> REFERENCES tbHeading (HeadingId)
> )
> go
> CREATE NONCLUSTERED INDEX ix_tbParagraph_BrochureId ON
tbBrochureHeadingMap (HeadingId)
> go
>
>
> It has been argued that Example 1: COMPOSITE FOREIGN KEY has the following
pros, over Example 2:
> 1) Fewer indexes are needed. Five (5) Indexes in Example 1 instead of Nine
(9) in Example 2.
> 2) Queries can be created with fewer joins.
> For example: (one join in Example 1)
> SELECT b.Title,
> p.ParagraphText
> FROM tbBrochure b
> INNER JOIN tbParagraph p
> ON (
> b.BrochureId = p.BrochureId and
> b.LanguageId = p.LanguageId
> )
> Instead Of: (two joins in Example 2)
> SELECT b.Title,
> p.ParagraphText
> FROM tbBrochure b
> INNER JOIN tbBrochureHeadingMap bhm
> ON bhm.BrochureId = b.BrochureId
> INNER JOIN tbParagraph p
> ON p.HeadingId = bhm.HeadingId
> Can anyone see any advantages of using the Example 2 over using Example 1
method ?
>
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

Database Design Question

What are the pros and cons of the following two design methods ?

(1) Using foreign keys to form a composite primary key of a child
tables -- as in Example.

POOR MAN'S ERD PROVIDED FOR SUMMARY OVERVIEW (Code provided below)

*** Example 1 COMPOSITE FOREIGN KEY ***
PK = Primary Key
FK = Foreign Key

Relationships:
Language to Brochure = one-to-many
Brochure to Heading = many-to-many
Heading to Paragraph = one-to-many

tbLanguage tbBrochure
------- -------
- LanguageId (PK) - --\ - BrochureId (PK) -
- LangName - \--> - LanguageId (PK)(FK)-
------- / - Title -
/ / -------
/ /
/ /
tbBrochureHeadingMap / / tbHeading
------- <--/ / -------
- BrochureId (PK)(FK)- <--/ -- - HeadingId (PK) -
- LanguageId (PK)(FK)- / - HeadingText -
- HeadingId (PK)(FK)- <---/ -------
------- |
/
tbParagraph /
------- /
- BrochureId (PK)(FK)- <----/
- LanguageId (PK)(FK)-
- HeadingId (PK)(FK)-
- SequenceNo (PK) -
- ParagraphText -
-------

(2) Using a new key to form a single primary key of a table, and
placing parent tables as only foreign keys -- as in Example 2.

*** Example 2 SINGLE PRIMARY KEY ***
Relationships:
Language to Brochure = one-to-many
Brochure to Heading = many-to-many
Heading to Paragraph = one-to-many

tbLanguage tbBrochure
------- -------
- LanguageId (PK) - --\ - BrochureId (PK) -
- LangName - \--> - LanguageId (FK) -
------- - Title -
-------
|
|
tbBrochureHeadingMap | tbHeading
--------- / -------
- BrochureHeadingMapId (PK)- / - HeadingId (PK) -
- BrochureId (FK) - <--/ / - HeadingText -
- HeadingId (FK) - <---/ -------
--------- |
|
tbParagraph /
------- /
- ParagraphId(PK) - /
- HeadingId (FK) - <-----/
- SequenceNo -
- ParagraphText -
-------

It has been argued that Example 1: COMPOSITE FOREIGN KEY has the
following pros, over Example 2:

1) Fewer indexes are needed. Five (5) Indexes in Example 1 instead of
Nine (9) in Example 2.

2) Queries can be created with fewer joins.

For example: (one join in Example 1)

SELECT b.Title,
p.ParagraphText

FROM tbBrochure b
INNER JOIN tbParagraph p
ON (
b.BrochureId = p.BrochureId and
b.LanguageId = p.LanguageId
)

Instead Of: (two joins in Example 2)

SELECT b.Title,
p.ParagraphText

FROM tbBrochure b
INNER JOIN tbBrochureHeadingMap bhm
ON bhm.BrochureId = b.BrochureId
INNER JOIN tbParagraph p
ON p.HeadingId = bhm.HeadingId

Can anyone see any advantages of using the Example 2 over using
Example 1 method ?

-- *** Example 1 COMPOSITE FOREIGN KEY Code (SQL Server 2000) ***

if exists (select * from dbo.sysobjects where id =
object_id(N'[tbParagraph]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [tbParagraph]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[tbBrochureHeadingMap]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [tbBrochureHeadingMap]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[tbBrochure]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tbBrochure]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[tbLanguage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tbLanguage]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[tbHeading]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tbHeading]
GO

CREATE TABLE tbLanguage
(
LanguageId int identity(1,1) not null,
LangNamevarchar(255) not null,
PRIMARY KEY CLUSTERED (LanguageId)
)
go
CREATE TABLE tbBrochure
(
BrochureIdint identity(1,1) not null,
LanguageIdint not null,
Titlevarchar(255) not null
PRIMARY KEY CLUSTERED(BrochureId,LanguageId),
FOREIGN KEY (LanguageId)
REFERENCES tbLanguage(LanguageId)
)
go
CREATE TABLE tbHeading
(
HeadingIdint identity(1,1) not null,
HeadingTextvarchar(1000) not null,
PRIMARY KEY CLUSTERED (HeadingId)
)
go
CREATE TABLE tbBrochureHeadingMap
(
BrochureIdint not null,
LanguageIdint not null,
HeadingIdint not null,
PRIMARY KEY CLUSTERED (BrochureId,LanguageId,HeadingId),
FOREIGN KEY (BrochureId,LanguageId)
REFERENCES tbBrochure (BrochureId,LanguageId),
FOREIGN KEY (HeadingId)
REFERENCES tbHeading (HeadingId)
)
go
CREATE TABLE tbParagraph
(
BrochureIdint not null,
LanguageIdint not null,
HeadingIdint not null,
SequenceNoint not null,
ParagraphTextvarchar(4000) not null,
PRIMARY KEY CLUSTERED (BrochureId,LanguageId,HeadingId,SequenceNo),
FOREIGN KEY (BrochureId,LanguageId)
REFERENCES tbBrochure (BrochureId,LanguageId),
FOREIGN KEY (HeadingId)
REFERENCES tbHeading (HeadingId)
)
go

-- *** Example 2 SINGLE PRIMARY KEY Code (SQL Server 2000) ***

if exists (select * from dbo.sysobjects where id =
object_id(N'[tbParagraph]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [tbParagraph]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[tbBrochureHeadingMap]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [tbBrochureHeadingMap]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[tbBrochure]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tbBrochure]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[tbLanguage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tbLanguage]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[tbHeading]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tbHeading]
GO

CREATE TABLE tbLanguage
(
LanguageId int identity(1,1) not null,
LangNamevarchar(255) not null,
PRIMARY KEY CLUSTERED (LanguageId)
)
go
CREATE TABLE tbBrochure
(
BrochureIdint identity(1,1) not null,
LanguageIdint not null,
Titlevarchar(255) not null
PRIMARY KEY CLUSTERED(BrochureId),
FOREIGN KEY (LanguageId)
REFERENCES tbLanguage(LanguageId)
)
go
CREATE NONCLUSTERED INDEX ix_tbBrochure_LanguageId ON tbBrochure
(LanguageId)
go
CREATE TABLE tbHeading
(
HeadingIdint identity(1,1) not null,
HeadingTextvarchar(1000) not null,
PRIMARY KEY CLUSTERED (HeadingId)
)
go
CREATE TABLE tbBrochureHeadingMap
(
BrochureHeadingMapId int identity(1,1) not null,
BrochureIdint not null,
HeadingIdint not null,
PRIMARY KEY CLUSTERED (BrochureHeadingMapId),
FOREIGN KEY (BrochureId)
REFERENCES tbBrochure (BrochureId),
FOREIGN KEY (HeadingId)
REFERENCES tbHeading (HeadingId)
)
go
CREATE NONCLUSTERED INDEX ix_tbBrochureHeadingMap_BrochureId ON
tbBrochureHeadingMap (BrochureId)
go
CREATE NONCLUSTERED INDEX ix_tbBrochureHeadingMap_HeadingId ON
tbBrochureHeadingMap (HeadingId)
go
CREATE TABLE tbParagraph
(
ParagraphIdint identity(1,1) not null,
HeadingIdint not null,
SequenceNoint not null,
ParagraphTextvarchar(4000) not null,
PRIMARY KEY CLUSTERED (ParagraphId),
FOREIGN KEY (HeadingId)
REFERENCES tbHeading (HeadingId)
)
go
CREATE NONCLUSTERED INDEX ix_tbParagraph_BrochureId ON
tbBrochureHeadingMap (HeadingId)
go[posted and mailed, please reply in news]

Michael D (sorengi@.yahoo.com) writes:
> What are the pros and cons of the following two design methods ?
> (1) Using foreign keys to form a composite primary key of a child
> tables -- as in Example.
>...
> (2) Using a new key to form a single primary key of a table, and
> placing parent tables as only foreign keys -- as in Example 2.

#1 wins, hands down.

If you get very many columns in your key, it may be tempting to
introduce a artificial key.

I had one case in our data base where a table with a four-column key
needed a subtable, with two more columns in the key. I though a six-
column key was a bit too much, so I added an artificial key, and used
that in the subtable. Thus I had:

CREATE TABLE summary (id int NOT NULL,
fk_a int NOT NULL,
fk_b int NOT NULL,
fk_c int NOT NULL,
fk_d int NOT NULL,
value float NOT NULL,
CONSTRAINT pk_sum PRIMARY KEY(id),
CONSTRAINT u_sum UNIQUE(fk_a, fk_b, fk_c, fk_d))
go
CREATE TABLE details (id int NOT NULL,
fk_e int NOT NULL,
flag char(1) NOT NULL,
value float NOT NULL,
CONSTRAINT pk_details PRIMARY KEY (id, fk_e, flag),
CONSTRAINT fk_details FOREIGN KEY (id) REFERENCES parent(id))

Then much later on, I had reason to write queries against these tables,
including updates where selection was on fk_a. It was extremely messy.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

database design question

Hi. I have a new OLTP database requirement, and was wondering what columns
do some of you use in your db design for primary keys, since identity
columns can't really be used for this? I was thinking of using the select
convert(int, convert(varbinary, newid())) to insert. Any drawbacks to this?
Other suggestions would be appreciated. Thanks.
Alex IvascuWhy are you saying that identity cannot be used for primary keys? I would estimate that perhaps 95%
of the SQL Server population who uses surrogate keys uses identity. And indeed, identity will find
its way into the next ANSI standard.
Having said that, you might want to read Joe Celko's posts regarding identity, even though many
points are inaccurate. So, if you do that, you should also read a good response to that (one from
Tony Rogerson, for instance). You can search the archives using the link at the bottom of my URL.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"alex ivascu" <alexdivascu@.sbcglobalNO.SPAMnet> wrote in message
news:IH0lb.2145$e56.174@.newssvr25.news.prodigy.com...
> Hi. I have a new OLTP database requirement, and was wondering what columns
> do some of you use in your db design for primary keys, since identity
> columns can't really be used for this? I was thinking of using the select
> convert(int, convert(varbinary, newid())) to insert. Any drawbacks to this?
> Other suggestions would be appreciated. Thanks.
> Alex Ivascu
>
>|||Hi, Tibor
Look, I am not lawyer of Celko and I agree with you that some his posts are
inaccurate.
Just yesterday I faced the problem where I was need to update the column
that is PK with Identity property.
At beginining our business logic did not allow to do such things but as you
know, came a new requirement of production in our company.
I think we falled in these 5 % that you estimated that should be used
identity property for promary key .
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:u$ADar5lDHA.2528@.TK2MSFTNGP12.phx.gbl...
> Why are you saying that identity cannot be used for primary keys? I would
estimate that perhaps 95%
> of the SQL Server population who uses surrogate keys uses identity. And
indeed, identity will find
> its way into the next ANSI standard.
> Having said that, you might want to read Joe Celko's posts regarding
identity, even though many
> points are inaccurate. So, if you do that, you should also read a good
response to that (one from
> Tony Rogerson, for instance). You can search the archives using the link
at the bottom of my URL.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "alex ivascu" <alexdivascu@.sbcglobalNO.SPAMnet> wrote in message
> news:IH0lb.2145$e56.174@.newssvr25.news.prodigy.com...
> > Hi. I have a new OLTP database requirement, and was wondering what
columns
> > do some of you use in your db design for primary keys, since identity
> > columns can't really be used for this? I was thinking of using the
select
> > convert(int, convert(varbinary, newid())) to insert. Any drawbacks to
this?
> > Other suggestions would be appreciated. Thanks.
> >
> > Alex Ivascu
> >
> >
> >
>|||>> was wondering what columns do some of you use in your db design for
primary keys, <<
There is no "magic, one-size-fits-all, universal" answer. Designing a
database is hard work! So, for each and every individual table:
1) Is there an ISO standard?
2) Is there a national standard?
3) Is there an industry standard?
Examples: GTIN for retail, VIN for vehicles, ISBN for books and
publications.
Other questions to ask, if you have to invent a code:
1) Will a human being input it? So, does it need an easy syntax
(airport codes)? Does it need a check digit(ISBN)? What kind of check
digit algorithm?
2) How do I verifiy it in the reality represented in my data model?
Internally or with a trusted external source?
External: government tax numbers. Internal: ISO tire sizes (155R15 =155 cm width, Radial construction, 15 inch diameter; verify with a ruler
and your eyes).
I have four chapers on designing codes in DATA & DATABASES which you
might want to read.
--CELKO--
=========================== Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

database design question

Hi. I have a new OLTP database requirement, and was wondering what columns
do some of you use in your db design for primary keys, since identity
columns can't really be used for this? I was thinking of using the select
convert(int, convert(varbinary, newid())) to insert. Any drawbacks to this?
Other suggestions would be appreciated. Thanks.
Alex IvascuIn article <8I0lb.2146$f86.1093@.newssvr25.news.prodigy.com>,
alexdivascu@.sbcglobalNO.SPAMnet said...
> Hi. I have a new OLTP database requirement, and was wondering what columns
> do some of you use in your db design for primary keys, since identity
> columns can't really be used for this? I was thinking of using the select
> convert(int, convert(varbinary, newid())) to insert. Any drawbacks to this?
> Other suggestions would be appreciated. Thanks.
Why not just use a GUID?|||I was thinking about storing it as an int... But, I guess a varchar would
do just fine.
"Brad" <me@.privacy.net> wrote in message
news:MPG.19fe630a4934c2ea98b876@.news...
> In article <8I0lb.2146$f86.1093@.newssvr25.news.prodigy.com>,
> alexdivascu@.sbcglobalNO.SPAMnet said...
> > Hi. I have a new OLTP database requirement, and was wondering what
columns
> > do some of you use in your db design for primary keys, since identity
> > columns can't really be used for this? I was thinking of using the
select
> > convert(int, convert(varbinary, newid())) to insert. Any drawbacks to
this?
> > Other suggestions would be appreciated. Thanks.
> Why not just use a GUID?|||In article <G11lb.2155$%a6.1679@.newssvr25.news.prodigy.com>,
alexdivascu@.sbcglobalNO.SPAMnet said...
> "Brad" <me@.privacy.net> wrote in message
> news:MPG.19fe630a4934c2ea98b876@.news...
> > In article <8I0lb.2146$f86.1093@.newssvr25.news.prodigy.com>,
> > alexdivascu@.sbcglobalNO.SPAMnet said...
> > > Hi. I have a new OLTP database requirement, and was wondering what
> columns
> > > do some of you use in your db design for primary keys, since identity
> > > columns can't really be used for this? I was thinking of using the
> select
> > > convert(int, convert(varbinary, newid())) to insert. Any drawbacks to
> this?
> > > Other suggestions would be appreciated. Thanks.
> >
> > Why not just use a GUID?
> I was thinking about storing it as an int... But, I guess a varchar would
> do just fine.
Don't store it as a VarChar, store it as a UniqueIdentifier. It will save
space and index more efficiently.

Database Design Question

What are the pros and cons of the following two design methods ?
(1) Using foreign keys to form a composite primary key of a child tables -- as in Example.
(2) Using a new key to form a single primary key of a table, and placing parent tables as only foreign keys -- as in Example 2.
Relationships:
Language to Brochure = one-to-many
Brochure to Heading = many-to-many
Heading to Paragraph = one-to-many
-- *** Example 1 COMPOSITE FOREIGN KEY Code ***
CREATE TABLE tbLanguage
(
LanguageId int identity(1,1) not null,
LangName varchar(255) not null,
PRIMARY KEY CLUSTERED (LanguageId)
)
go
CREATE TABLE tbBrochure
(
BrochureId int identity(1,1) not null,
LanguageId int not null,
Title varchar(255) not null
PRIMARY KEY CLUSTERED(BrochureId,LanguageId),
FOREIGN KEY (LanguageId)
REFERENCES tbLanguage(LanguageId)
)
go
CREATE TABLE tbHeading
(
HeadingId int identity(1,1) not null,
HeadingText varchar(1000) not null,
PRIMARY KEY CLUSTERED (HeadingId)
)
go
CREATE TABLE tbBrochureHeadingMap
(
BrochureId int not null,
LanguageId int not null,
HeadingId int not null,
PRIMARY KEY CLUSTERED (BrochureId,LanguageId,HeadingId),
FOREIGN KEY (BrochureId,LanguageId)
REFERENCES tbBrochure (BrochureId,LanguageId),
FOREIGN KEY (HeadingId)
REFERENCES tbHeading (HeadingId)
)
go
CREATE TABLE tbParagraph
(
BrochureId int not null,
LanguageId int not null,
HeadingId int not null,
SequenceNo int not null,
ParagraphText varchar(4000) not null,
PRIMARY KEY CLUSTERED (BrochureId,LanguageId,HeadingId,SequenceNo),
FOREIGN KEY (BrochureId,LanguageId)
REFERENCES tbBrochure (BrochureId,LanguageId),
FOREIGN KEY (HeadingId)
REFERENCES tbHeading (HeadingId)
)
go
-- *** Example 2 SINGLE PRIMARY KEY Code (SQL Server 2000) ***
CREATE TABLE tbLanguage
(
LanguageId int identity(1,1) not null,
LangName varchar(255) not null,
PRIMARY KEY CLUSTERED (LanguageId)
)
go
CREATE TABLE tbBrochure
(
BrochureId int identity(1,1) not null,
LanguageId int not null,
Title varchar(255) not null
PRIMARY KEY CLUSTERED(BrochureId),
FOREIGN KEY (LanguageId)
REFERENCES tbLanguage(LanguageId)
)
go
CREATE NONCLUSTERED INDEX ix_tbBrochure_LanguageId ON tbBrochure (LanguageId)
go
CREATE TABLE tbHeading
(
HeadingId int identity(1,1) not null,
HeadingText varchar(1000) not null,
PRIMARY KEY CLUSTERED (HeadingId)
)
go
CREATE TABLE tbBrochureHeadingMap
(
BrochureHeadingMapId int identity(1,1) not null,
BrochureId int not null,
HeadingId int not null,
PRIMARY KEY CLUSTERED (BrochureHeadingMapId),
FOREIGN KEY (BrochureId)
REFERENCES tbBrochure (BrochureId),
FOREIGN KEY (HeadingId)
REFERENCES tbHeading (HeadingId)
)
go
CREATE NONCLUSTERED INDEX ix_tbBrochureHeadingMap_BrochureId ON tbBrochureHeadingMap (BrochureId)
go
CREATE NONCLUSTERED INDEX ix_tbBrochureHeadingMap_HeadingId ON tbBrochureHeadingMap (HeadingId)
go
CREATE TABLE tbParagraph
(
ParagraphId int identity(1,1) not null,
HeadingId int not null,
SequenceNo int not null,
ParagraphText varchar(4000) not null,
PRIMARY KEY CLUSTERED (ParagraphId),
FOREIGN KEY (HeadingId)
REFERENCES tbHeading (HeadingId)
)
go
CREATE NONCLUSTERED INDEX ix_tbParagraph_BrochureId ON tbBrochureHeadingMap (HeadingId)
go
It has been argued that Example 1: COMPOSITE FOREIGN KEY has the following pros, over Example 2:
1) Fewer indexes are needed. Five (5) Indexes in Example 1 instead of Nine (9) in Example 2.
2) Queries can be created with fewer joins.
For example: (one join in Example 1)
SELECT b.Title,
p.ParagraphText
FROM tbBrochure b
INNER JOIN tbParagraph p
ON (
b.BrochureId = p.BrochureId and
b.LanguageId = p.LanguageId
)
Instead Of: (two joins in Example 2)
SELECT b.Title,
p.ParagraphText
FROM tbBrochure b
INNER JOIN tbBrochureHeadingMap bhm
ON bhm.BrochureId = b.BrochureId
INNER JOIN tbParagraph p
ON p.HeadingId = bhm.HeadingId
Can anyone see any advantages of using the Example 2 over using Example 1 method ?
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.<sorengi@.-NOSPAM-yahoo.com> wrote in message
news:OepiUCROEHA.3028@.TK2MSFTNGP11.phx.gbl...
> What are the pros and cons of the following two design methods ?
> (1) Using foreign keys to form a composite primary key of a child
tables -- as in Example.
>
This is one of my favorite designs. It does a couple of things for you
quite nicely.
First off it gives you a single, highly efficient access path to the child
rows, and simultaneously supports your foregn key with a clustered index.
This is especially effective for modeling parent/child relationships where
the child rows will usually be accessed through the parent row. And
especially ineffective elsewhere.
Remember you will need a supporting index on the foregn key column in any
case, and if you make the foreign key the leading columns in the primary
key, you may need a secondary index on the identity column.
. . .
>Example 1 COMPOSITE FOREIGN KEY Code ***
> CREATE TABLE tbLanguage
> (
> LanguageId int identity(1,1) not null,
> LangName varchar(255) not null,
> PRIMARY KEY CLUSTERED (LanguageId)
> )
> go
> CREATE TABLE tbBrochure
> (
> BrochureId int identity(1,1) not null,
> LanguageId int not null,
> Title varchar(255) not null
> PRIMARY KEY CLUSTERED(BrochureId,LanguageId),
> FOREIGN KEY (LanguageId)
> REFERENCES tbLanguage(LanguageId)
> )
If the foreign key does not lead the Primary Key, you need a secondary index
to support the foreign key.
create index ix_brocure_lang on tbBrocure(LanguageId)
This is incredibly important for queries like
select * from tbBrocure where LanguageId = 123
or
delete tbLanguage where LanguageId = 123
Having LanguageID as the second column in the primary key just doesn't help.
And so Example 1, as written, is pretty useless. You could have left
LanguageID out of the primary key altogher.
Example 1 should be
CREATE TABLE tbBrochure
(
BrochureId int identity(1,1) not null,
LanguageId int not null,
Title varchar(255) not null
PRIMARY KEY CLUSTERED(LanguageId,BrochureId),
FOREIGN KEY (LanguageId)
REFERENCES tbLanguage(LanguageId)
)
Then the foregn key is supported by an index, but you may need a secondary
index on BrocureId to support queries like
select * form tbBrochure where BrochureId = 1234
David
David|||What you call a "single primary key" is known as a "surrogate key". I think
this article from ASPFAQ covers both sides of the debate fairly well:
http://www.aspfaq.com/show.asp?id=2504
<sorengi@.-NOSPAM-yahoo.com> wrote in message
news:OepiUCROEHA.3028@.TK2MSFTNGP11.phx.gbl...
> What are the pros and cons of the following two design methods ?
> (1) Using foreign keys to form a composite primary key of a child
tables -- as in Example.
> (2) Using a new key to form a single primary key of a table, and placing
parent tables as only foreign keys -- as in Example 2.
>
> Relationships:
> Language to Brochure = one-to-many
> Brochure to Heading = many-to-many
> Heading to Paragraph = one-to-many
> -- *** Example 1 COMPOSITE FOREIGN KEY Code ***
> CREATE TABLE tbLanguage
> (
> LanguageId int identity(1,1) not null,
> LangName varchar(255) not null,
> PRIMARY KEY CLUSTERED (LanguageId)
> )
> go
> CREATE TABLE tbBrochure
> (
> BrochureId int identity(1,1) not null,
> LanguageId int not null,
> Title varchar(255) not null
> PRIMARY KEY CLUSTERED(BrochureId,LanguageId),
> FOREIGN KEY (LanguageId)
> REFERENCES tbLanguage(LanguageId)
> )
> go
> CREATE TABLE tbHeading
> (
> HeadingId int identity(1,1) not null,
> HeadingText varchar(1000) not null,
> PRIMARY KEY CLUSTERED (HeadingId)
> )
> go
> CREATE TABLE tbBrochureHeadingMap
> (
> BrochureId int not null,
> LanguageId int not null,
> HeadingId int not null,
> PRIMARY KEY CLUSTERED (BrochureId,LanguageId,HeadingId),
> FOREIGN KEY (BrochureId,LanguageId)
> REFERENCES tbBrochure (BrochureId,LanguageId),
> FOREIGN KEY (HeadingId)
> REFERENCES tbHeading (HeadingId)
> )
> go
> CREATE TABLE tbParagraph
> (
> BrochureId int not null,
> LanguageId int not null,
> HeadingId int not null,
> SequenceNo int not null,
> ParagraphText varchar(4000) not null,
> PRIMARY KEY CLUSTERED (BrochureId,LanguageId,HeadingId,SequenceNo),
> FOREIGN KEY (BrochureId,LanguageId)
> REFERENCES tbBrochure (BrochureId,LanguageId),
> FOREIGN KEY (HeadingId)
> REFERENCES tbHeading (HeadingId)
> )
> go
>
> -- *** Example 2 SINGLE PRIMARY KEY Code (SQL Server 2000) ***
>
> CREATE TABLE tbLanguage
> (
> LanguageId int identity(1,1) not null,
> LangName varchar(255) not null,
> PRIMARY KEY CLUSTERED (LanguageId)
> )
> go
> CREATE TABLE tbBrochure
> (
> BrochureId int identity(1,1) not null,
> LanguageId int not null,
> Title varchar(255) not null
> PRIMARY KEY CLUSTERED(BrochureId),
> FOREIGN KEY (LanguageId)
> REFERENCES tbLanguage(LanguageId)
> )
> go
> CREATE NONCLUSTERED INDEX ix_tbBrochure_LanguageId ON tbBrochure
(LanguageId)
> go
> CREATE TABLE tbHeading
> (
> HeadingId int identity(1,1) not null,
> HeadingText varchar(1000) not null,
> PRIMARY KEY CLUSTERED (HeadingId)
> )
> go
> CREATE TABLE tbBrochureHeadingMap
> (
> BrochureHeadingMapId int identity(1,1) not null,
> BrochureId int not null,
> HeadingId int not null,
> PRIMARY KEY CLUSTERED (BrochureHeadingMapId),
> FOREIGN KEY (BrochureId)
> REFERENCES tbBrochure (BrochureId),
> FOREIGN KEY (HeadingId)
> REFERENCES tbHeading (HeadingId)
> )
> go
> CREATE NONCLUSTERED INDEX ix_tbBrochureHeadingMap_BrochureId ON
tbBrochureHeadingMap (BrochureId)
> go
> CREATE NONCLUSTERED INDEX ix_tbBrochureHeadingMap_HeadingId ON
tbBrochureHeadingMap (HeadingId)
> go
> CREATE TABLE tbParagraph
> (
> ParagraphId int identity(1,1) not null,
> HeadingId int not null,
> SequenceNo int not null,
> ParagraphText varchar(4000) not null,
> PRIMARY KEY CLUSTERED (ParagraphId),
> FOREIGN KEY (HeadingId)
> REFERENCES tbHeading (HeadingId)
> )
> go
> CREATE NONCLUSTERED INDEX ix_tbParagraph_BrochureId ON
tbBrochureHeadingMap (HeadingId)
> go
>
>
> It has been argued that Example 1: COMPOSITE FOREIGN KEY has the following
pros, over Example 2:
> 1) Fewer indexes are needed. Five (5) Indexes in Example 1 instead of Nine
(9) in Example 2.
> 2) Queries can be created with fewer joins.
> For example: (one join in Example 1)
> SELECT b.Title,
> p.ParagraphText
> FROM tbBrochure b
> INNER JOIN tbParagraph p
> ON (
> b.BrochureId = p.BrochureId and
> b.LanguageId = p.LanguageId
> )
> Instead Of: (two joins in Example 2)
> SELECT b.Title,
> p.ParagraphText
> FROM tbBrochure b
> INNER JOIN tbBrochureHeadingMap bhm
> ON bhm.BrochureId = b.BrochureId
> INNER JOIN tbParagraph p
> ON p.HeadingId = bhm.HeadingId
> Can anyone see any advantages of using the Example 2 over using Example 1
method ?
>
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

database design - keys

You may also want to consider this:
Do you have any manufacturers that manufacture more than
one piece of equipment found in the Equipment table?
This is usually the case. You may want to use some
unique identifier per Manufacturer, and add the
maufacturer's ID field to the Equipment table.
This could get even trickier since you may have mulitple
manufacturer's supplying multiple types of equipment, in
which case you may want to create a third table which
would have fields for the Manufacturer ID and the
Equipment ID to implement the many to many relationship.
Just a couple of thoughts.
Matthew Bando
matthew.bando@.CSCTGI(remove this).com

quote:

---

>--Original Message--
>Hello All
>I am designing a database, or rather redesigning a very

---

old database and

quote:

---

>have a question regarding setting up key fields. The

---

old database has a

quote:

---

>table called Equipment with two fields:
>Equipment Code - text 8
>Equipment Description - text 50
>In the new design I will have a table called

---

Manufacturers that will have an

quote:

---

>Equipment Code field, which will link to the Equipment

---

table to get the

quote:

---

>description. My question is, should I make a new

---

Integer key field called

quote:

---

>say EquipID which is what would get stored in the

---

Manufacturers table or

quote:

---

>should I simply use the Equipment Code field as the

---

key? What are the

quote:

---

>advantages/disadvantages to each method?
>I have several other tables with a similar situation

---

where the 'Code' is

quote:

---

>unique but there are more fields in these tables.
>Thanks,
>Gerry
>
>.
>

---

Thanks Matthew.
At this point, there will only be one equipment code for one manufacturer.
Although I am having meetings next week which will confirm this. The old
database had one-to-one but anytime you update a program or database then
that is the time to think about such things. Perhaps they don't know the
possibilities after having used a paradox database for 12 years!
Gerry
"Matthew Bando" <anonymous@.discussions.microsoft.com> wrote in message
news:740c01c3e738$180f8140$a401280a@.phx.gbl...[QUOTE]
> You may also want to consider this:
> Do you have any manufacturers that manufacture more than
> one piece of equipment found in the Equipment table?
> This is usually the case. You may want to use some
> unique identifier per Manufacturer, and add the
> maufacturer's ID field to the Equipment table.
> This could get even trickier since you may have mulitple
> manufacturer's supplying multiple types of equipment, in
> which case you may want to create a third table which
> would have fields for the Manufacturer ID and the
> Equipment ID to implement the many to many relationship.
> Just a couple of thoughts.
> Matthew Bando
> matthew.bando@.CSCTGI(remove this).com
>
> old database and
> old database has a
> Manufacturers that will have an
> table to get the
> Integer key field called
> Manufacturers table or
> key? What are the
> where the 'Code' is

database design - keys

Hello All
I am designing a database, or rather redesigning a very old database and
have a question regarding setting up key fields. The old database has a
table called Equipment with two fields:
Equipment Code - text 8
Equipment Description - text 50
In the new design I will have a table called Manufacturers that will have an
Equipment Code field, which will link to the Equipment table to get the
description. My question is, should I make a new Integer key field called
say EquipID which is what would get stored in the Manufacturers table or
should I simply use the Equipment Code field as the key? What are the
advantages/disadvantages to each method?
I have several other tables with a similar situation where the 'Code' is
unique but there are more fields in these tables.
Thanks,
GerryThis is a subject that has spawned many heated debates. Here's my take on
it:
In your case, just use the codes that exist.
In general, if your data has a natural key, use it. If the natural key is a
composite key of sufficient length/complexity (a totally subjective
determination) there might be a good reason to use a surrogate. However,
you MUST also enforce the uniqueness of the natural key! I am not against
the use of surrogate keys at all, but they should be used only after much
careful thought and consideration. Surrogate keys tend, in the hands of the
inexperienced, to lend a false sense of security ("Of course I don't have
any duplicates, my surrogate key assures that!")
Since you have a simple natural key, there is really no reason not to use
it. Adding a surrogate key just creates more data...
"News" <gerrydyck@.shaw.ca> wrote in message
news:Q7cSb.328534$X%5.134270@.pd7tw2no...

quote:

---

> Hello All
> I am designing a database, or rather redesigning a very old database and
> have a question regarding setting up key fields. The old database has a
> table called Equipment with two fields:
> Equipment Code - text 8
> Equipment Description - text 50
> In the new design I will have a table called Manufacturers that will have

---

an

quote:

---

> Equipment Code field, which will link to the Equipment table to get the
> description. My question is, should I make a new Integer key field called
> say EquipID which is what would get stored in the Manufacturers table or
> should I simply use the Equipment Code field as the key? What are the
> advantages/disadvantages to each method?
> I have several other tables with a similar situation where the 'Code' is
> unique but there are more fields in these tables.
> Thanks,
> Gerry
>

---

|||There are no general rules or norms which recommend a specific datatype for
a key.
The considerations to select a good key are often misunderstood. They
include stability (column values rarely change), simplicity (so that
relational operations can be effective), familiarity (meaningful or commonly
understood by the user) and irreducibility (no proper subset of key column
be another key). A good design can tradeoff certain characteristics in favor
of others to tackle specific issues with regard to key selection.
In a precisely modeled system, a key is chosen only based on the rules
defined at the business model & key selection involves only logical
considerations.
However, the implementation of databases using popular SQL DBMSs, generally
favors the usage of narrow keys for query efficiency, due to their smaller
size at the physical level. This may often fall under the criteria of
simplicity (mentioned above), but shuffling keys just for performance sake
is not always a good idea.
Anith|||Thanks Don. This is my first SQL database and sometimes with new programs I
tend to overthink a solution. For this one, I will be sticking with a
natural key.
Gerry
"Don Peterson" <no1@.nunya.com> wrote in message
news:uVsFYAq5DHA.2556@.TK2MSFTNGP09.phx.gbl...

quote:

---

> This is a subject that has spawned many heated debates. Here's my take on
> it:
> In your case, just use the codes that exist.
> In general, if your data has a natural key, use it. If the natural key is

---

a

quote:

---

> composite key of sufficient length/complexity (a totally subjective
> determination) there might be a good reason to use a surrogate. However,
> you MUST also enforce the uniqueness of the natural key! I am not against
> the use of surrogate keys at all, but they should be used only after much
> careful thought and consideration. Surrogate keys tend, in the hands of

---

the

quote:

---

> inexperienced, to lend a false sense of security ("Of course I don't have
> any duplicates, my surrogate key assures that!")
> Since you have a simple natural key, there is really no reason not to use
> it. Adding a surrogate key just creates more data...
> "News" <gerrydyck@.shaw.ca> wrote in message
> news:Q7cSb.328534$X%5.134270@.pd7tw2no...
have[QUOTE]
> an
called[QUOTE]
>

---

|||Thanks for your input Anith.
Gerry
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ex9OK3q5DHA.1596@.TK2MSFTNGP10.phx.gbl...

quote:

---

> There are no general rules or norms which recommend a specific datatype

---

for

quote:

---

> a key.
> The considerations to select a good key are often misunderstood. They
> include stability (column values rarely change), simplicity (so that
> relational operations can be effective), familiarity (meaningful or

---

commonly

quote:

---

> understood by the user) and irreducibility (no proper subset of key column
> be another key). A good design can tradeoff certain characteristics in

---

favor

quote:

---

> of others to tackle specific issues with regard to key selection.
> In a precisely modeled system, a key is chosen only based on the rules
> defined at the business model & key selection involves only logical
> considerations.
> However, the implementation of databases using popular SQL DBMSs,

---

generally

quote:

---

> favors the usage of narrow keys for query efficiency, due to their smaller
> size at the physical level. This may often fall under the criteria of
> simplicity (mentioned above), but shuffling keys just for performance sake
> is not always a good idea.
> --
> Anith
>

---

database design - keys

Hello All
I am designing a database, or rather redesigning a very old database and
have a question regarding setting up key fields. The old database has a
table called Equipment with two fields:
Equipment Code - text 8
Equipment Description - text 50
In the new design I will have a table called Manufacturers that will have an
Equipment Code field, which will link to the Equipment table to get the
description. My question is, should I make a new Integer key field called
say EquipID which is what would get stored in the Manufacturers table or
should I simply use the Equipment Code field as the key? What are the
advantages/disadvantages to each method?
I have several other tables with a similar situation where the 'Code' is
unique but there are more fields in these tables.
Thanks,
GerryThis is a subject that has spawned many heated debates. Here's my take on
it:
In your case, just use the codes that exist.
In general, if your data has a natural key, use it. If the natural key is a
composite key of sufficient length/complexity (a totally subjective
determination) there might be a good reason to use a surrogate. However,
you MUST also enforce the uniqueness of the natural key! I am not against
the use of surrogate keys at all, but they should be used only after much
careful thought and consideration. Surrogate keys tend, in the hands of the
inexperienced, to lend a false sense of security ("Of course I don't have
any duplicates, my surrogate key assures that!")
Since you have a simple natural key, there is really no reason not to use
it. Adding a surrogate key just creates more data...
"News" <gerrydyck@.shaw.ca> wrote in message
news:Q7cSb.328534$X%5.134270@.pd7tw2no...
> Hello All
> I am designing a database, or rather redesigning a very old database and
> have a question regarding setting up key fields. The old database has a
> table called Equipment with two fields:
> Equipment Code - text 8
> Equipment Description - text 50
> In the new design I will have a table called Manufacturers that will have
an
> Equipment Code field, which will link to the Equipment table to get the
> description. My question is, should I make a new Integer key field called
> say EquipID which is what would get stored in the Manufacturers table or
> should I simply use the Equipment Code field as the key? What are the
> advantages/disadvantages to each method?
> I have several other tables with a similar situation where the 'Code' is
> unique but there are more fields in these tables.
> Thanks,
> Gerry
>|||There are no general rules or norms which recommend a specific datatype for
a key.
The considerations to select a good key are often misunderstood. They
include stability (column values rarely change), simplicity (so that
relational operations can be effective), familiarity (meaningful or commonly
understood by the user) and irreducibility (no proper subset of key column
be another key). A good design can tradeoff certain characteristics in favor
of others to tackle specific issues with regard to key selection.
In a precisely modeled system, a key is chosen only based on the rules
defined at the business model & key selection involves only logical
considerations.
However, the implementation of databases using popular SQL DBMSs, generally
favors the usage of narrow keys for query efficiency, due to their smaller
size at the physical level. This may often fall under the criteria of
simplicity (mentioned above), but shuffling keys just for performance sake
is not always a good idea.
--
Anith|||You may also want to consider this:
Do you have any manufacturers that manufacture more than
one piece of equipment found in the Equipment table?
This is usually the case. You may want to use some
unique identifier per Manufacturer, and add the
maufacturer's ID field to the Equipment table.
This could get even trickier since you may have mulitple
manufacturer's supplying multiple types of equipment, in
which case you may want to create a third table which
would have fields for the Manufacturer ID and the
Equipment ID to implement the many to many relationship.
Just a couple of thoughts.
Matthew Bando
matthew.bando@.CSCTGI(remove this).com
>--Original Message--
>Hello All
>I am designing a database, or rather redesigning a very
old database and
>have a question regarding setting up key fields. The
old database has a
>table called Equipment with two fields:
>Equipment Code - text 8
>Equipment Description - text 50
>In the new design I will have a table called
Manufacturers that will have an
>Equipment Code field, which will link to the Equipment
table to get the
>description. My question is, should I make a new
Integer key field called
>say EquipID which is what would get stored in the
Manufacturers table or
>should I simply use the Equipment Code field as the
key? What are the
>advantages/disadvantages to each method?
>I have several other tables with a similar situation
where the 'Code' is
>unique but there are more fields in these tables.
>Thanks,
>Gerry
>
>.
>|||You may also want to consider this:
Do you have any manufacturers that manufacture more than
one piece of equipment found in the Equipment table?
This is usually the case. You may want to use some
unique identifier per Manufacturer, and add the
maufacturer's ID field to the Equipment table.
This could get even trickier since you may have mulitple
manufacturer's supplying multiple types of equipment, in
which case you may want to create a third table which
would have fields for the Manufacturer ID and the
Equipment ID to implement the many to many relationship.
Just a couple of thoughts.
Matthew Bando
matthew.bando@.CSCTGI(remove this).com
>--Original Message--
>Hello All
>I am designing a database, or rather redesigning a very
old database and
>have a question regarding setting up key fields. The
old database has a
>table called Equipment with two fields:
>Equipment Code - text 8
>Equipment Description - text 50
>In the new design I will have a table called
Manufacturers that will have an
>Equipment Code field, which will link to the Equipment
table to get the
>description. My question is, should I make a new
Integer key field called
>say EquipID which is what would get stored in the
Manufacturers table or
>should I simply use the Equipment Code field as the
key? What are the
>advantages/disadvantages to each method?
>I have several other tables with a similar situation
where the 'Code' is
>unique but there are more fields in these tables.
>Thanks,
>Gerry
>
>.
>|||Thanks Don. This is my first SQL database and sometimes with new programs I
tend to overthink a solution. For this one, I will be sticking with a
natural key.
Gerry
"Don Peterson" <no1@.nunya.com> wrote in message
news:uVsFYAq5DHA.2556@.TK2MSFTNGP09.phx.gbl...
> This is a subject that has spawned many heated debates. Here's my take on
> it:
> In your case, just use the codes that exist.
> In general, if your data has a natural key, use it. If the natural key is
a
> composite key of sufficient length/complexity (a totally subjective
> determination) there might be a good reason to use a surrogate. However,
> you MUST also enforce the uniqueness of the natural key! I am not against
> the use of surrogate keys at all, but they should be used only after much
> careful thought and consideration. Surrogate keys tend, in the hands of
the
> inexperienced, to lend a false sense of security ("Of course I don't have
> any duplicates, my surrogate key assures that!")
> Since you have a simple natural key, there is really no reason not to use
> it. Adding a surrogate key just creates more data...
> "News" <gerrydyck@.shaw.ca> wrote in message
> news:Q7cSb.328534$X%5.134270@.pd7tw2no...
> > Hello All
> >
> > I am designing a database, or rather redesigning a very old database and
> > have a question regarding setting up key fields. The old database has a
> > table called Equipment with two fields:
> >
> > Equipment Code - text 8
> > Equipment Description - text 50
> >
> > In the new design I will have a table called Manufacturers that will
have
> an
> > Equipment Code field, which will link to the Equipment table to get the
> > description. My question is, should I make a new Integer key field
called
> > say EquipID which is what would get stored in the Manufacturers table or
> > should I simply use the Equipment Code field as the key? What are the
> > advantages/disadvantages to each method?
> >
> > I have several other tables with a similar situation where the 'Code' is
> > unique but there are more fields in these tables.
> >
> > Thanks,
> > Gerry
> >
> >
>|||Thanks for your input Anith.
Gerry
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ex9OK3q5DHA.1596@.TK2MSFTNGP10.phx.gbl...
> There are no general rules or norms which recommend a specific datatype
for
> a key.
> The considerations to select a good key are often misunderstood. They
> include stability (column values rarely change), simplicity (so that
> relational operations can be effective), familiarity (meaningful or
commonly
> understood by the user) and irreducibility (no proper subset of key column
> be another key). A good design can tradeoff certain characteristics in
favor
> of others to tackle specific issues with regard to key selection.
> In a precisely modeled system, a key is chosen only based on the rules
> defined at the business model & key selection involves only logical
> considerations.
> However, the implementation of databases using popular SQL DBMSs,
generally
> favors the usage of narrow keys for query efficiency, due to their smaller
> size at the physical level. This may often fall under the criteria of
> simplicity (mentioned above), but shuffling keys just for performance sake
> is not always a good idea.
> --
> Anith
>|||Thanks Matthew.
At this point, there will only be one equipment code for one manufacturer.
Although I am having meetings next week which will confirm this. The old
database had one-to-one but anytime you update a program or database then
that is the time to think about such things. Perhaps they don't know the
possibilities after having used a paradox database for 12 years!
Gerry
"Matthew Bando" <anonymous@.discussions.microsoft.com> wrote in message
news:740c01c3e738$180f8140$a401280a@.phx.gbl...
> You may also want to consider this:
> Do you have any manufacturers that manufacture more than
> one piece of equipment found in the Equipment table?
> This is usually the case. You may want to use some
> unique identifier per Manufacturer, and add the
> maufacturer's ID field to the Equipment table.
> This could get even trickier since you may have mulitple
> manufacturer's supplying multiple types of equipment, in
> which case you may want to create a third table which
> would have fields for the Manufacturer ID and the
> Equipment ID to implement the many to many relationship.
> Just a couple of thoughts.
> Matthew Bando
> matthew.bando@.CSCTGI(remove this).com
>
> >--Original Message--
> >Hello All
> >
> >I am designing a database, or rather redesigning a very
> old database and
> >have a question regarding setting up key fields. The
> old database has a
> >table called Equipment with two fields:
> >
> >Equipment Code - text 8
> >Equipment Description - text 50
> >
> >In the new design I will have a table called
> Manufacturers that will have an
> >Equipment Code field, which will link to the Equipment
> table to get the
> >description. My question is, should I make a new
> Integer key field called
> >say EquipID which is what would get stored in the
> Manufacturers table or
> >should I simply use the Equipment Code field as the
> key? What are the
> >advantages/disadvantages to each method?
> >
> >I have several other tables with a similar situation
> where the 'Code' is
> >unique but there are more fields in these tables.
> >
> >Thanks,
> >Gerry
> >
> >
> >.
> >

Database design

Hi All,

I wanted the expert opinion out there in the use of foreign keys as
primary keys in a table. I am not very good at explaining this
concept, but I am going to try -

Let us say you have a parent/master table( Ex: purchase order) that
is generating number (primary key for the main table)using the seed
and increment specified. We need all the records of this table to be
in sequential order - i.e. we need all purchase orders to be in
sequence. Now there are two different types purchase orders different
enough to have entity/tables of their own. So what are the downsides
of using the primary key generated in the main table which would
normally be a foreign key to the child table, as the actual primary
key in the child tables.

Thanks

KRIf I am understanding correctly, you want to be able to take the PK
from the master table, and use it as the PK in the detail table as
well? It sounds like from your description that you have two purchase
order tables, correct? So again, if my assumption is correct, you
want to be able to use the PK from either master as the PK in the
detail table right?

If so, I think you'll have a problem with the detail table in that you
can have the same number come up for both master tables, thereby
inserting a row into the detail table that is a duplicate.

Can you concat something onto the insert into the detail? ie:
MasterTbl1 and MasterTbl2 send entries to Detail. MasterTbl1 sends a
3, and since it's from that table, it tacks a M1 onto the beginning or
M2 if it's the other table - M13 or M23

That would set up your detail table like this:
PK
M11
M12
M21
M22
M13
M23

but that doesn't solve your keeping them in order, so maybe you could
tack it on the end?
1M1
1M2
2M1
3M1
2M2
3M2

hth
M@.

On May 25, 12:18 pm, KR <kra...@.bastyr.eduwrote:

Quote:

Originally Posted by

Hi All,
>
I wanted the expert opinion out there in the use of foreign keys as
primary keys in a table. I am not very good at explaining this
concept, but I am going to try -
>
Let us say you have a parent/master table( Ex: purchase order) that
is generating number (primary key for the main table)using the seed
and increment specified. We need all the records of this table to be
in sequential order - i.e. we need all purchase orders to be in
sequence. Now there are two different types purchase orders different
enough to have entity/tables of their own. So what are the downsides
of using the primary key generated in the main table which would
normally be a foreign key to the child table, as the actual primary
key in the child tables.
>
Thanks
>
KR

|||KR (kraman@.bastyr.edu) writes:

Quote:

Originally Posted by

Let us say you have a parent/master table( Ex: purchase order) that
is generating number (primary key for the main table)using the seed
and increment specified. We need all the records of this table to be
in sequential order - i.e. we need all purchase orders to be in
sequence.

Do you permit gaps in the sequence? Since you talk about seed and
increment, I suspect that you are using IDENTITY. Beware that IDENTITY
is very likely to give you gaps, since if an INSERT fails, an IDENTITY
number is still consumed.

Quote:

Originally Posted by

Now there are two different types purchase orders different
enough to have entity/tables of their own. So what are the downsides
of using the primary key generated in the main table which would
normally be a foreign key to the child table, as the actual primary
key in the child tables.

So you would have

CREATE TABLE Orders (OrderID int NOT NULL PRIMARY KEY,
...
go
CREATE TABLE BlackOrders(OrderID int NOT NULL
PRIMARY KEY REFERENCES Orders(OrderId),
...

go
CREATE TABLE WhiteOrders(OrderID int NOT NULL
PRIMARY KEY REFERENCES Orders(OrderId),
...

That's a perfectly normal design, and quite a common way to address
supertypes and subtypes.

Joe Celko has a twist to this:

CREATE TABLE Orders (OrderID int NOT NULL PRIMARY KEY,
OrderColour char(3)
CHECK (OrderColour IN ('BLK', 'WHT'))
...
UNIQUE(OrderId, OrderColour
go
CREATE TABLE BlackOrders(OrderID int NOT NULL PRIMARY KEY,
OrderColour char(3)
DEFAULT 'BLK',
CHECK (OrderColour = 'BLK'),
...
FOREIGN KEY (OrderID, OrderColour)
REFERENCES Orders(OrderId, OrderColour),
...

In this way you also assures that BlackOrders really only have black
orders.

Note: since the hour is later, I'm tired at the end of the week etc,
I have left out constraint names. But I like to stress that best
practice is to name your constraints.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On May 25, 9:18 pm, KR <kra...@.bastyr.eduwrote:

Quote:

Originally Posted by

Hi All,
>
I wanted the expert opinion out there in the use of foreign keys as
primary keys in a table. I am not very good at explaining this
concept, but I am going to try -
>
Let us say you have a parent/master table( Ex: purchase order) that
is generating number (primary key for the main table)using the seed
and increment specified. We need all the records of this table to be
in sequential order - i.e. we need all purchase orders to be in
sequence. Now there are two different types purchase orders different
enough to have entity/tables of their own. So what are the downsides
of using the primary key generated in the main table which would
normally be a foreign key to the child table, as the actual primary
key in the child tables.
>
Thanks
>
KR

1. Your PK is increment and seeded . So you are using Identity. Note
that Identity can have gaps .
2. If you have two different Purchase Order tables , what you can do
create another table having a union of PK from two tables and in
detail table FK will be pointing to this union table
3. If you don't want to go through 2, you need to create a trigger on
your detail table to check existence of key|||> We need all the records [sic] of this table to be in sequential order <<

Rows are not records and tables have no ordering. That is physical
and applies to files, which do have records. You are still un-
learning file systems.

Quote:

Originally Posted by

Quote:

Originally Posted by

>i.e. we need all purchase orders to be in sequence. <<

That is a logical condition that implies you have no gaps in PO
numbers. This sequence can be either numeric or temporal. I would
assume numeric. The constraint for no gaps is that:

(SELECT MAX(order_nbr) - MIN(order_nbr) + 1 FROM PurchaseOrders)
= (SELECT COUNT (order_nbr) FROM PurchaseOrders)

In Standard SQL-92, this would be in a CHECK() constraint; SQL Server
has to use a TRIGGER or stored procedure.

Quote:

Originally Posted by

Quote:

Originally Posted by

>there are two different types purchase orders different enough to have entity/tables of their own. So what are the downsides of using the primary key generated in the main table [sic: referenced table] which would normally be a foreign key to the child table [sic: referencing table], as the actual primary key in the child tables [sic]. <<

The terms parent and child are from old network databases and imply a
pointer chain. RDBMS uses references rather than points. Subtle but
important differences!

Erland already showed you my trick for doing sub-classes in SQL.