Normalization and relationship question

[smithbr]

Hello all, I am trying ot create a db that will allow me to tract commissions from various brokers and customers. I have created all the tables from much larger ones out of MAS90(an accounting program) and thought I normalized all the tables correctly. When I tried to run the query that will give me the commission rates I am looking for it does not work. I don't think my problem is in the query but in the relationships between the tables used in the query. I have included below the shorthand schema describing the tables involved and the business rules. Keep reading and you should understand my question more.

BROKER(BrokerId, BFName, BLName, BPhone)
CUSTOMER(Cust#, CustName, CAddress, CCity, CState, CZip, CPhone)
INVOICE(Invoice#, InvoiceDate, InvoiceTotal, ItemNumber, Customer#) Customer# is FK
PRODUCT(Item#, ItemDescription, ProductLine)
RATE(Cust#, BrokerID, ProductLine, Comm, StateDate) Cust# and BrokerID are FK's

For the rate table, there are many combinations of cust#, brokerID, and ProductLine that is why they are all part of the composite primary key. The combination of ProductLine and Cust# determines both the Broker and the CommRate for each broker involved. There are a handful of instances where there are two or more brokers to one invoice, so I want to CommRate to be determined by the ProductLine and Cust#. I want the Broker to be shown based on this so I can create a report detailing each brokers sales.

Access will not let me create a one-to-many relationship between the Rate.ProductLine and Product.ProductLine.
Below are the business rules the best I can explain them.

Business Rules:
One Customer per invoice, many invoices per customer
One or many brokers per invoice, many invoices per broker
One product line per invoice, many invoices per product line
one item number per invoice, many invoices per item number
one product line per item number, many item numbers per product line

I have one to many relationships between the following entities:
customer.customer# and rate.customer#
broker.brokerID and rate.brokerID
product.itemnumber and invoice.itemnumber

If anyone can see what i need to do either to continue cormalizing or something I would greatly appreciate it. I have been working on this for a while now and would love to be able to finsih it soon.

 

[JeremyNYC]

Hmm. It looks like you're doing pretty well. Are you sure that the two fields are the same data type? What message do you get when you try to create the relationship?

Also, we can't diagnose a query we can't see <g>.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[smithbr]

Here is the sql behind the query i created in access:
SELECT Invoice.InvoiceNumber, Customer.CustomerNumber, Broker.BrokerID, Rate.COMM
FROM Product INNER JOIN ((Customer INNER JOIN Invoice ON Customer.CustomerNumber = Invoice.CustomerNumber) INNER JOIN (Broker INNER JOIN Rate ON Broker.BrokerID = Rate.BrokerID) ON Customer.CustomerNumber = Rate.CustomerNumber) ON (Rate.ProductLine = Product.ProductLine) AND (Product.ItemNumber = Invoice.ItemNumber);

When I try to create the relationship and enfore referential integrity, I get the error:
&quot;No unique index found for the referenced field of the primary key&quot;

If you need any more info just ask and I will do my best to explain.

 

[smithbr]

Sorry about the last post. This is the SQL statement from access, I copied it from a version I was trying to tinker with. Anyways, here it is:

SELECT Invoice.InvoiceNumber, Customer.CustomerNumber, Broker.BrokerID, Rate.COMM
FROM Product INNER JOIN ((Customer INNER JOIN Invoice ON Customer.CustomerNumber = Invoice.CustomerNumber) INNER JOIN (Broker INNER JOIN Rate ON Broker.BrokerID = Rate.BrokerID) ON Customer.CustomerNumber = Rate.CustomerNumber) ON Product.ItemNumber = Invoice.ItemNumber;

 

[RickSpr]

I think you need a ProductLine table, with the ProductLine column as its key. (You'd probably want to name the table something else to avoid having the name duplicated.) Your Rate table (as well as your Product table) can then be related to ProductLine, not to Product.

This comes from your last business rule, &quot;One product line per item number, many item numbers per product line.&quot; Since Item# identifies a Product, this can be restated as &quot;One ProductLine per Product, many Products per ProductLine.&quot;

I'm also curious about this statement: &quot;The combination of ProductLine and Cust# determines both the Broker and the CommRate for each broker involved.&quot; If these two columns determine (i.e. identify) something, then they form a key by themselves. This suggests you're missing a table whose key is ProductLine and Cust#, and which has BrokerID and CommRate as additional columns. I can't say what this table would represent, not knowing your business. Notice, though, that it would define a many-to-many relationship between Customer and ProductLine. That seems to be what you want to do with Rate, and in fact it is like Rate, except that BrokerID is not part of the key.

I mapped out the tables and relationships you've given, plus those I suggested above, and it seems to support your relationships and business rules, if I make the assume that an &quot;item&quot; and a &quot;product&quot; are the same thing. Are they? If not, are you confusing them in your statements? That could be what's causing your problem.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[smithbr]

Rick,
I am confused on one of the things above. What would be the difference between my product table and a ProductLine table you suggested. If I created a ProductLine table, I don't think I could have the ProductLine as the primary key. There are about 35 products(item#'s) per product line and about 10 different ProductLines. That is way I created the Product table with the item# as the primary key. (Is it possible to create almost the same table but with the ProductLine as the primary key?)

About the rate table. I am pulling the invoice information for our accounting software and it only allows us to name one broker(salesrep) per invoice. Most invoices only have one broker but a handful have 2 or even three brokers taking different percentages of a commission. So, instead of having the broker be pulled from the accounting system, I have it be determined (or itentified) by the combination of customer# and ProductLine. One customer may have different brokers for the different productlines we sell. (So broker A sell all products from productline 71 to cust# 999 while broker be sells all products from productline 81 to customer# 999) I think I tried to have the rate table have only productline and cust# be the primary key but it would not let me because of the instances where there are multiple brokers for one customer and product line. Maybe I was wrong and need to try it again. ( I will check when I go into work tomorrow morning)
Thanks for the help.
Brent

 

[RickSpr]

The ProductLine table (I'll call it ProductLn from now on, to distinguish it from the column) would differ from the Product table in that it has ProductLine as the key, so ProductLine would be uniquely indexed. It would also be useful as the RowSource for, say, a list box for setting the ProductLine field in the Rate table--otherwise, it would be possible to enter an undefined ProductLine in the Rate table.

There may be other columns in ProductLn, such as a description. You didn't mention any, and perhaps you don't need any, but in the real world there must be other information about a product line that could some day be useful in the table.

You would have one-to-many relationships from ProductLn.ProductLine to Item.ProductLine and to Rate.ProductLine.

Your last paragraph contradicted what you said initially. In your first post, you said the combination of product line and customer# determines the broker ID, but in this paragraph you said there are multiple brokers for one product line and customer#. Both statements can't be true. If the last statement is true, your Rate table should be fine as it is.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[smithbr]

I am sorry i was not clear in my first post. Both statements I made are true, most of the time there is only one brokerID per order (Invoice#). BUT, there are a handful of clients who have 2 or 3 BrokerId's per order(invoice#) and that is why all brokerId's must be determined by the Customer# and productLine. If there were always only one brokerId per invoice I would sue that as the way to determine the commission rate. Since there are the cases of multiple brokers per invoice all brokers must be determined by the customer# and productline to ensure that all brokers are identified and thus receive the proper commission.

The only info that would go along with the productln would be very general information that describes what type of products fall into each particular productln. I tried setting up a table like this though, and it would let me creat one-to-many relationships from rate to productline and product to productline but would not let me enforce referential integrity in either case.

 

[RickSpr]

I'm afraid it's not getting any clearer. In the post 3 up from this one you said: &quot;I think I tried to have the rate table have only productline and cust# be the primary key but it would not let me because of the instances where there are multiple brokers for one customer and product line.&quot; This strongly suggests to me that Cust# and ProductLine is only usually enough to determine the broker ID. That is incompatible with the statement above that &quot;all brokers must be determined by the customer# and productline.&quot; If there are exceptions where there are multiple brokers for a customer# and productline, which I believe there are because Access wouldn't let you have productline and cust# be the primary key, then you have to just forget about the most common case where there is only one. You can't use that as a rule.

Disregarding the rule, then, your Rate table makes sense. You set up the ProductLn table and created the one-to-many relationships, and that makes sense, too. The only question I still see is, why can't you enforce relational integrity on those relationships?

The only time I can think of that Access won't let you enforce RI is when the related tables aren't in the same database. Did you create ProductLn in a different database from the other two?

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein