foreign keys

Last Post 14 Nov 2006 06:36 AM by vickicowles. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
vickicowles
New Member
New Member

--
08 Nov 2006 12:44 PM
I have a parent table ( P ) with 2 child tables (C1 and C2, relation to parent is 1 to many). I need another '1 to many' relation from C1 to C2. Does this often happen in other databases or have I just not done the design correctly?
mwesch
New Member
New Member

--
08 Nov 2006 12:59 PM
Abstractly, you may be able to explain any schema. For example, in the example you gave, I could fill in entities such as:

PARENT = REVIEWER
CHILD1 = AUTHOR
CHILD 2 = BOOK

Each Reviewer could review 0 or more authors.
Each Reviewer could review 0 or more books.
Each Author can write 1 or more books.

If you want validation of your specific case, describe the business case and your proposed schema. I'm sure someone here will offer some advice or maybe someone even has experience with the same.
vickicowles
New Member
New Member

--
09 Nov 2006 06:35 AM
Thanks for the example and advice. This is what I have going on:

PARENT = Contract. has a total contract amount
CHILD1 = Task. all contracts have tasks that have amounts assigned. these task amounts sum up to the total value of the contract. payments for work done on a contract are made to the tasks.
CHILD2 = Subcontracts. some contracts, not all, have subcontracts aka work orders. work orders also have an amount assigned. work orders usually have only 1 task but sometimes need to be able to have more than 1.

Originally I thought work orders and task were the same thing but then realized work orders can have sometimes (not very often) have many tasks. So I made them separate tables with task a child of work order.

Comments?
mwesch
New Member
New Member

--
09 Nov 2006 11:05 AM
Are subcontracts essentially the same thing as a contract? If so, maybe a self-referential relationship where Contract relates to itself.

Contract
-------------
ContractID [pkey]
ParentContractID [optional fkey to ContractID]


Task
-------------
TaskID [pkey]
ContractID [mandatory fkey to ContractID]

vickicowles
New Member
New Member

--
14 Nov 2006 06:36 AM

Are there advantages/disadvantages to the self join as opposed to the child table?
mwesch
New Member
New Member

--
14 Nov 2006 07:47 AM
The self-join would mean one less table to have to store data, so that the same classes/functions that you develop in your application to create contracts could be used for sub-contracts.

I also find it scalable in the sense that if contracts can have sub-contracts, might there be a chance that you had a project so big that you'd have a contract, with multiple sub-contracts, and possibly those sub-contracts might have sub-contracts.
SQLUSA
New Member
New Member

--
14 Nov 2006 03:04 PM
SQL 2005 can handle a "contract tree" .


Kalman Toth DB, DW & BI Architect, SQL Server Training
URL: http://www.sqlusa.com/order2005grandprix
The Best SQL Server 2005 Training in the World
You are not authorized to post a reply.

Acceptable Use Policy