Ok, so in going through and continually trying to get my database in normal (enough) form. I keep reevaluating my tables and looking at samples. In my sale table (same as an "order" )

tblPolicySales
pkSalesID
fkAgentID
fkClientID
fkCompanyID
fkPolicyID
fkPolicyTypeID
PolicyDateSigned—basically the selling date or transaction date
PolicyTelephoneVerificationNumber(given at the time of sale to confirm the sale. without it, no sale)
PolicyCancelDate

tblPolicySalesDetail
pkPolicySaleDetailID
PolicyNumber--given from the company after a sale
PolicyEffectiveDate (this is different from the date signed)
PolicyCashValue
PolicyNotes


But now I am wondering if the Company, Policy and Policy Type are actually "details" of a sale. I don't know what to the make of this. We do not collect any money or make any charges. This is an insurance agency that sales insurance policies for multiple companies. And each policy has a specific type. Such as Prudential is company, Life is Policy, Term is type of Policy. To me that information IS the sale, but in reviewing sample DB's the products are actually in the details table.

I have asked this question before, but always mixed up with a more complicated questions. Would anyone be willing to give this a quick look and let me know if my tables are correct or need changing before I start creating forms and queries that just need to be re-done later? I have a whoooooole bunch of those already. Pretty pretty please? I have attached a screen shot of my relationships or I can type more of the table outs. I think I have them all correct for now with the exception of the sales/sales detail problem. Click to view attachment


Thank you.
Kathy

okay, so you're an independent agency, a broker. and you want to keep track of your book of business. that's fairly straightforward. regarding sales vs sales details: your screen shot shows a 1-to-many relationship between the sales and sales details tables. a sales record would describe a specific sale as a whole, a sales detail record would describe one specific item that was included in a specific sale.

so ask yourself: does one sale ever involve more than one item (policy)? for instance, would you record one sale, with one verification number, that involved selling an auto policy and a homeowner policy? or do you consider one sale to always - no exceptions - be about a single policy to a specific customer? are there any details, ever, about the sale of a single policy to a single customer, that would require multiple detail records: multiple policy numbers? multiple payment methods? multiple effective dates? etc, etc, etc.

the answers to the above questions determines 1) if you need a sale details table, and 2) if so, what fields should be in the details table.

hth
tina

Absolutely not. No policy would ever have multiple details about it. and I consider one sale to equal one policy. Even if we actually sell a customer multiple policies, the details are completely separate and only for that particular policy. I guess I got confused about having a "details" table because it was recommended that I ask myself instead of what you said was this: is the policy number a part of the sale OR related to the policy itself. "Same with effective dates...is that part of the sale of part of the policy." Based on that question, the answer to that would be they are part of the policy so they would go in "details about that policy. But one policy never ever has more than one set of details.

So just to clarify---and please confirm this, if you would be so kind. IF each sale is based on one and only one policy, then all information regarding this sale goes into ONE table. (thereby making the creation of a sales form so abundantly easy and not requiring multiple sub forms! yay!) Is that correct??

it is correct, so long as each policy (each sales record) cannot have more than one of any of the details (fields) you're tracking in the table. multiples of a detail = multiple records in a related child table.

this is what process analysis is all about, Kath. understanding relational design principles, so that you know what questions to ask, to dig deeply enough into the process to make sure you have all the information you need - so you can create a relational data model that will fully support the process, but without unnecessary complexity. it does get easier with practice, believe it or not!

hth
tina