Logical Data Modeling Problem

I'm not sure if this is the place to post this or not but I didn't find any
newsgroups specifically for logical data modeling problems. I'm trying to
structure a relational database and the problem is that the complexity has
gotten beyond my level of knowledge. If anyone could help me out it would
be much appreciated.
This database contains a record of projects along with details about the
projects, primarily financial information. The logical entities (tables)
are:
A) Project
B) Funding Sources (many to many relationship with Project - each Project
can have multiple funding sources)
C) Years (many to many relationship with Project - each Project can span
multiple years)
Here's where I encounter my problem. For each project there is a dollar
amount associated with each funding source and year. Here's a sample
project:
Project: Sample
Funding Source A - Year 2005 - $20,000
Funding Source A - Year 2006 - $30,000
Funding Source B - Year 2005 - $10,000
Funding Source B - Year 2006 - $3,000
In tabular form Funding sources would be across the top (columns), while
Years would be across the side (rows).
So my question is that 4th table that contains the dollar amount. Would it
have a many to many relationship with all 3 other entities (Project,
Sources, Years)? Or is there another way to model this relational database?
Thanks for any assistance,
RyanThere is sketchy information to make much sense about your needs.
Off the top, it seems like the [Years] table should really be [YearsFunding]
and contain columns such as [Year],[ProjectID],[FundSourceID],[Amount].
Unless, of course, the 'unsaid' information shows a different usage for the
[Years] table.
(It is very difficult to advise modeling issues without knowing the entire
domain to model.)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:OFlMHInDHHA.3212@.TK2MSFTNGP04.phx.gbl...
> I'm not sure if this is the place to post this or not but I didn't find
> any newsgroups specifically for logical data modeling problems. I'm
> trying to structure a relational database and the problem is that the
> complexity has gotten beyond my level of knowledge. If anyone could help
> me out it would be much appreciated.
> This database contains a record of projects along with details about the
> projects, primarily financial information. The logical entities (tables)
> are:
> A) Project
> B) Funding Sources (many to many relationship with Project - each Project
> can have multiple funding sources)
> C) Years (many to many relationship with Project - each Project can span
> multiple years)
> Here's where I encounter my problem. For each project there is a dollar
> amount associated with each funding source and year. Here's a sample
> project:
> Project: Sample
> Funding Source A - Year 2005 - $20,000
> Funding Source A - Year 2006 - $30,000
> Funding Source B - Year 2005 - $10,000
> Funding Source B - Year 2006 - $3,000
> In tabular form Funding sources would be across the top (columns), while
> Years would be across the side (rows).
> So my question is that 4th table that contains the dollar amount. Would
> it have a many to many relationship with all 3 other entities (Project,
> Sources, Years)? Or is there another way to model this relational
> database?
> Thanks for any assistance,
> Ryan
>|||This is how I originally set it up, exactly how you described. Sounds like
I had it right the first time. Thanks.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23BrrXVnDHHA.4808@.TK2MSFTNGP03.phx.gbl...
> There is sketchy information to make much sense about your needs.
> Off the top, it seems like the [Years] table should really be
> [YearsFunding] and contain columns such as
> [Year],[ProjectID],[FundSourceID],[Amount].
> Unless, of course, the 'unsaid' information shows a different usage for
> the [Years] table.
> (It is very difficult to advise modeling issues without knowing the entire
> domain to model.)
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "Ryan" <Tyveil@.newsgroups.nospam> wrote in message
> news:OFlMHInDHHA.3212@.TK2MSFTNGP04.phx.gbl...
>> I'm not sure if this is the place to post this or not but I didn't find
>> any newsgroups specifically for logical data modeling problems. I'm
>> trying to structure a relational database and the problem is that the
>> complexity has gotten beyond my level of knowledge. If anyone could help
>> me out it would be much appreciated.
>> This database contains a record of projects along with details about the
>> projects, primarily financial information. The logical entities (tables)
>> are:
>> A) Project
>> B) Funding Sources (many to many relationship with Project - each Project
>> can have multiple funding sources)
>> C) Years (many to many relationship with Project - each Project can span
>> multiple years)
>> Here's where I encounter my problem. For each project there is a dollar
>> amount associated with each funding source and year. Here's a sample
>> project:
>> Project: Sample
>> Funding Source A - Year 2005 - $20,000
>> Funding Source A - Year 2006 - $30,000
>> Funding Source B - Year 2005 - $10,000
>> Funding Source B - Year 2006 - $3,000
>> In tabular form Funding sources would be across the top (columns), while
>> Years would be across the side (rows).
>> So my question is that 4th table that contains the dollar amount. Would
>> it have a many to many relationship with all 3 other entities (Project,
>> Sources, Years)? Or is there another way to model this relational
>> database?
>> Thanks for any assistance,
>> Ryan
>|||"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:OFlMHInDHHA.3212@.TK2MSFTNGP04.phx.gbl...
> I'm not sure if this is the place to post this or not but I didn't find
> any newsgroups specifically for logical data modeling problems. I'm
> trying to structure a relational database and the problem is that the
> complexity has gotten beyond my level of knowledge. If anyone could help
> me out it would be much appreciated.
> This database contains a record of projects along with details about the
> projects, primarily financial information. The logical entities (tables)
> are:
> A) Project
> B) Funding Sources (many to many relationship with Project - each Project
> can have multiple funding sources)
> C) Years (many to many relationship with Project - each Project can span
> multiple years)
> Here's where I encounter my problem. For each project there is a dollar
> amount associated with each funding source and year. Here's a sample
> project:
> Project: Sample
> Funding Source A - Year 2005 - $20,000
> Funding Source A - Year 2006 - $30,000
> Funding Source B - Year 2005 - $10,000
> Funding Source B - Year 2006 - $3,000
> In tabular form Funding sources would be across the top (columns), while
> Years would be across the side (rows).
In the UI maybe, but in the database the years should be on rows.
> So my question is that 4th table that contains the dollar amount. Would
> it have a many to many relationship with all 3 other entities (Project,
> Sources, Years)?
It would have a one-to-many relationship with each of those tables.
Something like:
create table ProjectFunding
(
ProjectID int not null
references Project on delete cascade,
FundingSourceID int not null
references FundingSource,
FundingYear datetime not null
references FundingYears,
FundingAmount decimal(17,2) not null,
constraint pk_Funding
primary key (ProjectID,FundingSourceID,FundingYear)
)
create index ix_Funding_Source on Funding(FundingSourceID)
David