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 Diagrams, SQL Server 2005

Hi everyone,

I was wondering if there is any way to generate a database diagram with foreign key relationships automatically generated between tables in SQL Server 2005... My initial investigations has yielded no, but I'd like to be sure.

Thank you

Chris

The database diagramming tool won't automatically create foreign key contraints on your tables. You can explicitly create foreign key relationships in a diagram by clicking on one table and dragging the relationship line to the related table.

If you have two existing tables with a foreign key relating them, the relationship line should automatically display in the diagram as soon as you add both tables to the diagram.

Hope this helps,
Steve

|||Thank you very much Steve, very helpful.

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

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

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 problem

I have a problem when the foreign key in a table points to the primary
key in the same table. Here is the script to create the tables:
CREATE TABLE [Folder] (
[FolderID] [int] NOT NULL ,
[MasterFolderID] [int] NULL ,
CONSTRAINT [PK_Folder] PRIMARY KEY CLUSTERED
(
[FolderID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Folder_Folder] FOREIGN KEY
(
[MasterFolderID]
) REFERENCES [Folder] (
[FolderID]
)
) ON [PRIMARY]
GO
CREATE TABLE [File] (
[FileID] [int] NOT NULL ,
[FolderID] [int] NOT NULL ,
[FileSize] [int] NOT NULL ,
CONSTRAINT [PK_File] PRIMARY KEY CLUSTERED
(
[FileID]
) ON [PRIMARY] ,
CONSTRAINT [FK_File_Folder] FOREIGN KEY
(
[FolderID]
) REFERENCES [Folder] (
[FolderID]
)
) ON [PRIMARY]
GO
So I have a "Folder" table as the master and a "File" table as the
detail, but the Folder table also has a one to many relationship to
itself.
Then I have the following data in these two tables:
INSERT INTO [Folder]([FolderID])
VALUES(1)
INSERT INTO [Folder]([FolderID], [MasterFolderID])
VALUES(2,1)
INSERT INTO [File]([FileID], [FolderID], [FileSize])
VALUES(1, 1, 10)
INSERT INTO [File]([FileID], [FolderID], [FileSize])
VALUES(2, 2, 10)
The "File" table has a field called FileSize that needs to be added to
give you the size of each folder. Here is the sql statement I use for
that:
Select FolderID,
(Select Sum(FileSize) From [File] Where [File].FolderID =
Folder.FolderID) As Size
From Folder
The problem with the above statement is that it only adds sizes of the
files in the folder and not the sizes of the folders in a folder.
I hope someone understands my problem and would realy appreciate some
help.To make sure:
Folder 1
File 1a 10 kb
File 1b 10 kb
Folder 1.1
File 1.1a 10 kb
File 1.1b 10 kb
You want the following:
Folder 1 40kb
Folder 1.1 20kb
Right?
That is done via recursion. Unfortunately, there is no simple way to
accomplish this in T-SQL (at least not until SQL Server 2005). To accomplish
this, you will have to curse through the hierarchy and create the aggregates
.
My advice, esp. if this is a large app: Denormalize slightly to add the size
to the folder. Then create a routine that curses through and gets the total.
Finally, create a trigger that updates totals when a new record is added.
NOTE: This assumes that this is not an oft updated/inserted table. If it is,
you can still get totals for the files and recude the amount of recursion
work necessary to get the final tally for each directory (reduce by one "sum
"
aggregate).
--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Pierre" wrote:

> I have a problem when the foreign key in a table points to the primary
> key in the same table. Here is the script to create the tables:
> CREATE TABLE [Folder] (
> [FolderID] [int] NOT NULL ,
> [MasterFolderID] [int] NULL ,
> CONSTRAINT [PK_Folder] PRIMARY KEY CLUSTERED
> (
> [FolderID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Folder_Folder] FOREIGN KEY
> (
> [MasterFolderID]
> ) REFERENCES [Folder] (
> [FolderID]
> )
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [File] (
> [FileID] [int] NOT NULL ,
> [FolderID] [int] NOT NULL ,
> [FileSize] [int] NOT NULL ,
> CONSTRAINT [PK_File] PRIMARY KEY CLUSTERED
> (
> [FileID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_File_Folder] FOREIGN KEY
> (
> [FolderID]
> ) REFERENCES [Folder] (
> [FolderID]
> )
> ) ON [PRIMARY]
> GO
> So I have a "Folder" table as the master and a "File" table as the
> detail, but the Folder table also has a one to many relationship to
> itself.
> Then I have the following data in these two tables:
>
> INSERT INTO [Folder]([FolderID])
> VALUES(1)
> INSERT INTO [Folder]([FolderID], [MasterFolderID])
> VALUES(2,1)
> INSERT INTO [File]([FileID], [FolderID], [FileSize])
> VALUES(1, 1, 10)
> INSERT INTO [File]([FileID], [FolderID], [FileSize])
> VALUES(2, 2, 10)
>
> The "File" table has a field called FileSize that needs to be added to
> give you the size of each folder. Here is the sql statement I use for
> that:
> Select FolderID,
> (Select Sum(FileSize) From [File] Where [File].FolderID =
> Folder.FolderID) As Size
> From Folder
> The problem with the above statement is that it only adds sizes of the
> files in the folder and not the sizes of the folders in a folder.
> I hope someone understands my problem and would realy appreciate some
> help.
>

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

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.