Joining tables Needed?

[Anderson]

I am designing a database to track revenue generated at various sites from
various programs.

I have the following tables: Sites, VPs, Programs, Revenue Categories(eg.
other, Ancillary), Revenue Classes(eg. goods, services, other), and Revenue
Items/details (tracks the actual item that produces the revenue).

Each site can have one VP only. Each site(VP) can have many programs and
each program can be atributed to many sites(VPs).

The Revenue details table is set up to provide a unique Rev_ID to each item
entered (even though a revenue item may occur at more than one site, there
will be separate instances of this in the table.)

My main problem is that I can't determine whether I should have linking
tables between: Sites and rev Items, Programs and Rev Items, Revenue Category
and Revenue Class, Revenue Item and Revenue Category, etc).

I hope I have been clear enough. Any help would be appreciated.

Thanks,
Anderson

 

[Tim Ferguson]

Each site can have one VP only. Each site(VP) can have many programs
and each program can be atributed to many sites(VPs).

Programs --< IsSitedAt >-- VPs

The IsSitedAt table would look like

IsSitedAt(ProgramID, VP_Code)
primary key (ProgramID, VP_Code)
foreign key ProgramID references Programs
foreign key VP_Code references VPs

The Revenue details table is set up to provide a unique Rev_ID to each
item entered (even though a revenue item may occur at more than one
site, there will be separate instances of this in the table.)

How about this; does it provide the kind of information you need? This
means that you can track a RevenueItem to a particular program at a
particular VP:

RevenueItems >-- IsSitedAt

In other words, just add two fields (ProgramID, VP_Code) to the
RevenueItems table and make them reference the IsSitedAt table. Note: you
do _not_ need a relationship between RevenueItems and Programs or
RevenueItems and VPs.

Hope that makes sense


Tim F

 

[Anderson]

Thanks for the tips. I'll give them a try and see what I can get.

Anderson