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 -
/ / ---------------------
/ /
/ /
tbBrochureHeadi ngMap / / 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 -
----------------------
|
|
tbBrochureHeadi ngMap | tbHeading
---------------------------- / ----------------------
- BrochureHeading MapId (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 tbBrochureHeadi ngMap 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'[tbBrochureHeadi ngMap]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
drop table [tbBrochureHeadi ngMap]
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,
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(Broch ureId,LanguageI d),
FOREIGN KEY (LanguageId)
REFERENCES tbLanguage(Lang uageId)
)
go
CREATE TABLE tbHeading
(
HeadingId int identity(1,1) not null,
HeadingText varchar(1000) not null,
PRIMARY KEY CLUSTERED (HeadingId)
)
go
CREATE TABLE tbBrochureHeadi ngMap
(
BrochureId int not null,
LanguageId int not null,
HeadingId int not null,
PRIMARY KEY CLUSTERED (BrochureId,Lan guageId,Heading Id),
FOREIGN KEY (BrochureId,Lan guageId)
REFERENCES tbBrochure (BrochureId,Lan guageId),
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,Lan guageId,Heading Id,SequenceNo),
FOREIGN KEY (BrochureId,Lan guageId)
REFERENCES tbBrochure (BrochureId,Lan guageId),
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'[tbBrochureHeadi ngMap]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
drop table [tbBrochureHeadi ngMap]
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,
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(Broch ureId),
FOREIGN KEY (LanguageId)
REFERENCES tbLanguage(Lang uageId)
)
go
CREATE NONCLUSTERED INDEX ix_tbBrochure_L anguageId 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 tbBrochureHeadi ngMap
(
BrochureHeading MapId int identity(1,1) not null,
BrochureId int not null,
HeadingId int not null,
PRIMARY KEY CLUSTERED (BrochureHeadin gMapId),
FOREIGN KEY (BrochureId)
REFERENCES tbBrochure (BrochureId),
FOREIGN KEY (HeadingId)
REFERENCES tbHeading (HeadingId)
)
go
CREATE NONCLUSTERED INDEX ix_tbBrochureHe adingMap_Brochu reId ON
tbBrochureHeadi ngMap (BrochureId)
go
CREATE NONCLUSTERED INDEX ix_tbBrochureHe adingMap_Headin gId ON
tbBrochureHeadi ngMap (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
tbBrochureHeadi ngMap (HeadingId)
go 1 4384
[posted and mailed, please reply in news]
Michael D (so*****@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, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Lefevre, Steven |
last post by:
Hey folks -
Thanks to everyone who gave input to my concerns. Of course, we don't intend
to have the mysql port open to the world. We will have Apache/PHP connect on
a unix socket, or to another machine with a cross-cable on non-routeable
IPs.
But now I have another question. We are working on a web database to allow
our suppliers to log on and submit information that they would otherwise
fax, email, or phone to us. It would reduce...
|
by: Josh McFarlane |
last post by:
If this is not the right place to post this, I apologize.
I've taken over work for a few utility programs for a collection of
database / raw files. All the programs but one read from the files, and
as it is, many of the operations are done through standard non-OO code.
All of the database information is currently stored in linked list
structures. I'm looking to move the entire thing to objects.
I'm defining generic database, table, and...
|
by: Don Vaillancourt |
last post by:
Hello all,
Over the years as I design more database schemas the more I come up with
patterns in database design. The more patterns I recognize the more I
want to try to design some kind of generic design patterns that can be
used and shared amongst many sub-schemas.
For example, the grouping of entities. I may have the following tables:
employee, product and client. These tables have no direct relationship
with each other. But...
|
by: MP |
last post by:
Greets,
context: vb6/ado/.mdb/jet 4.0 (no access)/sql
beginning learner, first database, planning stages
(I think the underlying question here is whether to normalize or not to
normalize this one data field - but i'm not sure)
:-)
Background info:
|
by: nyathancha |
last post by:
Hi,
I have a question regarding best practices in database design. In a
relational database, is it wise/necessary to sometimes create tables
that are not related to other tables through a foreign Key
relationship or does this always indicate some sort of underlying
design flaw. Something that requires a re evaluation of the problem
domain?
The reason I ask is because in our application, the user can perform x
| |
by: arrival123 |
last post by:
Hello,
I'm currently trying to decide on a database design for tags in my web 2.0 application. The problem I'm facing is that I have 3 separate tables i.e. cars, planes, and schools. All three tables need to interact with the tags, so there will only be one universal set of tags for the three tables.
I read a lot about tags and the best articles I found were:
Road to Web 2.0 ( http://wyome.com/docs/Road_to_Web_2.0:_The_Database_Design )...
|
by: ARC |
last post by:
Hello all,
General question for back-end database that has numerous date fields where
the database will be used in regions that put the month first, and regions
that do not.
Should I save a date format in the table design, such as: mm/dd/yyyy?
What I've done for years is to store the date format in date fields, then on
the forms, based on their region, I would set the date formats on form_load
|
by: dgleeson3 |
last post by:
Hello all
I am creating a VB.Net distributed SQL server 2005 application.
Each computer in the system has a database with a table of users and
their telephone numbers.
Each computer has a unique 4 digit identifying code.
The central server runs an application which reads the database table
on each computer.
|
by: David |
last post by:
Hi list.
I have a few database-related questions. These aren't Python-specific
questions, but some of my apps which use (or will use) these tables
are in Python :-) Let me know if I should ask this on a different
list.
Question 1: Storing app defaults.
If you have a table like this:
|
by: Les Desser |
last post by:
In article
<fcebdacd-2bd8-4d07-93a8-8b69d3452f3e@s50g2000hsb.googlegroups.com>, The
Frog <Mr.Frog.to.you@googlemail.comMon, 14 Apr 2008 00:45:10 writes
Not sure if I quite follow that.
1. Data encrypted by AES key
2. AES key encrypted with Asymmetric public key (?)
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |