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...
> list
> table
>|||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...
>
>

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 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...
> list
> table
>
|||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...
>
>

Database Diagrams: FOREIGN KEY constraint "fell off"; can't drop/recreate it...

Hello,

I have two tables Person & Location where Location has a primary key LocationId and Person has a foreign key LocationId.

Sometime ago I used the Database Diagrams visual tool of SQL Server Management Studio Express to create the foreign key relationship between the two tables - i.e. "visually" (drawing a line between the PK & FK LocationId elements of both tables).

Time has passed and I recently noticed that, upon retrieving my saved diagram, the foreign key relationship had "fallen off" (i.e. the many-to-one line was no longer showing in the diagram).

After recreating the relationship (redrawing the line) I find that I get an error message when I try to save the diagram:

Post-Save Notifications
[!] Errors were encountered during the save process. Some database objects were not saved.

'Location' table saved successfully
'Person' table
- Unable to create relationship 'FK_Person_Location'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Person_Location". The conflict occurred in database "mydb", table "dbo.Location", column 'LocationId'.

When I go back to the object explorer and view the dependencies for the two tables, there is no dependency (between these two tables) revealed. When I try to create the foreign key constraint manually (T-SQL) it again says can't add the constraint. It comes up with an error as follows:

ALTER TABLE Person
ADD FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

Msg 547, Level 16, State 0, Line 2
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK__Person__LocationId__793DFFAF". The conflict occurred in database "mydb", table "dbo.Location", column 'LocationId'.

(Note: Each time I do this, the 8 hexadecimal character suffix changes.)

When I try to drop the foreign key:

alter table Person
drop constraint FK__Person__LocationId

it comes back with the error:

Msg 3728, Level 16, State 1, Line 2
'FK__Person__LocationId' is not a constraint.
Msg 3727, Level 16, State 0, Line 2
Could not drop constraint. See previous errors.

So it seems that there's some kind of goof up here. Can anybody shed light on this / tell me how to fix it?

Best guess, you have data that will not pass the Foreign key constraint. As for why it "dropped off" that is a totally different question that can;t be answered here (do your developers have dbo access and possibly dropped the constraint so they could insert invalid data to make the UI work? (no offense to any developer who wouldn't do that :) )

create table person
(
personId int constraint PKperson primary key,
locationId int
)
insert into person
values (1,1)
insert into person
values (2,2)

create table location
(
locationId int constraint PKlocation primary key
)

ALTER TABLE Person
ADD FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK__person__location__4B0D20AB". The conflict occurred in database "master", table "dbo.location", column 'locationId'.

Better to name the constraint:
ALTER TABLE Person
ADD Constraint FKPerson_References_Location FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKPerson_References_Location". The conflict occurred in database "master", table "dbo.location", column 'locationId'.

insert into location
values (1)
insert into location
values (2)

ALTER TABLE Person
ADD Constraint FKPerson_References_Location FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

to see the constraints and the columns you can use this query:

select *
from information_schema.constraint_column_usage as ccu
join information_schema.referential_constraints as rc
on rc.constraint_schema = ccu.table_schema
and ccu.constraint_name = rc.constraint_name
where ccu.table_name = 'person'

Also, if you want to ignore the invalid data there is a NOCHECK clause when creating constraints, but it is highly advised to not use it.

|||

Thanks Louis for the useful info. It helped in examining the problem more closely.

In the end I decided just to save off my data from the Location table, and to drop / recreate it. Everything is working fine again. I really believe that an inconsistency got in the system tables somewhere - that was my main concern. By dropping / recreating the problem table I believe I eliminated the inconsistency.

|||I get the same error. I wish I could find the source of it in the system tables (or wherever the cause is located) and fix it - I do not have the luxury of merely trying to drop and recreata the tables.|||

Hi all,

I have the same error. But only on my table that I migrate from SQL 2000 to SQL 2005.

And I can't drop and recreate table.

How can I do ?

|||

The ALTER TABLE that you were using does not have a constraint name specified so SQL Server will generate a name automatically and since constraint names have to be unique SQL Server uses combination of table / column names / unique number to make it unique. But it looks like you never had the FK constraint defined so the tables would have contained invalid data. And when you tried to create the constraint from the diagram designer it failed and the constraint creation gets rolled back. You could have checked the data in the table like below to find the offending rows:

select *

from Person as p

where not exists(

select * from Location as l

where l.LocationId = p.LocationId

)

But you shouldn't use diagram designer as the modelling tool. It is not designed for that. It does lot of things behind the scenes which is unnecessary in some cases (like dropping & recreating tables to make certain schema changes). You could make simple schema changes but script out the schema definitions and store it in files.

Database Diagrams: FOREIGN KEY constraint "fell off"; can't drop/recreate it...

Hello,

I have two tables Person & Location where Location has a primary key LocationId and Person has a foreign key LocationId.

Sometime ago I used the Database Diagrams visual tool of SQL Server Management Studio Express to create the foreign key relationship between the two tables - i.e. "visually" (drawing a line between the PK & FK LocationId elements of both tables).

Time has passed and I recently noticed that, upon retrieving my saved diagram, the foreign key relationship had "fallen off" (i.e. the many-to-one line was no longer showing in the diagram).

After recreating the relationship (redrawing the line) I find that I get an error message when I try to save the diagram:

Post-Save Notifications
[!] Errors were encountered during the save process. Some database objects were not saved.

'Location' table saved successfully
'Person' table
- Unable to create relationship 'FK_Person_Location'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Person_Location". The conflict occurred in database "mydb", table "dbo.Location", column 'LocationId'.

When I go back to the object explorer and view the dependencies for the two tables, there is no dependency (between these two tables) revealed. When I try to create the foreign key constraint manually (T-SQL) it again says can't add the constraint. It comes up with an error as follows:

ALTER TABLE Person
ADD FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

Msg 547, Level 16, State 0, Line 2
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK__Person__LocationId__793DFFAF". The conflict occurred in database "mydb", table "dbo.Location", column 'LocationId'.

(Note: Each time I do this, the 8 hexadecimal character suffix changes.)

When I try to drop the foreign key:

alter table Person
drop constraint FK__Person__LocationId

it comes back with the error:

Msg 3728, Level 16, State 1, Line 2
'FK__Person__LocationId' is not a constraint.
Msg 3727, Level 16, State 0, Line 2
Could not drop constraint. See previous errors.

So it seems that there's some kind of goof up here. Can anybody shed light on this / tell me how to fix it?

Best guess, you have data that will not pass the Foreign key constraint. As for why it "dropped off" that is a totally different question that can;t be answered here (do your developers have dbo access and possibly dropped the constraint so they could insert invalid data to make the UI work? (no offense to any developer who wouldn't do that :) )

create table person
(
personId int constraint PKperson primary key,
locationId int
)
insert into person
values (1,1)
insert into person
values (2,2)

create table location
(
locationId int constraint PKlocation primary key
)

ALTER TABLE Person
ADD FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK__person__location__4B0D20AB". The conflict occurred in database "master", table "dbo.location", column 'locationId'.

Better to name the constraint:
ALTER TABLE Person
ADD Constraint FKPerson_References_Location FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKPerson_References_Location". The conflict occurred in database "master", table "dbo.location", column 'locationId'.

insert into location
values (1)
insert into location
values (2)

ALTER TABLE Person
ADD Constraint FKPerson_References_Location FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

to see the constraints and the columns you can use this query:

select *
from information_schema.constraint_column_usage as ccu
join information_schema.referential_constraints as rc
on rc.constraint_schema = ccu.table_schema
and ccu.constraint_name = rc.constraint_name
where ccu.table_name = 'person'

Also, if you want to ignore the invalid data there is a NOCHECK clause when creating constraints, but it is highly advised to not use it.

|||

Thanks Louis for the useful info. It helped in examining the problem more closely.

In the end I decided just to save off my data from the Location table, and to drop / recreate it. Everything is working fine again. I really believe that an inconsistency got in the system tables somewhere - that was my main concern. By dropping / recreating the problem table I believe I eliminated the inconsistency.

|||I get the same error. I wish I could find the source of it in the system tables (or wherever the cause is located) and fix it - I do not have the luxury of merely trying to drop and recreata the tables.|||

Hi all,

I have the same error. But only on my table that I migrate from SQL 2000 to SQL 2005.

And I can't drop and recreate table.

How can I do ?

|||

The ALTER TABLE that you were using does not have a constraint name specified so SQL Server will generate a name automatically and since constraint names have to be unique SQL Server uses combination of table / column names / unique number to make it unique. But it looks like you never had the FK constraint defined so the tables would have contained invalid data. And when you tried to create the constraint from the diagram designer it failed and the constraint creation gets rolled back. You could have checked the data in the table like below to find the offending rows:

select *

from Person as p

where not exists(

select * from Location as l

where l.LocationId = p.LocationId

)

But you shouldn't use diagram designer as the modelling tool. It is not designed for that. It does lot of things behind the scenes which is unnecessary in some cases (like dropping & recreating tables to make certain schema changes). You could make simple schema changes but script out the schema definitions and store it in files.

Database Diagrams: FOREIGN KEY constraint "fell off"; can't drop/recreate it...

Hello,

I have two tables Person & Location where Location has a primary key LocationId and Person has a foreign key LocationId.

Sometime ago I used the Database Diagrams visual tool of SQL Server Management Studio Express to create the foreign key relationship between the two tables - i.e. "visually" (drawing a line between the PK & FK LocationId elements of both tables).

Time has passed and I recently noticed that, upon retrieving my saved diagram, the foreign key relationship had "fallen off" (i.e. the many-to-one line was no longer showing in the diagram).

After recreating the relationship (redrawing the line) I find that I get an error message when I try to save the diagram:

Post-Save Notifications
[!] Errors were encountered during the save process. Some database objects were not saved.

'Location' table saved successfully
'Person' table
- Unable to create relationship 'FK_Person_Location'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Person_Location". The conflict occurred in database "mydb", table "dbo.Location", column 'LocationId'.

When I go back to the object explorer and view the dependencies for the two tables, there is no dependency (between these two tables) revealed. When I try to create the foreign key constraint manually (T-SQL) it again says can't add the constraint. It comes up with an error as follows:

ALTER TABLE Person
ADD FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

Msg 547, Level 16, State 0, Line 2
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK__Person__LocationId__793DFFAF". The conflict occurred in database "mydb", table "dbo.Location", column 'LocationId'.

(Note: Each time I do this, the 8 hexadecimal character suffix changes.)

When I try to drop the foreign key:

alter table Person
drop constraint FK__Person__LocationId

it comes back with the error:

Msg 3728, Level 16, State 1, Line 2
'FK__Person__LocationId' is not a constraint.
Msg 3727, Level 16, State 0, Line 2
Could not drop constraint. See previous errors.

So it seems that there's some kind of goof up here. Can anybody shed light on this / tell me how to fix it?

Best guess, you have data that will not pass the Foreign key constraint. As for why it "dropped off" that is a totally different question that can;t be answered here (do your developers have dbo access and possibly dropped the constraint so they could insert invalid data to make the UI work? (no offense to any developer who wouldn't do that :) )

create table person
(
personId int constraint PKperson primary key,
locationId int
)
insert into person
values (1,1)
insert into person
values (2,2)

create table location
(
locationId int constraint PKlocation primary key
)

ALTER TABLE Person
ADD FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK__person__location__4B0D20AB". The conflict occurred in database "master", table "dbo.location", column 'locationId'.

Better to name the constraint:
ALTER TABLE Person
ADD Constraint FKPerson_References_Location FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKPerson_References_Location". The conflict occurred in database "master", table "dbo.location", column 'locationId'.

insert into location
values (1)
insert into location
values (2)

ALTER TABLE Person
ADD Constraint FKPerson_References_Location FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

to see the constraints and the columns you can use this query:

select *
from information_schema.constraint_column_usage as ccu
join information_schema.referential_constraints as rc
on rc.constraint_schema = ccu.table_schema
and ccu.constraint_name = rc.constraint_name
where ccu.table_name = 'person'

Also, if you want to ignore the invalid data there is a NOCHECK clause when creating constraints, but it is highly advised to not use it.

|||

Thanks Louis for the useful info. It helped in examining the problem more closely.

In the end I decided just to save off my data from the Location table, and to drop / recreate it. Everything is working fine again. I really believe that an inconsistency got in the system tables somewhere - that was my main concern. By dropping / recreating the problem table I believe I eliminated the inconsistency.

|||I get the same error. I wish I could find the source of it in the system tables (or wherever the cause is located) and fix it - I do not have the luxury of merely trying to drop and recreata the tables.|||

Hi all,

I have the same error. But only on my table that I migrate from SQL 2000 to SQL 2005.

And I can't drop and recreate table.

How can I do ?

|||

The ALTER TABLE that you were using does not have a constraint name specified so SQL Server will generate a name automatically and since constraint names have to be unique SQL Server uses combination of table / column names / unique number to make it unique. But it looks like you never had the FK constraint defined so the tables would have contained invalid data. And when you tried to create the constraint from the diagram designer it failed and the constraint creation gets rolled back. You could have checked the data in the table like below to find the offending rows:

select *

from Person as p

where not exists(

select * from Location as l

where l.LocationId = p.LocationId

)

But you shouldn't use diagram designer as the modelling tool. It is not designed for that. It does lot of things behind the scenes which is unnecessary in some cases (like dropping & recreating tables to make certain schema changes). You could make simple schema changes but script out the schema definitions and store it in files.

Database Diagrams: FOREIGN KEY constraint "fell off"; can't drop/recreate it...

Hello,

I have two tables Person & Location where Location has a primary key LocationId and Person has a foreign key LocationId.

Sometime ago I used the Database Diagrams visual tool of SQL Server Management Studio Express to create the foreign key relationship between the two tables - i.e. "visually" (drawing a line between the PK & FK LocationId elements of both tables).

Time has passed and I recently noticed that, upon retrieving my saved diagram, the foreign key relationship had "fallen off" (i.e. the many-to-one line was no longer showing in the diagram).

After recreating the relationship (redrawing the line) I find that I get an error message when I try to save the diagram:

Post-Save Notifications
[!] Errors were encountered during the save process. Some database objects were not saved.

'Location' table saved successfully
'Person' table
- Unable to create relationship 'FK_Person_Location'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Person_Location". The conflict occurred in database "mydb", table "dbo.Location", column 'LocationId'.

When I go back to the object explorer and view the dependencies for the two tables, there is no dependency (between these two tables) revealed. When I try to create the foreign key constraint manually (T-SQL) it again says can't add the constraint. It comes up with an error as follows:

ALTER TABLE Person
ADD FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

Msg 547, Level 16, State 0, Line 2
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK__Person__LocationId__793DFFAF". The conflict occurred in database "mydb", table "dbo.Location", column 'LocationId'.

(Note: Each time I do this, the 8 hexadecimal character suffix changes.)

When I try to drop the foreign key:

alter table Person
drop constraint FK__Person__LocationId

it comes back with the error:

Msg 3728, Level 16, State 1, Line 2
'FK__Person__LocationId' is not a constraint.
Msg 3727, Level 16, State 0, Line 2
Could not drop constraint. See previous errors.

So it seems that there's some kind of goof up here. Can anybody shed light on this / tell me how to fix it?

Best guess, you have data that will not pass the Foreign key constraint. As for why it "dropped off" that is a totally different question that can;t be answered here (do your developers have dbo access and possibly dropped the constraint so they could insert invalid data to make the UI work? (no offense to any developer who wouldn't do that :) )

create table person
(
personId int constraint PKperson primary key,
locationId int
)
insert into person
values (1,1)
insert into person
values (2,2)

create table location
(
locationId int constraint PKlocation primary key
)

ALTER TABLE Person
ADD FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK__person__location__4B0D20AB". The conflict occurred in database "master", table "dbo.location", column 'locationId'.

Better to name the constraint:
ALTER TABLE Person
ADD Constraint FKPerson_References_Location FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKPerson_References_Location". The conflict occurred in database "master", table "dbo.location", column 'locationId'.

insert into location
values (1)
insert into location
values (2)

ALTER TABLE Person
ADD Constraint FKPerson_References_Location FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

to see the constraints and the columns you can use this query:

select *
from information_schema.constraint_column_usage as ccu
join information_schema.referential_constraints as rc
on rc.constraint_schema = ccu.table_schema
and ccu.constraint_name = rc.constraint_name
where ccu.table_name = 'person'

Also, if you want to ignore the invalid data there is a NOCHECK clause when creating constraints, but it is highly advised to not use it.

|||

Thanks Louis for the useful info. It helped in examining the problem more closely.

In the end I decided just to save off my data from the Location table, and to drop / recreate it. Everything is working fine again. I really believe that an inconsistency got in the system tables somewhere - that was my main concern. By dropping / recreating the problem table I believe I eliminated the inconsistency.

|||I get the same error. I wish I could find the source of it in the system tables (or wherever the cause is located) and fix it - I do not have the luxury of merely trying to drop and recreata the tables.|||

Hi all,

I have the same error. But only on my table that I migrate from SQL 2000 to SQL 2005.

And I can't drop and recreate table.

How can I do ?

|||

The ALTER TABLE that you were using does not have a constraint name specified so SQL Server will generate a name automatically and since constraint names have to be unique SQL Server uses combination of table / column names / unique number to make it unique. But it looks like you never had the FK constraint defined so the tables would have contained invalid data. And when you tried to create the constraint from the diagram designer it failed and the constraint creation gets rolled back. You could have checked the data in the table like below to find the offending rows:

select *

from Person as p

where not exists(

select * from Location as l

where l.LocationId = p.LocationId

)

But you shouldn't use diagram designer as the modelling tool. It is not designed for that. It does lot of things behind the scenes which is unnecessary in some cases (like dropping & recreating tables to make certain schema changes). You could make simple schema changes but script out the schema definitions and store it in files.

Database Design Questions...

I am using SQL Server 2000.
I got two tables.
Table1:- PriceList
Column are ProductId(Primary Key) and rest of the column.
Table2:- PriceListHistory
Column are same columns as "PriceList" plus one more column to keep track of
change history.
I can add one more column to keep track of price change history and make
that as part of the primary key.
Questions is what should be the data type of this extra column. Can use data
type as datetime or use interger ? Is it not a
good idea to use datetime as part of the pimary key.
Any suggestions ?I'd go an integer with the identity property to make life very easy. Dates
as part of a PK can be problematic - dates can be reset on machines etc...
Also if 2 recs for the same part come in together they might get the same
time.
"Gary Smith" <GarySmith_77@.yahoo.com> wrote in message
news:OjkYNN81DHA.1760@.TK2MSFTNGP10.phx.gbl...

quote:

---

> I am using SQL Server 2000.
> I got two tables.
> Table1:- PriceList
> Column are ProductId(Primary Key) and rest of the column.
> Table2:- PriceListHistory
> Column are same columns as "PriceList" plus one more column to keep track

---

of

quote:

---

> change history.
> I can add one more column to keep track of price change history and make
> that as part of the primary key.
> Questions is what should be the data type of this extra column. Can use

---

data

quote:

---

> type as datetime or use interger ? Is it not a
> good idea to use datetime as part of the pimary key.
> Any suggestions ?
>

---

Database Design Questions...

I am using SQL Server 2000.
I got two tables.
Table1:- PriceList
Column are ProductId(Primary Key) and rest of the column.
Table2:- PriceListHistory
Column are same columns as "PriceList" plus one more column to keep track of
change history.
I can add one more column to keep track of price change history and make
that as part of the primary key.
Questions is what should be the data type of this extra column. Can use data
type as datetime or use interger ? Is it not a
good idea to use datetime as part of the pimary key.
Any suggestions ?I'd go an integer with the identity property to make life very easy. Dates
as part of a PK can be problematic - dates can be reset on machines etc...
Also if 2 recs for the same part come in together they might get the same
time.
"Gary Smith" <GarySmith_77@.yahoo.com> wrote in message
news:OjkYNN81DHA.1760@.TK2MSFTNGP10.phx.gbl...
> I am using SQL Server 2000.
> I got two tables.
> Table1:- PriceList
> Column are ProductId(Primary Key) and rest of the column.
> Table2:- PriceListHistory
> Column are same columns as "PriceList" plus one more column to keep track
of
> change history.
> I can add one more column to keep track of price change history and make
> that as part of the primary key.
> Questions is what should be the data type of this extra column. Can use
data
> type as datetime or use interger ? Is it not a
> good idea to use datetime as part of the pimary key.
> Any suggestions ?
>

Database Design Question

Hi Guys

I have one parent entity that is related to two other entity with one to many relatetion ship (i,e main entitys primary is becoming part of primary key of other entitys i,e Identifying relation ship).when these
two entitys are involved in many to many.(one column (primary key of main entity) is common in both the entitys).then what would be primary key of intersetion table or associative table.

ex : A(projId) --> B(ProjId,P1)
-->C(ProjId,P2)

When B and C are involved in manay to many realteionship
ProjId is common in both the entitys.

(... ) is primary key

Can any body explain what would be the scenario.

Thanks
UmashankarI may be mis-interpreting your question, but I think the answer is that there is no relationship between Tables B and C outside of the relationship that exists through Table A. I guess you could say that there would be a Many-to-Many relationship between B and C, but I don't think that it would be very useful to any kind of informative analysis (short of alway using SELECT DISTINCT on the tables). Any kind of JOINs that you are going to do between B and C are always going to have to include A.

Table A
ProjID int IDENTITY,
ProjName

Table B
ProjID int,
TaskID int IDENTITY,
TaskName

Table C
ProjID int,
CostID int IDENTITY,
CostName

If this doesn't answer your question, perhaps you could rephrase it or include more detail in your DDL post.

Regards,

hmscott|||Thanks hmscott.

I am trying to understand the existing database datamodel.

In this Project ,Task,Assignment,Resource are four entitiys.

Primary Key Information:

Project :Proj_Id
Task :Proj_id,Task_uid
Resource :Res_uid,Proj_id
Assignment :Assn_uid,Proj_id

In a given model thare is
1.one to many relationship(identifying) between Project and Task.
2.one to many relationship(non Identifying) between Task and Assignments.
3. one to many relationship(non Identifying) between Resource and Assignments.

Surprising to me thare is no relationship shown between project and Assignments in datamodel diagram.But Assignment entity has Proj_id as part of primary key.

Is Proj_id in Assignments entity came due to relation with tasks and resource entity.

Regards
Umashankar




Originally posted by hmscott
I may be mis-interpreting your question, but I think the answer is that there is no relationship between Tables B and C outside of the relationship that exists through Table A. I guess you could say that there would be a Many-to-Many relationship between B and C, but I don't think that it would be very useful to any kind of informative analysis (short of alway using SELECT DISTINCT on the tables). Any kind of JOINs that you are going to do between B and C are always going to have to include A.

Table A
ProjID int IDENTITY,
ProjName

Table B
ProjID int,
TaskID int IDENTITY,
TaskName

Table C
ProjID int,
CostID int IDENTITY,
CostName

If this doesn't answer your question, perhaps you could rephrase it or include more detail in your DDL post.

Regards,

hmscott

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

I have to design choices
Here's the situation (OLTP):
I have a WorkOrder table that could contain millions of record and a
WorkOrderId primary key. The record is inserted and then updated once
by a technician. The workorder is created when the user access a web
page and then updated when the user save the work order. The work
order is almost never canceled after it's creation.
The choices
1) I do all my processing directly in the table.
2) I have a temp table which is commited to the real table when the
technician saves the work orders.
What do you think? Are all these OLTP order systems working with temp
tables. Is there some things I should consier like having no indexes
on a temp table so insert is faster and response time is better. I
have a low number of user and not many concurrent requests but a lot of
data can accumulate over time.
Thanks for your time.
ObIt may not be as complicated as you apparently think. Just present the Web
page to the user - collect all then info there and then when the user goes
to save it, do an insert and you're done.
But have I missed something?
You talk about "all my processing directly in the table" or "do all my
processing..."
What processing is that? I couldn't imagine what it could possibly be that
would require any database involvement (temp table or otherwise) prior to
the user saving the new work order.
We could provide you with more specific guidance if you tell us what all
this processing is that you refer to that is apparently required on creation
of new work orders.
-HTH
"oberyn" <oberyn1martell@.yahoo.com> wrote in message
news:1141966631.129590.181350@.p10g2000cwp.googlegroups.com...
>I have to design choices
> Here's the situation (OLTP):
> I have a WorkOrder table that could contain millions of record and a
> WorkOrderId primary key. The record is inserted and then updated once
> by a technician. The workorder is created when the user access a web
> page and then updated when the user save the work order. The work
> order is almost never canceled after it's creation.
> The choices
> 1) I do all my processing directly in the table.
> 2) I have a temp table which is commited to the real table when the
> technician saves the work orders.
> What do you think? Are all these OLTP order systems working with temp
> tables. Is there some things I should consier like having no indexes
> on a temp table so insert is faster and response time is better. I
> have a low number of user and not many concurrent requests but a lot of
> data can accumulate over time.
> Thanks for your time.
> Ob
>|||ok I tried to simplify my situation to much. What I have is a work
order that can be modified multiple times before saving. Right now we
have a flag that represents the state of the work order each time it is
modified. And the same flag is set to completed when the work order is
saved. This state managing could all be done in an object and commited
to the database when the user saves the work order but we decided to
put it in the database to persist the state for up to 1 day because we
don't to want to have many pending asp.net sessions persisted (sql
server session state) all day and the technician has to be able to
resume the work order at any time during the day.
Should I have some sort of Cart table to hold the work orders and
commit them to the WorkOrder table when the flag is set to completed or
inserting and updating the main table (work order) with millions of
record is ok.?
Jordan wrote:
> It may not be as complicated as you apparently think. Just present the Web
> page to the user - collect all then info there and then when the user goes
> to save it, do an insert and you're done.
> But have I missed something?
> You talk about "all my processing directly in the table" or "do all my
> processing..."
> What processing is that? I couldn't imagine what it could possibly be that
> would require any database involvement (temp table or otherwise) prior to
> the user saving the new work order.
> We could provide you with more specific guidance if you tell us what all
> this processing is that you refer to that is apparently required on creati
on
> of new work orders.
> -HTH
>
>
> "oberyn" <oberyn1martell@.yahoo.com> wrote in message
> news:1141966631.129590.181350@.p10g2000cwp.googlegroups.com...|||Instead of a flag in your main table, why don't you have a
WorkOrderStatus table? Minimize UPDATEs, and simply INSERT new rows to
the Status table; when you want to find out what status a WorkOrder
has, join it to the Status table and retrieve the most recent status?
Stu|||Okay, then given this additional information I would go with the
recommendation earlier by Stu. His recommendation makes a lot of sense
because each of your "work order" thingies can each exist in multiple states
over time. Each of those states goes into a "Status" table which gives you
the benefit of knowing a date/time (if you record that, of course) of each
status change. A status "flag" column in your WorkOrder table would not
permit you to record or know the history of the order. Plus, from a data
modeling point of view an [order] is just an [order] regardless of its
current status ([current status] is just an attribute). Modeling an
[incomplete order] as a separate entity from a [completed order] would
likely be a misconceptualization of the data.
Finally and alternatively, you could persist the state data in a cookie (and
set it to expire after 24 hours) on the client and only store completed
orders in the database. But if you do this then you lose out on the status
history info and have a more fragile system to support (e.g., users with
cookies disabled, switching from one computer to another thoughout the day,
etc).
-HTH
"oberyn" <oberyn1martell@.yahoo.com> wrote in message
news:1141998248.065606.52840@.z34g2000cwc.googlegroups.com...
> ok I tried to simplify my situation to much. What I have is a work
> order that can be modified multiple times before saving. Right now we
> have a flag that represents the state of the work order each time it is
> modified. And the same flag is set to completed when the work order is
> saved. This state managing could all be done in an object and commited
> to the database when the user saves the work order but we decided to
> put it in the database to persist the state for up to 1 day because we
> don't to want to have many pending asp.net sessions persisted (sql
> server session state) all day and the technician has to be able to
> resume the work order at any time during the day.
> Should I have some sort of Cart table to hold the work orders and
> commit them to the WorkOrder table when the flag is set to completed or
> inserting and updating the main table (work order) with millions of
> record is ok.?
>
> Jordan wrote:
>|||I disagree. Finding the "latest status" for a workitem would require
either a "group by" or some flag to indicate which row is the latest
which frequently causes problems and requires an insert to also do an
update.
As I see it, a WorkItem only has 2 status 'Complete' and 'In Process'
and therefore will work fine with just the status on the main table.
If properly indexed, SQL server will have no problem updating 1 row out
of millions. If however, the status implies some sort of workflow then
storing each status, as well as other relative audit data historically
may have some value.
You should never rely on storing state in anything other than the
database because servers and computers can be rebooted and any objects
lost, cache cleared etc...|||Differences of opinion, I guess. As I get more experience with
database design, I've come to realize that the only constant thing in
design that you can truly rely on is that the user's specifications
will change the day after you roll something out :) I try to store as
much information as I can, and a bit flag doesn't tell me how long
something has been in that status, nor does it give me any sense of
direction (e.g., how do you handle "re-opened" tickets?). In other
words, even if the user doesn't mention workflow, I try to design
around that.
It has been my experience, though, that INSERTS are a lot less
expensive than UPDATE's, regardless of your indexes. I'd be
interested in understanding what problems you've had when dealing with
GROUP BY, and finding the latest row; it might influence some of the
decisions I'm making on a new project.
Stu|||RE:
<<Finding the "latest status" for a workitem would require either a "group
by" or some flag to indicate which row is the latest>>
Not true at all. If a date column in the Status table defualt value
automatically inserted when the status row is inserted (representing the
date/time the row was inserted), then it is real easy to get the latest
status without any GROUP BY clause - e.g., SELECT TOP N ... ORDER BY
date_column DESC.
RE:
<<a WorkItem only has 2 status 'Complete' and 'In Process'>>
You have no way to know that - and even if it were true today the
requirements could change tomorrow. What if there were 5 steps towards
completion - each of significance to the business. You would of course argue
that an int column could store a flag to indicate which of the 5 steps the
work order is at. The problem there is that you fail to record the
historical information about when the status changed, the employee who
changed the status, etc. Your little flag column really misses a lot of
potentially valuable information.
RE:
<< You should never rely on... >>
You should never say never. Really. If you are correct, then why are there
so many options for storing or managing state (database, View_State,
Application, Session, cookies, client-side objects persisted to file on the
client via XML (easily facilitated in .NET). There's a lot more to life (and
state) than databases.
-J
"JeffB" <jeff.bolton@.citigatehudson.com> wrote in message
news:1142017670.700641.290070@.i39g2000cwa.googlegroups.com...
>I disagree. Finding the "latest status" for a workitem would require
> either a "group by" or some flag to indicate which row is the latest
> which frequently causes problems and requires an insert to also do an
> update.
> As I see it, a WorkItem only has 2 status 'Complete' and 'In Process'
> and therefore will work fine with just the status on the main table.
> If properly indexed, SQL server will have no problem updating 1 row out
> of millions. If however, the status implies some sort of workflow then
> storing each status, as well as other relative audit data historically
> may have some value.
> You should never rely on storing state in anything other than the
> database because servers and computers can be rebooted and any objects
> lost, cache cleared etc...
>|||Ok the issue is a lot clearer for me now. Thank you all for your
answers.