Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Two tables One key - what is the best design?

Author  Topic 

numeroneq
Starting Member

8 Posts
Posted - 2003-04-08 : 10:47:34
Hi, I have a question regarding good design practice; we have a few solutions available but cannot possibly agree to which is the correct design so I hope you could help us find the right solution!

The situation is a Company that can have multiple Investors that can consist of either Individuals, Companies or a mix of both.

The proposed solutions follows (somewhat simplified to fit the forum).

First, we have two common tables equal in all solutions, the Company table and the Company_Investor table.

Table: Company
CompanyId (PK)
CompanyName
Employees

Table: Company_Investor
CompanyId (PK / FK Company)
InvestorId (PK / FK Investor)

------------------------------
1. Possible solution:
Using a master table to allocate the "InvestorId" key and using this key as primary in the two subtables for investor Individuals and investor Companies.

Table: Investor
InvestorId (PK)
Edited
EditedById

Table: InvestorIndividual
InvestorId (PK / FK Investor)
FirstName
LastName

Table: InvestorCompany
InvestorId (PK / FK Investor)
CompanyName
HomepageURL

Issue: The primary key is split over the two subtables.

------------------------------
2. Possible Solution:
Separate subtables for Investors and Companies each with it's own primary key and using a master link table to allocate a common primary key.

Table: InvestorIndividual
IndividualId (PK)
FirstName
LastName
Edited
EditedById

Table: InvestorCompany
CompanyId (PK)
Name
URL
Edited
EditedById

Table: Investor
InvestorId (PK)
InvestorCompanyId (FK Company)
InvestorIndividualId (FK Individual)

Issue: A new joint primary key is generated exclusively to join the two primary keys for the subtables.

------------------------------
3. Possible solution:
Merge the design for Individual and Company investors with a single primary key.

Table: Investor
InvestorId (PK)
Name (individual/company name)
LastName (allow null for company)
URL (allow null for individual)
Type (bool individual/company)
Edited
EditedById

Issue: Redundant design for either type of data.
------------------------------

I hope you can see the problem and we very much appreciate any input on this issue, thank you!

//NumerOneQ

Edited by - numeroneq on 04/08/2003 10:49:35

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2003-04-08 : 11:25:54
I think #3 is the clear cut winner. Not sure what you mean by:

Issue: Redundant design for either type of data.

How is it redundant? There may be fields in your table that don't apply to one type or the other, and you can just leave them NULL, but that's not redundancy, that's just wasting a little bit of disk space.

Another idea is this:

Investors
-------------
InvestorID
Type (boolean)
.. all common fields for all investors ....

and then two "detail" tables for each type of investor with specific fields only for each type:

InvestorCompanyDetails
-----------
InvestorID
..all fields only for companies ...

InvestorIndividualDetails
-----------------
InvestorID
..all fields only for individuals ....


and you create a view for your investors, with a LEFT OUTER JOIN to both detail tables and you have all the data you need, no redunancy, no wasted space, no issue with lots of NULL fields.

That's just a suggestion, of course. Good luck!!


- Jeff
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2003-04-08 : 11:32:38
I'm assuming that a company can invest in itself as well, but how about:

[Company]-||---|<-[Investor]->|---||-[Individual]

Company has a PK of CoId
Individual has a PK of IndId
Investor has a PK of CoId, InvId, and Inv_Type

Company.CoId is a FK to Investor.InvId
Company.CoId is a FK to Investor.CoId
Individual.InvId is a FK to Investor.InvId

Inv_Type is used to identify which values is stored in InvId. I believe this breaks when of the rules about the attributes of an Entity are only describe the key (or is it the other way around).

What'dya think?




Brett

8-)
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2003-04-08 : 12:42:56
Jeff and Brett, I disagree with both of you.
The Investor_type attribute makes a problem for DRI as you cannot create a Foreign Key constraint with conditional logic built in.

I think you need:

create table company (
    [name] varchar(10) not null primary key)

create table individual (
    [name] varchar(10) not null primary key)

create table corporate_investor (
    [name] varchar(10) not null,
    invests_in varchar(10) not null,
    constraint pk_ci primary key ([name],invests_in),
    constating fk_ci_name foreign key ([name]) references company([name]),
    constraint fk_ci_invests_in foreign key (invests_in) references company([name]))

create table individual_investor (
    [name] varchar(10) not null,
    invests_in varchar(10) not null,
    constraint pk_ii primary key ([name],invests_in),
    constating fk_ii_name foreign key ([name]) references individual([name]),
    constraint fk_ii_invests_in foreign key (invests_in) references company([name]))

create view investors
as
select
    investor,
    investor_type,
    invests_in
from (
    select
        [name] as investor,
        'corporate' as investor_type,
        invests_in
    from
        corporate_investor
    union
    select
        [name] as investor,
        'individual' as investor_type,
        invests_in
    from
        individual_investor ) a




Jay White
{0}
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2003-04-08 : 12:56:00
quote:

Inv_Type is used to identify which values is stored in InvId. I believe this breaks when of the rules about the attributes of an Entity are only describe the key (or is it the other way around).


Yup Jay, You da man....that's the sound logical design for that one.

Just curious though, you never leap from the pure faith going from logical to physical?



Brett

8-)
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2003-04-08 : 13:27:56
quote:

The Investor_type attribute makes a problem for DRI as you cannot create a Foreign Key constraint with conditional logic built in.


or can you ???

(oooooooh)

create table Investors(
InvestorID int primary key,
InvestorType char(1) not null check (InvestorType in ('I','C'))
constraint i_altkey unique (investorID, investorType))

-- Note that constraint is redundant to the primary key,
-- but allows us to do the following:

create table InvestorIndividuals(
InvestorID int not null,
InvestorType char(1) not null default 'I' check (InvestorType = 'I')
constraint ii_pk primary key (investorID, investorType),
constraint ii_fk foreign key (investorID, investorType) references Investors (investorID, investorType))


create table InvestorCompanies(
InvestorID int not null,
InvestorType char(1) not null default 'C' check (InvestorType ='C')
constraint ic_pk primary key (investorID, investorType),
constraint ic_fk foreign key (investorID, investorType) references Investors (investorID, investorType))


- Jeff

Edited by - jsmith8858 on 04/08/2003 13:30:33
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2003-04-08 : 13:40:25
But I believe Jay's point is that you can insert a Company Investor Row for an Individual there by breaking any infered RI. You could programmatically enforce, but that still doesn't save you from a rogue programmer (which of course we've never seen one of those )

I'm just wondering if denormalizing the 2 tables in to one provides any performance benefits (not to mention minimizing maintenance).



Brett

8-)
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2003-04-08 : 14:41:57
Brett -- I don't think you can insert into the wrong table with the DDL I posted ... can you show me some insert statements that will break the RI of what I posted?

-- OK:
insert into investors
select 1,'I' union all
select 2,'I' union all
select 3,'C' union all
select 4,'C'

-- will fail (Already there, different type):
insert into investors
select 3, 'I'

-- OK:
insert into investorindividuals (investorid)
select 1 union select 2

-- will fail (wrong type) :
insert into investorindividuals (investorid)
select 3

-- OK:
insert into investorcompanies (investorid)
select 3 union select 4

-- will fail (no matching investor ID):
insert into investorcompanies (investorid)
select 7


- Jeff

Edited by - jsmith8858 on 04/08/2003 14:47:54
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2003-04-08 : 14:46:53
OK, Jeff ... that works too ...

I don't really know how to weigh your solution against mine at the moment ...

Brett: Denormalize for performance? Have you gone mad, man? Don't you read Pascal and Date?

Jay White
{0}
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2003-04-08 : 15:08:56
I guess I missed the point.

What's the purpose of having the three tables in your solution Jeff? Is Investors a consolidation of Corporate and Individuals?

As for Denormalization for performance I guess it really depends on which way the db leans, towards olap or oltp.

And no, I have not read Pascal and Date. Got any links?

Brett

8-)
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2003-04-08 : 15:16:53
Brett -- actually, like I mentioned in my original reply, I would have just 1 table -- investors, it is just an idea to use more than 1.

If there are, say 20 attributes you only track of individuals and a different bunch of attributes you only track for companies, then instead of having NULLS all over the place you can break it out and store the specific attributes in separate tables.

If you don't do that, then you can't say certain fields are required -- because depending on the type, they may need to be NULL. Your integrity of your data can be compromised. Plus, someone could fill in ALL of the tables for both types in the one record -- what would THAT mean?

Hopefully, you can see this is a pretty good solution for this type of data. But again, it is not worth it if there are only like 2 fields that are specific to one type.





- Jeff
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2003-04-08 : 15:22:52
ok, now I've read pascal...

http://www.firstsql.com/dbdebunk/index.htm

Brett

8-)
Go to Top of Page

numeroneq
Starting Member

8 Posts
Posted - 2003-04-08 : 15:32:57
Alright!! Thanx a million guys! I really appreciate it - I will show the thread to my compadres and we'll be sure to agree on a solution! THANK YOU!

//NumerOneQ
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site