473,320 Members | 1,957 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Database design

KR
Hi All,

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

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

Thanks

KR

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

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

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

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

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

hth
M@

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

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

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

Thanks

KR

May 25 '07 #2
KR (kr****@bastyr.edu) writes:
Let us say you have a parent/master table( Ex: purchase order) that
is generating number (primary key for the main table)using the seed
and increment specified. We need all the records of this table to be
in sequential order - i.e. we need all purchase orders to be in
sequence.
Do you permit gaps in the sequence? Since you talk about seed and
increment, I suspect that you are using IDENTITY. Beware that IDENTITY
is very likely to give you gaps, since if an INSERT fails, an IDENTITY
number is still consumed.
Now there are two different types purchase orders different
enough to have entity/tables of their own. So what are the downsides
of using the primary key generated in the main table which would
normally be a foreign key to the child table, as the actual primary
key in the child tables.
So you would have

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

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

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

Joe Celko has a twist to this:

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

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

Note: since the hour is later, I'm tired at the end of the week etc,
I have left out constraint names. But I like to stress that best
practice is to name your constraints.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 25 '07 #3
On May 25, 9:18 pm, KR <kra...@bastyr.eduwrote:
Hi All,

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

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

Thanks

KR

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

Rows are not records and tables have no ordering. That is physical
and applies to files, which do have records. You are still un-
learning file systems.
>i.e. we need all purchase orders to be in sequence. <<
That is a logical condition that implies you have no gaps in PO
numbers. This sequence can be either numeric or temporal. I would
assume numeric. The constraint for no gaps is that:

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

In Standard SQL-92, this would be in a CHECK() constraint; SQL Server
has to use a TRIGGER or stored procedure.
>there are two different types purchase orders different enough to have entity/tables of their own. So what are the downsides of using the primary key generated in the main table [sic: referenced table] which would normally be a foreign key to the child table [sic: referencing table], as the actual primary key in the child tables [sic]. <<
The terms parent and child are from old network databases and imply a
pointer chain. RDBMS uses references rather than points. Subtle but
important differences!

Erland already showed you my trick for doing sub-classes in SQL.
May 27 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Rushikesh | last post by:
I am designing a WEB BASED Accounting Software with ASP and SQL Server. For this I need some help for the Database design. My design is as follows. I)User table: User_id, UserName..... Users...
5
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...
1
by: Lane Beneke | last post by:
All, New to the list and a relative newbie to PostgreSQL. Please forgive stupid questions. Designing an application server for a work order processing (et al) database. I have a good handle...
5
by: trynittee | last post by:
Hello, It's been a while since I've posted. I am an intermediate user of Access. I can read simple VB code, have done complex queries, comfortable with event procedures, designing forms and...
12
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...
3
by: vicky | last post by:
Hi All, Can u please suggest me some books for relational database design or database modelling(Knowledgeable yet simple) i.e. from which we could learn database relationships(one to many,many to...
0
by: Laurynn | last post by:
# (ebook - pdf) - programming - mysql - php database applicati # (Ebook - Pdf)Learnkey How To Design A Database - Sql And Crystal Report # (ebook-pdf) E F Codd - Extending the Database Relational...
1
by: abhijitbkulkarni | last post by:
Hello, I am designing a .NET database application that uses 3 tier architecture. Starting initially, this application will be desktop application but I will convert it into a website later but...
0
by: sam | last post by:
Hi, Hope you are doing well !!!! One of our clients is looking to augment their team with “Database Architect – DB2" please find below the details and respond with
2
by: programmerx101 | last post by:
Ok, I'm looking for expert advice on this one. I have a database which keeps going into read_only mode. Sometimes it goes into read_only / single user mode. Once it was taken offline completely....
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.