Where to set referential integrities at

[SgtJarrow]

I have taken over a database that is just wrong in so many ways, that for me to maintain it would be a nightmare. I have decided to rework the whole thing, and i want to get it right from the start. I have been staring at the tables, trying to decided what to do and that's my problem. I am finding it rather complicated deciding how to divide the tables for normalization. Here's what I have and what I need...

tblFundDetails
FundNumber (PK)
SKNumber
APNumber
InvoiceCCY
FundName
FundDataSource

That table is pretty simple. Here's the tough part. Each fund belongs to one of three clients. But each client operates out of different locations and has different transaction types per location. So my second table looks something like:

tblFeeSchedules
Client (PK)
Center (PK)
Security (PK)
Charge
Price
Currency

I need to be able to set up referential integrity against these two tables. I thought maybe a thiord linking table that holds FundNumber and ClientName, but this doesn't seemt owork for me either. I will happily give any more info you might need and have a sanitized version of the database I can pass. I need to get this solved to continue on my project. Thanks in advance. "Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." Albert Einstein.

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com

 

[LittleSmudge]

Doesn't sound like you need more tables, but I'm making some assumptions baout what is behind tblFeeSchedules based on the text of your post.

You have clients in
tblClients
ClientId ( PK )
ClientInfo1
ClientInfo2
etc..

They have locations
tblLocations
LocationId ( PK )
ClientRef ( FK ) linking to tblClients
Address1
Address2
etc..


There are Transactions in
tblTransactions
TransactionId (PK )
LocationRef ( FK ) link to tblLocation
TransactData1
TransactData2
etc..
( Is this the tblFeeSchedule table ? )

So you can have any number of clients.
For a given client you can have many locations.
For any location you can have many Transation Records
Given any particular Transaction type you can find the location by tracing up the link
and given any location you can find the client by tracing up the link.

Now, Where does the tblFundDetails link into all this?
If a Fund is associated with just one Transaction record then as a FundDetailsRef field to the tblTransactions and store the FundDetailsId in there and the job is done.


If this doesn't fix it - then post to explain why.

G LS

 

[SgtJarrow]

LittleSmudge,

Thansk for the post, but I happened to solve it yesterday. Your suggestions are good, but do not fit into my situation. My tblFeeSchedules lists the cost of performing transactions at each center per client. In other words, Client A will be charged differently than client B for the same transaction at the same center. The tblFunDetails gives account informtaion for each fund. What I finally got to work was a table with transaction information that is linked to the other two. I combined three fields from the import data during import to make the Pk for the transaction table. This PK links to the tblFeeSchedule to find the pricing info per transaction. Then the invoice uses the tranasaction table and tblFundDetails to generate the invoices. Just took me a while to find a way to generate a PK that would enfore referential integrity. Once I got that, it has been smooth programming ever since. Again Thanks. "Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." Albert Einstein.

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com