Table design

Question

Table design

AVB

SSChampion

Points: 11418
More actions
December 4, 2007 at 6:43 am

#95518

I had a few questions about basic table design and relationships. I've created 2 tables:
Table 1:
ID Identity PK
Type varchar(8)
Table 2:
ID Identity PK
Table1_ID FK relationship with Table1 PK
Are there any good reasons why I should create a separate ID column as the PK for Table 2 instead of using the PK of Table 1 as the PK for Table 2?
Even if I create a child table to Table 2 I can still assoicate it by Table 1's PK.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Well - this is about the nature of your relationships.

First - you need to remember that a PK uniquely identifies each row. So, if a row in tableA can ONLY be related to just one row within tableB, then yes, you probably could use the same field as the PK (since it would be a unique non null value in every row in each table). Any other arrangement however requires that the FK be a separate field.

So - if you need to represent a 1-1 relationship, yes - you could "share" a PK. If it's a 1-many, then the "many" holds a FK which matches up to the PK of the "1" side.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

AVB SSChampion Points: 11418 More actions · Answer 2

Thanks for the reply.

It's a 1 to 1 relationship so I think that is the way to go. Thanks for the input.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 3

What other data are you putting in these tables? It might be that they should be the same table?

You shouldn't need the FK if this is a 1:1 as mentioned above.

AVB SSChampion Points: 11418 More actions · Answer 4

Well my example isn't really an accurate example as to what I'm doing. Below is a trimmed down version of the three tables.

What I have really is a master Lead table that is generating IDs for Sales Leads. Then I have a child table for each Lead Type. Each Lead Type data is specific to that type.

IMO they really need to be in a separate table. For example the PurchasedLead table has data that is imported via a txt file. So each lead type can have very unique data.

I'm not really sure how you would classify the relationship between the lead and leadtype tables. They are 1 to 1 in the respect that the LeadID will only exist in one of the child tables. But at the same time there are multiple tables associated to the Parent table.

Lead Table

ID Identity Seed PK

Rating

AgentID

WebLead Table

LeadID PK (FK relationship to the LeadTable)

F.Name

L.Name

CompanyName

PurchasedLead Table

LeadId PK (FK relationship to the LeadTable)

PurchasedID

PrimarySIC Code

CompanyName